Use PDO statement multiple times when using multiple foreach loops

Question

I have the following code:

if(!empty($postCountryAdd)) {
$sqlQueryLocalityAdd = $dbh->prepare("SELECT DISTINCT locality_add FROM table WHERE country_add = :country_add ORDER BY locality_add ASC");
$sqlQueryLocalityAdd->execute(array(':country_add' => $postCountryAdd));

echo '<option value="">Select locality</option>';
    foreach($sqlQueryLocalityAdd as $localityAddRow) {
        //while ($localityAddRow = $sqlQueryLocalityAdd->fetch()){
        echo '<option value="';
        echo $localityAddRow["locality_add"];
        echo '">';
        echo $localityAddRow["locality_add"];
        echo '</option>';
    }
}

If I use foreach($sqlQueryLocalityAdd as $localityAddRow) the code stops responding. Why can't I use foreach more than once? How can I fix it please?

maybe interesting? states that scrollable cursor not available in mysql. http://stackoverflow.com/a/19076778/3184785. — Ryan Vincent, Apr 05 '16 at 14:44
Your example code will not fail as it's not showing the case where you have multiple foreach blocks after each other--that's why folks are confused. The code isn't stopping in the first iteration over the entire result set, somewehre else you trying a second full iteration. — Ray, Apr 05 '16 at 18:26

Ray · Answer 1 · 2016-04-05T18:29:39.097

0

$sqlQueryLocalityAdd isn't a result set, it's a PDOStatement object and you can only iterate over it directly once (as noted clearly by deceze).

The execute() statement returns a boolean true or false on success of the query.

If successfully run, you need to fetch results from it post-query and iterate the array returned:

   $success = $sqlQueryLocalityAdd->execute(array(':country_add' => $postCountryAdd));
   if($success) {

       $results = $sqlQueryLocalityAdd->fetchAll();

       foreach($results as $localityAddRow) {
           echo '<option value="';
            ....

The resulting array $results is just a vanilla array, so you can iterate over it as many times as you want.

Note: If the execute() returns false, something is wrong with the query--a successful running query returning a empty result set results will still result in a true.

edited Apr 05 '16 at 18:29

answered Apr 05 '16 at 14:00

Ray

40,256
21
101
138

`PDOStatement` `implements Traversable`, so you *can* iterate it directly! – deceze Apr 05 '16 at 14:05
@deceze indeed it does, good point. Can't seem to find documentation of exactly how it implements it, so not sure exactly what to expect. Does it do a fetchAll behind the scene? Makes sense that it would be to iterate over results. – Ray Apr 05 '16 at 14:14
@deceze if thats true and when passed into a foreach returns the exactly same thing as `$row = $stmt->fetch()` the example code would run the same with either the foreach. Do you know if you get an object back freom the iteration instead of an array? I – Ray Apr 05 '16 at 14:25
@deceze I've never seen an example using direct iteration over the result set, so I'd like get a handle on how to do so as it seems more elegant – Ray Apr 05 '16 at 14:28
@deceze ah, i see the other thread of comments in another answer. I get it now. BTW, my answer never said you can't traverse a PDOStatement. – Ray Apr 05 '16 at 14:29
@Ray : returned types from the fetch: [PDOStatement::setFetchMode — Set the default fetch mode for this statement](http://php.net/manual/en/pdostatement.setfetchmode.php). The `PDO::FETCH_CLASS` is useful when using 'models'? – Ryan Vincent Apr 05 '16 at 16:18

score 0 · Answer 2 · edited May 23 '17 at 12:07

0

$sqlQueryLocalityAdd is an Object which in this case - which I am showing and OP has used - cannot be iterated through. (bold so @deceze can understand).

You can use the fetchAll() inside a foreach loop to achieve this however.

Your code should look something like this:

[...]
    if($sqlQueryLocalityAdd->execute(array(':country_add' => $postCountryAdd)):
        foreach($sqlQueryLocalityAdd->fetchAll() as $row):
            echo $row['column'];
            [...]
        endforeach;
    endif;
[...]

Difference between an array and an Object

edited May 23 '17 at 12:07

Community

1
1

answered Apr 05 '16 at 14:00

Jaquarh

6,493
7
34
86

You can `foreach` objects just fine, especially when they `implement Traversable`, as `PDOStatement` does. – deceze Apr 05 '16 at 14:05
Never seen this done, but this answers his question so... I will edit out 'only'. @deceze – Jaquarh Apr 05 '16 at 14:06
You're still saying "cannot be iterated", which is clearly wrong. The OP also (cryptically) says that it works *once* (if I interpret the question correctly). – deceze Apr 05 '16 at 14:12
Yea, where has OP stated he used a `cursor` ? The Object OP has inside the question cannot be iterated through without changing his `Prepare` statement. So in this context, my answer is correct. @deceze – Jaquarh Apr 05 '16 at 14:13
It cannot be iterated *more than once.* It *can* be iterated *once*. – deceze Apr 05 '16 at 14:14
I'm sure he knows how to **store** it so he can iterate as many times as he likes (creating a local variable). If you look at his commented out code however, you can see he is only trying to fetch line by line but in **two** statements which my answer does for him.. So please correct me where I am wrong? @deceze – Jaquarh Apr 05 '16 at 14:16
I take objection to your ***"which in this case cannot be iterated through"***. – It ***can*** be iterated through! It just can't be iterated through *more than once.* – deceze Apr 05 '16 at 14:17
`in this case` - meaning the case I am showing from what OP did. I rest my case. @deceze – Jaquarh Apr 05 '16 at 14:18

score 0 · Answer 3 · edited May 23 '17 at 11:50

0

The problem is that the result set can only be iterated once; it is then exhausted, MySQL discards it, and you cannot iterate it again.

I have never actually tried this, but to create a result set which can be iterated several times, you need a scrollable cursor, which you should be able to create thusly:

$sqlQueryLocalityAdd = $dbh->prepare(..., [PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL]);

You should then theoretically be able to iterate the result multiple times.

If that doesn't work, fetch the data into an array using $sqlQueryLocalityAdd->fetchAll() and iterate it as often as you want. In fact, that appears to be the only option with MySQL: https://stackoverflow.com/a/19076778/476

edited May 23 '17 at 11:50

Community

1
1

answered Apr 05 '16 at 14:10

deceze

510,633
85
743
889

1

I get it finally. The confusing part the OP only shows a single foreach block. It's not dying on a step of the initial full iteration over the result set generated by the statement, but it's dying because somewhere later (not shown in post) he's trying another foreach iteration. – Ray Apr 05 '16 at 18:28
@Ray Ding ding ding ding! We've got a winner! – deceze Apr 05 '16 at 18:35

Use PDO statement multiple times when using multiple foreach loops

3 Answers3

Linked