PHP PDO with foreach and fetch

Question

The following code    lt  php try        dbh   new PDO  mysql host  hostname dbname  dbname    username   password       echo  Connection is successful  lt br  gt         sql    SELECT   FROM users        users    dbh- gt query  sql       foreach   users as  row            print  row  name      -     row  sex      lt br  gt              foreach   users as  row            print  row  name      -     row  sex      lt br  gt               dbh   null    catch  PDOexception  e        echo  Error is       e- gt  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    lt  php try        dbh   new PDO  mysql host  hostname dbname  dbname    username   password       echo  Connection is successful  lt br  gt         sql    SELECT   FROM users        users    dbh- gt query  sql       foreach   users as  row            print  row  name      -     row  sex      lt br  gt              echo   lt br  gt         result    users- gt fetch PDO  FETCH ASSOC       foreach  result as  key   gt   value            echo  key    -     value     lt br  gt               dbh   null    catch  PDOexception  e        echo  Error is       e- gt  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    lt  php try        dbh   new PDO  mysql host  hostname dbname  dbname    username   password       echo  Connection is successful  lt br  gt         sql    SELECT   FROM users        users    dbh- gt query  sql        echo   lt br  gt         result    users- gt fetch PDO  FETCH ASSOC       foreach  result as  key   gt   value            echo  key    -     value     lt br  gt               dbh   null    catch  PDOexception  e        echo  Error is       e- gt  etmessage        Output   Connection is successful   user id-0000000001 name-person A sex-male   Why does this happen

User · Answer

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.

User · Answer

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- gt query  sql   foreach   users as  row        print  row  name      -     row  sex      lt br  gt        users- gt execute     foreach   users as  row        print  row  name      -     row  sex      lt br  gt        Or you could cache using tailored CachingIterator with a fullcache    users          dbh- gt query  sql     usersCached   new CachedPDOStatement  users    foreach   usersCached as  row        print  row  name      -     row  sex      lt br  gt      foreach   usersCached as  row        print  row  name      -     row  sex      lt br  gt        You find the CachedPDOStatement class as a gist  The caching itertor is probably more sane than storing the resultset into an array because it still offers all properties and methods of the PDOStatement object it has wrapped

User · Answer

users    dbh- gt query  sql   foreach   users as  row        print  row  name      -     row  sex      lt br  gt      foreach   users as  row        print  row  name      -     row  sex      lt br  gt        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- gt 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- gt query  sql   foreach   users as  row        print  row  name      -     row  sex      lt br  gt      echo   lt br  gt     result    users- gt fetch PDO  FETCH ASSOC   foreach  result as  key   gt   value        echo  key    -     value     lt br  gt        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

User · Answer

foreach over a statement is just a syntax sugar for the regular one-way fetch   loop  If you want to loop over your data more than once  select it as a regular array first   sql    SELECT   FROM users    stm    dbh- gt query  sql      here you go   users    stm- gt fetchAll     foreach   users as  row        print  row  name      -     row  sex      lt br  gt      echo   lt br  gt    foreach   users as  row        print  row  name      -     row  sex      lt br  gt        Also quit that try  catch thing  Don t use it  but set the proper error reporting  for PHP and PDO

[php] PHP PDO with foreach and fetch

Examples related to php

Examples related to mysql

Examples related to pdo

Examples related to foreach

Examples related to fetch