[Resolved] Previous, Next rows

mikeatrpi

I'm trying to find the IDs of the previous and next rows of data in relation to a given row. The following result set is sorted by wo_comp_date DESC.

+------+--------------+
| id | wo_comp_date |
+------+--------------+
| 28 | 2004-01-12 |
| 27 | 2004-01-12 |
| 21 | 2004-01-11 |
| 25 | 2004-01-11 |
| 23 | 2004-01-10 |
+------+--------------+

I'd like to find that the previous of 21 is 27, the next of 21 is 25. Same for 25: previous = 21, next = 23.

I am using mySQL 3.23.

Thank you in advance for your help. I'm truly stumped on this one!

LordShryku

So just do the SQL query like you are, add a LIMIT on there.

mikeatrpi

I am familiar with LIMIT, however I don't know how to make it "start" at the ID I want. Say I want the "next" of ID = 25. That would be ID 23. How would I write an SQL statement to find that?

LordShryku

Actually, now that I think about it, you're probably going to be better off doing this is the loop. Here's what I would do...

$id  = 25; // specify your target id here
$sql = "SELECT id
          FROM table
      ORDER BY wo_comp_date";
$res = mysql_query($sql);
$arr = array(); // blank array for results
while($row = mysql_fetch_array($res)) {
   $arr[] = $row['id']; // put id's in the array
}

$key  = array_search($id,$arr); // find the target
if(isset($arr[$key+1])) {
   // find if there is a next value
   $next = $arr[$key+1];
}

Same can be done to find the previous value. Just use $key-1 instead.

mikeatrpi

Thank you for the informative reply with code.

Now, lets say I have 8000 records in my table- will this loop kill performance? I was hoping for an SQL solution to avoid the linear search. I mean, I know best case or optimistic case is that you find the previous and next before hitting the end of the loop. But there is always the possibility you have to traverse the entire result set.

I was trying to do something like this:

SELECT a.id FROM tablename a, tablename b 
WHERE b.id = 25 AND a.wo_comp_date <= b.wo_comp_date
 ORDER BY wo_comp_date LIMIT 2;

I'm beginning to see that the linear approach might be my only option! Thanks again!

EDIT: Actually I can think of ways to cut down on the set a little bit. For instance, only query the current and sequential date (to handle the borderline case)- that will reduce my result set size by a lot, and then I can linear search that.