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

Question

Hello all,

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

Answer 2

If you run a modern version of MySQL, you can simplify your function into

function getNextTweet($key, $direction)
{
    $cmp   = ($direction == 'next') ? '>' : '<';
    $order = ($direction == 'next') ? 'ASC' : 'DESC';

    $sql = "SELECT *
              FROM tweets
             WHERE tweet_id $cmp (SELECT tweet_id FROM tweets WHERE tweet_key = '$key' LIMIT 1)
          ORDER BY tweet_id $order
             LIMIT 1";

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

    return mysql_fetch_assoc($result);
}

As long as "tweet_id" is indexed, the query will be very fast, even for millions of records.

One last thing, make sure that $key is properly validated! Otherwise, anyone can inject SQL in your query, that's a huge security flaw. If $key is anything but a hash key, it should at least go through mysql_real_escape_string()