PHP PDO with foreach and fetch

Question

The following code:

<?php
try {
    $dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
    echo "Connection is successful!<br/>";
    $sql = "SELECT * FROM users";
    $users = $dbh->query($sql);
    foreach ($users as $row) {
        print $row["name"] . "-" . $row["sex"] ."<br/>";
    }
    foreach ($users as $row) {
        print $row["name"] . "-" . $row["sex"] ."<br/>";
    }
    $dbh = null;
}
catch (PDOexception $e) {
    echo "Error is: " . $e-> etmessage();
}

Output:

Connection is successful!

person A-male
person B-female

Running "foreach" twice is not my purpose, I'm just curious why TWO "foreach" statements only output the result once?

Following is the similar case:

<?php
try {
    $dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
    echo "Connection is successful!<br/>";
    $sql = "SELECT * FROM users";
    $users = $dbh->query($sql);
    foreach ($users as $row) {
        print $row["name"] . "-" . $row["sex"] ."<br/>";
    }
    echo "<br/>";
    $result = $users->fetch(PDO::FETCH_ASSOC);
    foreach($result as $key => $value) {
        echo $key . "-" . $value . "<br/>";
    }
    $dbh = null;
}
catch (PDOexception $e) {
    echo "Error is: " . $e-> etmessage();
}

Output:

Connection is successful!

person A-male
person B-female

SCREAM: Error suppression ignored for
Warning: Invalid argument supplied for foreach()

But when I delete the first "foreach" from the above codes, the output will become normal:

<?php
try {
    $dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
    echo "Connection is successful!<br/>";
    $sql = "SELECT * FROM users";
    $users = $dbh->query($sql);

    echo "<br/>";
    $result = $users->fetch(PDO::FETCH_ASSOC);
    foreach($result as $key => $value) {
        echo $key . "-" . $value . "<br/>";
    }
    $dbh = null;
}
catch (PDOexception $e) {
    echo "Error is: " . $e-> etmessage();
}

Output:

Connection is successful!

user_id-0000000001
name-person A
sex-male

Why does this happen?

hakre · Accepted Answer · 2021-07-14T13:16:32.240

29

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.

edited Jul 14 '21 at 13:16

answered Mar 13 '13 at 13:07

hakre

193,403
52
435
836

Thanks, although I can't understand it clearly but your example is definitely useful to me in the future. – Eathen Nutt Mar 13 '13 at 13:42
Please see yet another edit, I had a typo: Use `$users->execute();` after the first `foreach` to reset the cursor to the beginning of the result-set. This is most likely what you're looking for. The cache is a more specific solution and not really necessary as you can do `$users->execute();`. I added it just for the general example. – hakre Mar 13 '13 at 13:45
thank you, the first one seems to be clearer to me, I will look into the execute function in php manual. – Eathen Nutt Mar 13 '13 at 13:51
Yes, it's much more straight forward. The method is here: http://php.net/manual/en/pdostatement.execute.php – hakre Mar 13 '13 at 13:53

Your Common Sense · Answer 2 · 2022-02-15T09:04:16.437

18

Executing the same query again only to get the results you already had, as suggested in the accepted answer, is a madness. Adding some extra code to perform such a simple task also makes no sense. I have no idea why people would devise such complex and inefficient methods to complicate such primitive, most basic actions.

PDOStatement is not an array. Using foreach over a statement is just a syntax sugar that internally uses the familiar one-way while loop. If you want to loop over your data more than once, simply select it as a regular array first

$sql = "SELECT * FROM users";
$stm = $dbh->query($sql);
// here you go:
$users = $stm->fetchAll();

and then use this array as many times as you need:

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

Also quit that try..catch thing. Don't use it, but set the proper error reporting for PHP and PDO

edited Feb 15 '22 at 09:04

answered Mar 13 '13 at 13:27

Your Common Sense

156,878
40
214
345

Thank you for the tip, learning the prepare and execute is my next step. – Eathen Nutt Mar 13 '13 at 13:38
5

@YourCommonSense Why do you have two for each loops in your answer? I'm not fault finding, just learning... – Norman Oct 14 '13 at 15:14
4

using pdo->query() is definitely NOT a wrong way of using the library. exec() and query() are there to be used. The only wrong usage was trying to go through the statement two times, he should just have fetchAll() and then he can iterate several times through the array.. If you stay to your word that using query() is wrong, give a proper reason please. – John Sep 24 '16 at 23:08
I too am unclear on what is wrong with using `query()`, as [mentioned in your tutorial](https://phpdelusions.net/pdo#foreach). Is there something about this particular implementation that's not ideal? – showdev Apr 21 '17 at 00:40
1

@showdev you are right. I don't remember the context o this answer but definitely it looked alien, so I edited it – Your Common Sense Apr 21 '17 at 03:35
Hi Common Sense can you expand on the comment "quit that try..catch thing" more? Do you have a good link to the correct way to catch errors when using PHP/PDO? Thanks – Neal Davis Aug 09 '18 at 19:36
@NealDavis yes, I do. See the link added to the answer – Your Common Sense Nov 04 '19 at 10:44

score 10 · Answer 3 · edited Jul 08 '19 at 00:00

This is because you are reading a cursor, not an array. This means that you are reading sequentially through the results and when you get to the end you would need to reset the cursor to the beginning of the results to read them again.

If you did want to read over the results multiple times, you could use fetchAll to read the results into a true array and then it would work as you are expecting.

score 1 · Answer 4 · answered Mar 13 '13 at 13:05

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

Here $users is a PDOStatement object over which you can iterate. The first iteration outputs all results, the second does nothing since you can only iterate over the result once. That's because the data is being streamed from the database and iterating over the result with foreach is essentially shorthand for:

while ($row = $users->fetch()) ...

Once you've completed that loop, you need to reset the cursor on the database side before you can loop over it again.

$users = $dbh->query($sql);
foreach ($users as $row) {
    print $row["name"] . "-" . $row["sex"] ."<br/>";
}
echo "<br/>";
$result = $users->fetch(PDO::FETCH_ASSOC);
foreach($result as $key => $value) {
    echo $key . "-" . $value . "<br/>";
}

Here all results are being output by the first loop. The call to fetch will return false, since you have already exhausted the result set (see above), so you get an error trying to loop over false.

In the last example you are simply fetching the first result row and are looping over it.

score -2 · Answer 5 · edited Feb 15 '22 at 08:56

-2

$row = $db->getAllRecords(DB_TBLPREFIX . '_payplans', '*', ' AND ppid = "' . $myid . '"');
foreach ($row as $value) {
    $bpprow = array_merge($bpprow, $value);
}

This is based on PHP functions where you can globally use this data.

edited Feb 15 '22 at 08:56

F. Müller

3,969
8
38
49

answered Feb 15 '22 at 08:55

Mubashir Majeed

1
1

1

There is no PHP function getAllRecords() (and shouldn't be) – Your Common Sense Feb 15 '22 at 08:58
it's a self made function which will get all records from database – Mubashir Majeed Feb 15 '22 at 09:15
Yes. But this answer helps no one because getAllRecords is not a php function. And also, this function is very bad bacause it allows SQL injection. – Your Common Sense Feb 15 '22 at 09:24
Okay, i'l change it – Mubashir Majeed Feb 15 '22 at 09:45

PHP PDO with foreach and fetch

Output:

5 Answers5

Linked