How to retrieve multiple rows MYSQL

Question

$sql = "SELECT * FROM orders WHERE ordercode = '$oc' AND order_status = '$pendingorderstatus'";
        $sqlresult = mysql_query($sql);
        $pendingorderrow = mysql_fetch_array($sqlresult);

        print_r($pendingorderrow);

Guys, I have the code above to retrieve records from the database, but it will only retrieve 1 row of in array format. I have multiple row of same ordercode and pendingorderstatus in the database, why it doesn't retrieve all the records/rows?

Just call mysql_fetch_array another time. It'll fetch the next row. TO get all of them, do it in some loop until the call returns false. — Johannes H., Feb 19 '14 at 02:32
[Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — Phil, Feb 19 '14 at 02:35

score 3 · Answer 1 · answered Feb 19 '14 at 02:32

3

You need to use a loop to iterate through the result set. A while loop is the easiest to use here:

while ($pendingorderrow = mysql_fetch_array($sqlresult)){
    print_r($pendingorderrow);
}

answered Feb 19 '14 at 02:32

John Conde

217,595
99
455
496

Shankar Narayana Damodaran · Answer 2 · 2014-02-19T02:58:57.663

1

You need to loop through the resultset !

while($pendingorderrow = mysql_fetch_array($sqlresult))
{
$orderarray[]=$pendingorderrow['orders'];
}

print_r($orderarray);

Bottomline

This (mysql_*) extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used.

edited Feb 19 '14 at 02:58

answered Feb 19 '14 at 02:32

Shankar Narayana Damodaran

68,075
43
96
126

score 1 · Answer 3 · answered Feb 19 '14 at 02:32

because you need to iterate over the result set:

$sql = "SELECT * FROM orders WHERE ordercode = '$oc' AND order_status = '$pendingorderstatus'";
$sqlresult = mysql_query($sql);
while($pendingorderrow = mysql_fetch_array($sqlresult))
{
    //process here $pendingorderrow, e.g. print_r($pendingorderrow);
}

score 0 · Answer 4 · answered Feb 19 '14 at 03:18

If you are a different strokes kind of person and don't want to iterate over the array, I might suggest some group_concat and explode action, mainly if you are doing only one column, but you could do it for multiple also.

$sql = "SELECT group_concat(orders.columnA) FROM orders WHERE ordercode = '$oc' AND order_status = '$pendingorderstatus'";

Just do the group_concat for each field you want to select from the table. Explode result, and voila array. Or just iterate over the result set...

Just figured throw that there in for completeness, you should be aware of all options when deciding which tool fits what need. The more you know.

How to retrieve multiple rows MYSQL

4 Answers4

You need to loop through the resultset !

Bottomline