PHP Pdo with Foreach and Fetch

PHP PDO with foreach and fetch

A PDOStatement (which you have in $users) is a forward-cursor. That means, once consumed (the first foreach iteration), it won't rewind to the beginning of the resultset.

You can close the cursor after the foreach and execute the statement again:

$users       = $dbh->query($sql);
foreach ($users as $row) {
    print $row["name"] . " - " . $row["sex"] . "<br/>";
}

$users->execute();

foreach ($users as $row) {
    print $row["name"] . " - " . $row["sex"] . "<br/>";
}

Or you could cache using tailored CachingIterator with a fullcache:

$users       = $dbh->query($sql);

$usersCached = new CachedPDOStatement($users);

foreach ($usersCached as $row) {
    print $row["name"] . " - " . $row["sex"] . "<br/>";
}
foreach ($usersCached as $row) {
    print $row["name"] . " - " . $row["sex"] . "<br/>";
}

You find the CachedPDOStatement class as a gist. The caching iterator is probably more sane than storing the result set into an array because it still offers all properties and methods of the PDOStatement object it has wrapped.

PHP PDO fetchAll() vs direct foreach loop

The only difference is that the former doesn't consume extra memory for the returned records like the latter does.

However, given you generally shouldn't fetch more records than could be shown on a single HTML page anyway, the difference considered to be a negligible one.

pdo fetchAll in a foreach loop

Use joins to combine all 3 queries into one, do not re-do joins in php code.

Select *
FROM tbl_products p
INNER JOIN tbl_products_to_categories ptoc on ptoc.id_product=tbl_products.id_product
INNER JOIN tbl_catalog_categories c1 on ptoc.id_category=c1.id_category
INNER JOIN tbl_catalog_categories c2 on c1.id_parent=c2.id_category
WHERE c2.name=:id

This way you can avoid the multiple nested loops within php that are basically simulating the joins. The :id parameter in the above query is the $id parameter supplied to the php function. Just loop through the results of the above query to get all products belonging to all the categories.

If a product belongs to multiple categories, then the above query (similarly to your own code) will list it multiple times.

PDO Foreach fetch loop returns first row with specific id and rest as false

Okay, I figured it out! All I had to do is to loop users messages array once they were fetched and then push each of those separately into another array that got returned in the end.

while($row = $getMessage->fetchObject()){
       array_push($messages, $row);   
} 

Here is the complete function.

public function listMessages($group_id){ 
        $db = initDb(); 
        $getGroupUsers = $db->prepare("SELECT user_id FROM in_group WHERE group_id = :group_id");
        $getGroupUsers->bindParam(":group_id", $group_id, PDO::PARAM_INT);
        $getGroupUsers->execute();
        $users = $getGroupUsers->fetchAll(PDO::FETCH_ASSOC);
        $messages = array();
        foreach ($users as $item) {
            $getMessage = $db->prepare("SELECT * FROM task_messages WHERE user_id = :user_id ORDER BY visited ASC");
            $getMessage->bindParam(":user_id", $item['user_id'], PDO::PARAM_INT);
            $getMessage->execute();                
            if($getMessage->rowCount() > 0){                                        
                while($row = $getMessage->fetchObject()){
                    array_push($messages, $row);   
                }                                 
            }

        }
        return json_encode($messages); 
}

Now returned array looks like this:

How to get all rows from myslq table by foreach loop in php pdo

PDOStatement::fetchColumn Returns a single column from the next row of a result set

You should use fetch() or fetchall() in your case

<?php

$query = $db->query("SELECT * FROM `POS_refund`")->fetchall();

foreach($query as $row){

    echo $get_prod_id = $row['prod_id'];

    $get_prod_qnt = $row['qnt'];

} 
?>

If you want to see if there's records returned you could use count() since fetchall() returns array, then just count your array elemenets

<?php

$query = $db->query("SELECT * FROM `POS_refund`");
$results = $query->fetchall();

if(count($results) > 0){
     foreach($results as $row){

        echo $get_prod_id = $row['prod_id'];

        $get_prod_qnt = $row['qnt'];

    } 

}else{

    echo "No results";
}

?>

php PDO fetchAll with foreach not working

You need to execute the statement.

$q = $db->prepare("SELECT * FROM articles ORDER BY aid ASC");
$q->execute();
$r = $q->fetchAll(PDO::FETCH_ASSOC);

Alternatively you can use PDO::query http://php.net/manual/en/pdo.query.php.

PHP PDO fetch form data with while and foreach inside the while

You are executing two query while fetch loops, with one nested inside another. There may be other ways of organizing your logic, such as using fetchAll() to retrieve all rows from the outer query at once and then using a foreach to loop them.

But the main problem here is that you use the variable name $row in both fetch loops. This will cause the inner fetch to overwrite the previous contents of $row, breaking any future intended use of that array. Simply using a different variable name than $row for the inner loop will help.

You may have been seeing PHP complain about unset array indexes that would have pointed more closely to problems with $row. Always when developing and testing code, ensure PHP is displaying errors on screen. At the top of your script, (or enable in php.ini on a development workstation):

error_reporting(E_ALL);
ini_set('display_errors', 1);

And ensure that PDO is throwing useful exceptions to help debug its behavior. By default it will error silently.

$pdo = new PDO(/* connection details... */);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

PDO foreach echoing the same value

You use PDOStatement::fetch(), which will only get one (the first, next) row.
So you iterated over one row, which gave you 7 turnes (because you obviously have 7 columns) the same result for $array['AUTHOR'].

As you want the whole result set you need to use fetchAll().

This will return an array of all rows, that you then can iterate over.

public function get_result(){
    $res=self::$conn->prepare("SELECT * FROM POSTS");
    $res->execute();
    $array=$res->fetchAll(PDO::FETCH_ASSOC);
    foreach($array as $row){
        echo $row['AUTHOR'] . "<br>";
    }
}

---

Related Topics