Selecting the previous or next row based on ID

Question

I have a mysql table where I want to get the inferior (i.e. previous) or superior (i.e. next) item based on the ID of the current selected value.

Like this:

$id_ani = $cap['id_ani']; //Current id
$id_capnex = $cap['id_cap']+1; //Superior id
$nexcap = mysql_query("SELECT * FROM m_table WHERE id_cap=$id_capnex");
$id_caprev =  $cap['id_cap']-1; //Inferior id
$prevcap = mysql_query("SELECT * FROM m_table WHERE id_cap=$id_caprev");

So in the code I have the Current id, Superior id, and Inferior id.

The problem here is that if the current id = 1 and the only existing id after 1 is 4 the Superior id would be '1'+1 instead of 4 which is the only existing id after 1. The same applies if I did it with a inferior one.

Is there a way to select 4 instead of 2 and do the same with the inferior id?

Thanks in advance.

score 1 · Accepted Answer · edited May 23 '17 at 10:32

Try to use greater than > or less than <

$id_capnex = $cap['id_cap']+1; //Superior id
$nexcap = mysql_query("SELECT * FROM m_table WHERE id_cap=$id_capnex");
$id_caprev =  $cap['id_cap'];
$prevcap = mysql_query("SELECT * FROM m_table WHERE id_cap<$id_caprev orber by id_cap ASC");
//inferior
$prevcap = mysql_query("SELECT * FROM m_table WHERE id_cap>$id_caprev orber by id_cap ASC");
//superior

As side note i'd add that mysql_* function are deprecated and no longer maintained, so I think it's time to switch either to PDO or mysqli and use prepared statements to avoid any risk of mysql injections, learn more here How can I prevent SQL injection in PHP?

Thanks for the advice, it works just needed to add LIMIT 1 so it wouldn't throw all the values greater than the current id. — Joscplan, Jun 20 '13 at 00:03

score 0 · Answer 2 · answered Sep 05 '13 at 07:23

$limit_id=$cap['id_cap']+2

Using bellow Query, you can get prev and next . The result of this query will returns two rows . first one is previous and other one is next.

SELECT * , IF( id >$cap['id_cap'], 1, 0 ) AS NextFlag, IF( id <$cap['id_cap'], 1, 0 ) AS PrevFlag FROM m_table 
WHERE id_cap <$limit_id
HAVING NextFlag =1
OR PrevFlag =1
ORDER BY id_cap DESC
LIMIT 2

Selecting the previous or next row based on ID

2 Answers2