use foreach() instead of while() with a query result

Question

I had this code working (I thought).

$sql = "SELECT
          order.id,
          order.date
        FROM
          order";
$result = mysql_query($sql) or die(mysql_error());
$rows = mysql_fetch_assoc($result);

foreach($rows as $row){
  echo $row['id'].": ".$row['date']."<br>";
}

The result was the same as if I would use:

while($row = mysql_fetch_assoc($result)){
  echo $row['id'].": ".$row['date']."<br>";
}

But strangely, today it stopped working. Or it never has, but then I'm getting a little crazy.

I want to use foreach() because the array $rows can also be 'manually' filled and not from a query.

What am I doing wrong..?

You can't `foreach` here. It has never worked. You've only ever fetched a single row and iterated over the *columns* in that row with `foreach`. — user229044, Mar 19 '13 at 13:33
first one fetch 1 record only, then loops through the records columns — Waygood, Mar 19 '13 at 13:33
Why don't you use print_r($rows) before any loop and check what is actually there? You'll have a clear idea of what is going on ;) — J A, Mar 19 '13 at 13:35
The two results can't be same unless you have only one entry in your database. Someone deleted that entry perhaps. — itachi, Mar 19 '13 at 13:35
Thanx all! I have my answer, see answer of DaveRandom [link](http://stackoverflow.com/a/15500980/2153795) below. I think I need a holiday... I really thought I had it working! — Timo002, Mar 19 '13 at 14:03

score 4 · Accepted Answer · edited May 25 '19 at 14:25

4

That has never worked (properly). mysql_fetch_assoc() only returns one row, your foreach is just iterating the columns of the first row from the result set.

You cannot use foreach without first using a while (or for) loop to fetch the result rows into an array.

If you want to use foreach, as you say, do this:

 $rows = array();
 while ($row = mysql_fetch_assoc($result)) {
     $rows[] = $row;
 }

 foreach ($rows as $row) {
     // ...
 }

Also, please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

edited May 25 '19 at 14:25

Zoe

27,060
21
118
148

answered Mar 19 '13 at 13:34

DaveRandom

87,921
11
154
174

Thanks Dave, this is the answer! I don't understand why I think that this code has worked.. probably I need a holiday! I also know that `mysql_` is deprecated in php 5.5. At the moment I'm making little modifications with the old `mysql_`, later I will update the whole site with `mysqli_`. – Timo002 Mar 19 '13 at 13:44
@Timo002 Btw, if you were using PDO the foreach code would actually work :) – NikiC Mar 19 '13 at 15:44

score 1 · Answer 2 · answered Mar 19 '13 at 13:39

Firstly, it's worth knowing that the mysql_xx() functions are deprecated; it is recommended not to use them any more, and switch your code to either the mysqli or PDO libraries instead.

Now, to answer your question:

You can't use foreach with recordsets returned by the mysql_xxx() functions. It simply can't be done.

However, both of the alternative APIs (mysqli and PDO) return their recordset objects as iterators, which means you can use foreach to loop over them.

So the answer to your question is: if you want to use foreach to loop through a record set, you need to switch to one of the other database APIs.

(as a bonus, if you do switch, the other APIs also provide quite a bit of extra functionality over the old mysql_xx() functions)

score 0 · Answer 3 · answered Mar 19 '13 at 13:35

0

mysql_fetch_assoc reads only single row.

That means foreach iterated over columns of a single row.

answered Mar 19 '13 at 13:35

Aleš Krajník

177
1
10

The two ways were never the same even though you had a single line in your DB. – Aleš Krajník Mar 19 '13 at 13:37

use foreach() instead of while() with a query result

3 Answers3