Display next and previous rows

[Guest oscarthecat]

Hi, I need help creating a query that will be able to show the next and previous rows from my mysql table when the current row is selected.The trouble is the table has not got a single column to order the results by.Example of my table:Type | Number | ID----------------------HA......1234........1HA......1235........6HA......1236........3MSR....1234........9MSR....1235........2MSR....1236........0TY.......1234........7TY.......1235........8I need to first order by Type then by Number then display the next and previous rows.Example of required output:If “HA 1236” is selected thenNext Row = “MSR 1234”Previous Row = “HA 1235”Any help will be greatly appreciated.

[justsomeguy]

It doesn't matter how the rows are ordered, you can order them by whatever you want.SELECT * FROM table ORDER BY type, numberTo find the next and previous rows you need to know which row you're on and you need to loop through the result set looking for the current row and saving the last one you found. When you find the current one you know the last one you found is the previous row.

$prev = false;$next = false;$found = false;while ($row = mysql_fetch_assoc($result)){  if ($found)  {	$next = $row;	break;  }  elseif ($row['id'] == $current_row)  {	$found = true;  }  else	$prev = $row;}if ($prev){  echo "found previous row:";  print_r($prev);}if ($next){  echo "found next row:";  print_r($next);}

That code won't work by itself, you still need the database connection stuff, the actual query, and the $current_id variable populated with the ID of the current or selected row.