Will fetch twice from the same table in same function cause problems?
Im trying to fetch all "items" from the logged user, if the user has no items the function should return false, else, it should return false and echo "nothing", if there are items in the database from the logged user it should return true and echo all the itemnames. Everything works except the first item gets cutted off, so if user X, has items One, Two, Three, Four, the output will be: TwoThreeFour.
I guess its because im using the Fetch twice from the table, if so, how can i fix it so it works?
Heres the function:
public function myitems() {
$user_user_id = $_SESSION['userSession'];
$stmt = $this->db->prepare("SELECT * FROM item WHERE user_user_id=:user_user_id");
$stmt->execute(array(":user_user_id" => $user_user_id));
if ($itemRow = $stmt->fetch(PDO::FETCH_ASSOC) == 0) {
echo "nothing";
return false;
} else {
while ($itemRow = $stmt->fetch(PDO::FETCH_ASSOC)) {
$item_item_id = $itemRow['item_id'];
$stmt2 = $this->db->prepare("SELECT * FROM picture WHERE item_item_id=:item_item_id");
$stmt2->execute(array(":item_item_id" => $item_item_id));
$imgRow = $stmt2->fetch(PDO::FETCH_ASSOC);
echo $itemRow['itemname'];
}
return true;
}
}
When you fetch once, your pointer will move by one row. So you will not get the first row when iterating by while. You can better fetch all row into a array and count the array to know if data exists. Also I would suggest a JOIN instead of running multiple queries for each item_id. So it can be like this:
$stmt = $this->db->prepare("SELECT * FROM item JOIN picture ON item.item_id = picture.item_item_id WHERE item.user_user_id=:user_user_id");
$stmt->execute(array(":user_user_id" => $user_user_id));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
if (count($rows) == 0) {
echo "nothing";
return false;
} else {
foreach ($rows as $itemRow) {
$item_item_id = $itemRow['item_id'];
//You can get your picture details also from $itemRow
echo $itemRow['itemname'];
}
return true;
}
Note: Use
LEFT OUTER JOINin place ofJOIN, if you expect some items which will not have any entry in picture table and you want to get the data for the item at least in that case. (Image fields will provideNULLvalue here)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments