Pdo Looping Through and Printing Fetchall

PDO looping through and printing fetchAll

It should be

while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
  $id = $row['id'];
  $n = $row['n'];
  $k = $row['k'];
}

If you insist on fetchAll, then

$results = $query->fetchAll(PDO::FETCH_ASSOC);
foreach($results as $row) {
   $id = $row['id'];
   $n = $row['n'];
   $k = $row['k'];
}

PDO::FETCH_ASSOC fetches only column names and omits the numeric index.

how to properly use while loop in PDO fetchAll

With fetchAll() you don't have to use while at all. As this function returns an array, you have to use foreach() instead:

function getContent() {
    $db = PDOconn();
    $query = "SELECT * FROM posts ORDER BY id DESC LIMIT 0,3";
    $sql = $db->prepare($query);
    $sql->execute();
    return $sql->fetchAll();
}

$data = getContent();
foreach($data as $row) {
    $id = $row['id'];
    $content = $row['content'];
}

PDO::fetchAll vs. PDO::fetch in a loop

Little benchmark with 200k random records. As expected, the fetchAll method is faster but require more memory.

Result :
fetchAll : 0.35965991020203s, 100249408b
fetch : 0.39197015762329s, 440b

The benchmark code used :

<?php
// First benchmark : speed
$dbh = new PDO('mysql:dbname=testage;dbhost=localhost', 'root', '');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = 'SELECT * FROM test_table WHERE 1';
$stmt = $dbh->query($sql);
$data = array();
$start_all = microtime(true);
$data = $stmt->fetchAll();
$end_all = microtime(true);

$stmt = $dbh->query($sql);
$data = array();
$start_one = microtime(true);
while($data = $stmt->fetch()){}
$end_one = microtime(true);

// Second benchmark : memory usage
$stmt = $dbh->query($sql);
$data = array();
$memory_start_all = memory_get_usage();
$data = $stmt->fetchAll();
$memory_end_all = memory_get_usage();

$stmt = $dbh->query($sql);
$data = array();
$memory_end_one = 0;
$memory_start_one = memory_get_usage();
while($data = $stmt->fetch()){
  $memory_end_one = max($memory_end_one, memory_get_usage());
}

echo 'Result : <br/>
fetchAll : ' . ($end_all - $start_all) . 's, ' . ($memory_end_all - $memory_start_all) . 'b<br/>
fetch : ' . ($end_one - $start_one) . 's, ' . ($memory_end_one - $memory_start_one) . 'b<br/>';

loop through the data in PDO

The solution is to change select to return all rows , since fetch() only return a single row at the time.

Option 1:

$result = array();
while($row = $smtp->fetch(PDO:: FETCH_ASSOC)){    
  $result[]=$row;
}             
return $result;

option 2:

$result = $smtp->fetchAll(PDO:: FETCH_ASSOC);
return $result;

use your function like this

$rows = select($conn, 'user', 'cart', $user,':user','*');
foreach($rows as $row){
 //do something with $row
}

Loop through fetchall without knowing the fields php

First, fix your return fetchAll to specify the return style like so:

return $query->fetchAll(PDO::FETCH_ASSOC);

Then you can use an inner loop with your outter loop like so:

//get the results
$results = $User->getUser();
//loop over the results, setting $result to an array representing each row
foreach($results as $result){
    //loop over each $result (row), setting $key to the column name and $value to the value in the column.
    foreach($result as $key=>$value){
        //echo the key and value.
        echo "{$key} = {$value}<br>";
    }
}

This will output all columns and their value regardless of what columns there are in the array. Following the comments, you can see what I do is, using your code, loop over the outer array that is an array of each row from the query. Then loop over the array from each row getting the column name and the value in that column. For now I am just echo'ing out the column and value. You would likely want to do more like echo this out to a table or whatever your end goal is.

How do I loop through a MySQL query via PDO in PHP?

Here is an example for using PDO to connect to a DB, to tell it to throw Exceptions instead of php errors (will help with your debugging), and using parameterised statements instead of substituting dynamic values into the query yourself (highly recommended):

// connect to PDO
$pdo = new PDO("mysql:host=localhost;dbname=test", "user", "password");

// the following tells PDO we want it to throw Exceptions for every error.
// this is far more useful than the default mode of throwing php errors
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// prepare the statement. the placeholders allow PDO to handle substituting
// the values, which also prevents SQL injection
$stmt = $pdo->prepare("SELECT * FROM product WHERE productTypeId=:productTypeId AND brand=:brand");

// bind the parameters
$stmt->bindValue(":productTypeId", 6);
$stmt->bindValue(":brand", "Slurm");

// initialise an array for the results
$products = array();
$stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $products[] = $row;
}

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.

Iterating over results of PDO query

Try this (If I understood correctly) :

$products = $productDB->prepare("SELECT * FROM products WHERE cat_id LIKE :cat");

// Now, you can either do this :
$products->bindParam('cat', '%'.$cat.'%');
$products->execute();

// or you can call execute with an associative array of your parameterized query.
$products->execute(array('cat' => '%'.$cat.'%'));

// Then, get all the results like this :
$rows = $products->fetchAll();
foreach ($rows as $row) {
    // Do work here ..
}

// Or, like this :
while ($row = $products->fetch(PDO::FETCH_ASSOC)) {
    // Do work here ..
}

I personaly prefer the while, because you don't fetch the whole query in one var, reducing the amount of memory needed.

I also recommend you to use the FETCH_* parameter, to get only the kind of array you want.

By the way, you need to know that rowCount should not be used to count the rows returned by a SELECT. As said by php.net :

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

How to loop through PHP PDO FETCH ALL ASSOC ARRAY?

One of the possible solutions could be the following:

<?php

$list = array (
    array('activity_id' => '2',
          'ip_address' => '127.0.0.1',
          'activity_name' => 'B',
          'start_date' => '2014-12-03',
          'end_date' => '2014-12-31',
          'task_manager' => 'fd'),
    array('activity_id' => '3',
          'ip_address' => '127.0.0.1',
          'activity_name' => 'dsw',
          'start_date' => '12/04/2014',
          'end_date' => '12/05/2014',
          'task_manager' => 'Hello_am'),
    array('activity_id' => '4',
          'ip_address' => '127.0.0.1',
          'activity_name' => 'dswfwaesf',
          'start_date' => '12/04/2014',
          'end_date' => '12/05/2014',
          'task_manager' => 'Hello_am_2')
);

$headers = array('activity_id','ip_address','activity_name','start_date','end_date','task_manager');

$fcsv = fopen('file.csv', 'w');

fputcsv($fcsv, $headers);

foreach ($list as $row) {
    fputcsv($fcsv, $row);
}

fclose($fcsv);
?>

Hope it's useful!

---

Related Topics