How do I get previous and next rows even though some rows have been deleted?

Question

I have the following PHP functions that determine the next and previous rows in a database. However, there are lots of occasions when rows can be deleted and therefore my functions will not work as all they do is decrement the auto_increment field.

For example, current row 5. My function gives: 4 (previous) and 6 (next). What if 6 and 7 is deleted. My best idea is to keep querying until I get a row, but this seems inefficient, is there a better way?

Thanks all

//function to get next tweet
function getNextTweet($key, $direction){

$sql = "SELECT tweet_id FROM tweets WHERE tweet_key = '$key' LIMIT 1";

$result = mysql_query($sql) or die("DB Error : ". mysql_error());

$result = mysql_fetch_assoc($result);

if($direction=='next'){

    $tweet_id = $result['tweet_id'] + 1;

}else{

    $tweet_id = $result['tweet_id'] - 1;

}

$sql = "SELECT * FROM tweets WHERE tweet_id = '$tweet_id' LIMIT 1";

$result = mysql_query($sql) or die("DB Error : ". mysql_error());   

return mysql_fetch_assoc($result);

}

Answer 1

Assuming you don't have a bajillion records...

Previous:

SELECT * 
FROM table 
WHERE (id < currentID) 
ORDER BY id DESC 
LIMIT 1

Next

SELECT * 
FROM table 
WHERE (id > currentID) 
ORDER BY id ASC 
LIMIT 1