How to get next/previous record in MySQL?

Question

Say I have records with IDs 3,4,7,9 and I want to be able to go from one to another by navigation via next/previous links. The problem is, that I don't know how to fetch record with nearest higher ID.

So when I have a record with ID 4, I need to be able to fetch next existing record, which would be 7. The query would probably look something like

SELECT * FROM foo WHERE id = 4 OFFSET 1

How can I fetch next/previous record without fetching the whole result set and manually iterating?

I'm using MySQL 5.

Answer 1

How to get next/previous record in MySQL & PHP?

My example is to get the id only

function btn_prev(){

  $id = $_POST['ids'];
  $re = mysql_query("SELECT * FROM table_name WHERE your_id < '$id'  ORDER BY your_id DESC LIMIT 1");

  if(mysql_num_rows($re) == 1)
  {
    $r = mysql_fetch_array($re);
    $ids = $r['your_id'];
    if($ids == "" || $ids == 0)
    {
        echo 0;
    }
    else
    {
        echo $ids;
    }
  }
  else
  {
    echo 0;
  }
}



function btn_next(){

  $id = $_POST['ids'];
  $re = mysql_query("SELECT * FROM table_name WHERE your_id > '$id'  ORDER BY your_id ASC LIMIT 1");

  if(mysql_num_rows($re) == 1)
  {
    $r = mysql_fetch_array($re);
    $ids = $r['your_id'];
    if($ids == "" || $ids == 0)
    {
        echo 0;
    }
    else
    {
        echo $ids;
    }
  }
  else
  {
    echo 0;
  }
}

Answer 2

SELECT * FROM foo WHERE id>4 ORDER BY id LIMIT 1

Answer 3

I think to have the real next or previous row in SQL table we need the real value with equal, (< or >) return more than one if you need to change position of row in a ordering table.

we need the value $position to search the neighbours row In my table I created a column 'position'

and SQL query for getting the needed row is :

for next :

SELECT * 
FROM `my_table` 
WHERE id = (SELECT (id) 
            FROM my_table 
            WHERE position = ($position+1)) 
LIMIT 1

for previous:

 SELECT * 
 FROM my_table 
 WHERE id = (SELECT (id) 
             FROM my_table 
             WHERE `position` = ($position-1)) 
 LIMIT 1

Answer 4

Using @Dan 's approach, you can create JOINs. Just use a different @variable for each sub query.

SELECT current_row.row, current_row.id, previous_row.row, previous_row.id
FROM (
  SELECT @rownum:=@rownum+1 row, a.* 
  FROM articles a, (SELECT @rownum:=0) r
  ORDER BY date, id
) as current_row
LEFT JOIN (
  SELECT @rownum2:=@rownum2+1 row, a.* 
  FROM articles a, (SELECT @rownum2:=0) r
  ORDER BY date, id
) as previous_row ON
  (current_row.id = previous_row.id) AND (current_row.row = previous_row.row - 1)

Answer 5

If you want to feed more than one id to your query and get next_id for all of them...

Assign cur_id in your select field and then feed it to subquery getting next_id inside select field. And then select just next_id.

Using longneck answer to calc next_id:

select next_id
from (
    select id as cur_id, (select min(id) from `foo` where id>cur_id) as next_id 
    from `foo` 
) as tmp
where next_id is not null;

Answer 6

In addition to cemkalyoncu's solution:

next record:

SELECT * FROM foo WHERE id > 4 ORDER BY id LIMIT 1;

previous record:

SELECT * FROM foo WHERE id < 4 ORDER BY id DESC LIMIT 1;

edit: Since this answer has been getting a few upvotes lately, I really want to stress the comment I made earlier about understanding that a primary key colum is not meant as a column to sort by, because MySQL does not guarantee that higher, auto incremented, values are necessarily added at a later time.

If you don't care about this, and simply need the record with a higher (or lower) id then this will suffice. Just don't use this as a means to determine whether a record is actually added later (or earlier). In stead, consider using a datetime column to sort by, for instance.