CODEWITHSUNDEEP
phpmysql
Joscplan
Joscplan

Reputation: 1034

Selecting the previous or next row based on ID

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.

Upvotes: 1

Views: 127

Answers (2)

KTAnj
KTAnj

Reputation: 1356

$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

Upvotes: 0

Fabio
Fabio

Reputation: 23480

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?

Upvotes: 1

Related Questions