Naveen Sharma Naveen Sharma - 1 year ago 58
SQL Question

Does mysqli_fetch_assoc return data in order?

I am using mysqli_fetch_assoc to push data into a row array.

$res = mysqli_query($link, $query);
$rows = array();
while($r = mysqli_fetch_assoc($res)) {
$rows[] = $r;
echo "<br>location:".$r["assigned"]."||".$r["amount"]."<br>";

The order I see in this php file and the order seen when I execute the query on mysql command line is different. The last and second last row are swapped in php.

In mysql query I am using an
order by timestamp

I've searched into mysql_fetch_assoc but didn't find any similar problems experienced by anyone else.

Answer Source

The order of data that is returned to PHP is the order that is returned to it by mySQL.

If the timestamps are identical and that's the only ORDER BY field, then yes, it is perfectly possible for mySQL to give different orders from the same query.

Why it would provide them in different orders between to identical queries is unknown; perhaps an artifact from caching or something like that? But the ultimate point is that if there is no sort order specified that differentiates records, then the database is free to provide them in any order it likes.

If you really want them in to always come in the same order, even when there are dups, the simple answer is to add your primary key field to the end of the ORDER BY clause. (you do have a unique primary key field, right? right??)