Move Next with unordered ID column

[yamantaka]

Thanks in advance.

I'm trying to move to the next record in a query using php/MySQL

A simplified query is below:

$result = mysql_query("SELECT id FROM table ORDER BY datefield, id ASC");

$row = mysql_fetch_object($result);

$id = $row->id;

This simply selects the table data and orders it by a date field first, then id.

Normally, if the query was only ordered by ID, we could be assured that the ID was increasing as we move forward row by row.

If this was the case, to get the next row I would use:

$next_result = mysql_query("SELECT id FROM table WHERE id > '$id' ORDER BY id ASC LIMIT 1

$next_row = mysql_fetch_object($next_result);

$next_id = $next_row->id;

This normally works perfectly, however the problem here is that the primary order of the initial query is by a date field, then ID. The date field does not necessarily have any relation to the order of the ID

That means that you can get data ordered like this:

   Date       |   ID

---

2010-08-01 | 1
2010-08-01 | 4
2010-08-02 | 3
2010-08-02 | 2

You can probably see the problem here.. we cannot move next by simply getting the next highest ID in the query because the ID is not in increasing order.

Ive googled.. i've yahood.. ive pulled out hair but I cant find a solution.

having said that, it is probably pretty obvious.

Any assistance appreciated.

[bradgrafelman]

Er... why are you executing a new query to get the next row? Just call the mysql_fetch_*() function a second time.

[Crowly]

All or atleast most the the examples i've seen put this in a loop, usually a while loop. Something like this

$sql="SELECT id FROM table ORDER BY datefield, id ASC";

$result=mysql_query($sql);

while ($row=mysql_fetch_row($result))
  echo $row[0]."<br />";

mysql_free_result($result);

[paulnaj]

Apart from the comments above, I'm confused as to what you actually want to happen.

If you want to cycle through the records with increasing ID, then don't ORDER BY the datefield, just use the ID field.