Wrong data retrieved from database
- by holyredbeard
So, I want to retrieve the order of the elements of a list. The order is set before by the user, and are stored in the table below. Because I also want to retrieve name and description of the list elements I need to combine two tables (see below).
However, what is actually retrieved is an array containing 16 elements (should be four because it only exists four elements as for now). The array is too long to post here, but I put it in a phpFiddle to be found here if you're interested.
Well, I have really tried to find what's wrong (probably something easy as always), but with no luck.
Thanks a lot for your time and help!
listModel.php:
public function GetOrderedElements($userId, $listId) {
// $userId = 46
// $listId = 1
$query = "SELECT le.listElemId, le.listElemName, le.listElemDesc, lo.listElemOrderPlace
FROM listElement AS le
INNER JOIN listElemOrder AS lo
ON le.listId = lo.listId
WHERE lo.userId = ?
AND lo.listId = ?
ORDER BY listElemId";
$stmt = $this->m_db->Prepare($query);
$stmt->bind_param("ii", $userId, $listId);
$listElements = $this->m_db->GetOrderedElements($stmt);
return $listElements;
}
database.php:
public function GetOrderedElements(\mysqli_stmt $stmt) {
if ($stmt === FALSE) {
throw new \Exception($this->mysqli->error);
}
if ($stmt->execute() == FALSE) {
throw new \Exception($this->mysqli->error);
}
if ($stmt->bind_result($listElemId, $listElemName, $listElemDesc, $listElemOrderPlace) == FALSE) {
throw new \Exception($this->mysqli->error);
}
$listElements = array();
while ($stmt->fetch()) {
$listElements[] = array('listElemId' => $listElemId,
'listElemName' => $listElemName,
'listElemDesc' => $listElemDesc,
'listElemOrderPlace' => $listElemOrderPlace);
}
var_dump($listElements);
$stmt->Close();
return $listElements;
}
from the database:
listElemOrder:
listElemOrderId | listId | listElemId | userId | listElemOrderPlace
1 1 1 46 1
2 1 2 46 4
3 1 3 46 2
4 1 4 46 3
listElement:
listElemId | listElemName | listId | listElemDesc | listElemOrderPlace
1 Elem A 1 Derp NULL
2 Elem B 1 Herp NULL
3 Elem C 1 Lorum NULL
4 Elem D 1 Ipsum NULL
Note: 'listElemOrderPlace' in the table listElement is the final order of the elements (all users average), not to be mixed with the one with the same name in the other table, that's only a specific user's order of the list elements (which is the interesting one in this case).
