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

I had the same problem as Dan, so I used his answer and improved it.

First select the row index, nothing different here.

SELECT row
FROM 
(SELECT @rownum:=@rownum+1 row, a.* 
FROM articles a, (SELECT @rownum:=0) r
ORDER BY date, id) as article_with_rows
WHERE id = 50;

Now use two separate queries. For example if the row index is 21, the query to select the next record will be:

SELECT * 
FROM articles
ORDER BY date, id
LIMIT 21, 1

To select the previous record use this query:

SELECT * 
FROM articles
ORDER BY date, id
LIMIT 19, 1

Keep in mind that for the first row (row index is 1), the limit will go to -1 and you will get a MySQL error. You can use an if-statement to prevent this. Just don't select anything, since there is no previous record anyway. In the case of the last record, there will be next row and therefor there will be no result.

Also keep in mind that if you use DESC for ordering, instead of ASC, you queries to select the next and previous rows are still the same, but switched.

Answer 2

My solution to get the next and previews record also to get back to the first record if i'm by the last and vice versa

I'm not using the id i'm using the title for nice url's

I'm using Codeigniter HMVC

$id = $this->_get_id_from_url($url);

//get the next id
$next_sql = $this->_custom_query("select * from projects where id = (select min(id) from projects where id > $id)");
foreach ($next_sql->result() as $row) {
    $next_id = $row->url;
}

if (!empty($next_id)) {
    $next_id = $next_id;
} else {
    $first_id = $this->_custom_query("select * from projects where id = (SELECT MIN(id) FROM projects)");
    foreach ($first_id->result() as $row) {
        $next_id = $row->url;
    }
}

//get the prev id
$prev_sql = $this->_custom_query("select * from projects where id = (select max(id) from projects where id < $id)");
foreach ($prev_sql->result() as $row) {
    $prev_id = $row->url;
}

if (!empty($prev_id)) {
    $prev_id = $prev_id;
} else {
    $last_id = $this->_custom_query("select * from projects where id = (SELECT MAX(id) FROM projects)");
    foreach ($last_id->result() as $row) {
        $prev_id = $row->url;
    }     
}

Answer 3

Select top 1 * from foo where id > 4 order by id asc

Answer 4

This is universal solution for conditions wiht more same results.

<?php
$your_name1_finded="somethnig searched"; //$your_name1_finded must be finded in previous select

$result = db_query("SELECT your_name1 FROM your_table WHERE your_name=your_condition ORDER BY your_name1, your_name2"); //Get all our ids

$i=0;
while($row = db_fetch_assoc($result)) { //Loop through our rows
    $i++;
    $current_row[$i]=$row['your_name1'];// field with results
    if($row['your_name1'] == $your_name1_finded) {//If we haven't hit our current row yet
        $yid=$i;
    }
}
//buttons
if ($current_row[$yid-1]) $out_button.= "<a  class='button' href='/$your_url/".$current_row[$yid-1]."'>BUTTON_PREVIOUS</a>";
if ($current_row[$yid+1]) $out_button.= "<a  class='button' href='/$your_url/".$current_row[$yid+1]."'>BUTTON_NEXT</a>";

echo $out_button;//display buttons
?>

Answer 5

If you have an index column, say id, you can return previous and next id in one sql request. Replace :id with your value

SELECT
 IFNULL((SELECT id FROM table WHERE id < :id ORDER BY id DESC LIMIT 1),0) as previous,
 IFNULL((SELECT id FROM table WHERE id > :id ORDER BY id ASC LIMIT 1),0) as next

Answer 6

CREATE PROCEDURE `pobierz_posty`(IN iduser bigint(20), IN size int, IN page int)
BEGIN
 DECLARE start_element int DEFAULT 0;
 SET start_element:= size * page;
 SELECT DISTINCT * FROM post WHERE id_users .... 
 ORDER BY data_postu DESC LIMIT size OFFSET start_element
END