Topic awaiting preservation: getting PREVIOUS and NEXT values from db

Let's say I have this database that has tons of records in it (in this case, articles). At the bottom of each page, I'd like to do a PREVIOUS ARTICLE and a NEXT ARTICLE link. So, I need to get the info from the database in order to construct the URLs for these links.

Using $aid as a URL variable to indicate the CURRENT article, I'm doing this ('article' in the select statements is the field in the table that holds the ID number ($aid)):

<?php
if (!isset($aid)){
$aid = 500; // just in case I'm lazy
}

// Let's get the NEXT value
$sql = "select article from articles where article > $aid order by article asc LIMIT 1";
mysql_query($sql) or die ("<b>SQL query failed getting NEXT value:</b><br />$sql<br /><br />");
$result = mysql_query($sql);
$myrow = mysql_num_rows($result);

if ($myrow > 0){
while ($row = mysql_fetch_row($result)) {
$next = $row[0];
}
}else{
$next = "No rows, bud";
}
// Let's get the PREVIOUS value
$sql = "select article from articles where article < $aid order by article desc LIMIT 1";
mysql_query($sql) or die ("<b>SQL query failed getting PREVIOUS value:</b><br />$sql<br /><br />");
$result = mysql_query($sql);
$myrow = mysql_num_rows($result);

if ($myrow > 0){
while ($row = mysql_fetch_row($result)) {

$previous = $row[0];
}
}else{
$previous = "No rows, bud";
}
print "Previous: $previous<br />\n";
print "Target: $aid<br />\n";
print "Next: $next<br />\n";
?>

As you can see, I'm making two selects, and checking them each with a mysql_num_rows to make sure there is actually data being returned. Is this the best way?

I thought about doing a select like:
$sql = "select article from articles where article = $aid-1 order by article asc LIMIT 3";

and then looping through them. The first is the PREVIOUS record, and the last is the NEXT record. But if the user is at the first record, the select will likely fail, since there won't be a record with a negative value.

My method posted above DOES work. I'm just curious as to if this is the best way.

Best I can figure, your other idea should work fine (although you put = rather than >=).

select article from articles where article >= $aid-1 order by article asc LIMIT 3

Granted, you still need to check to make sure that the first value is less than $aid to solve the beginning problem (and also check whether only 2 results were returned for the ending case), but that strikes me as more efficient than making more DB queries.

-jiblet

Check the webmoney.com tutorial on mySQL and php. If I recall, queries are numbered much like arrays are, no? If not, you can always use mysql_fetch_array. Um.. the currect number can be held in the variable and then you simply link to x - 1 and x - 2.

Shine and shine.

That's assuming that there are no "missing" numbers. In my case, there are. And, I need to check several things, such as
"select id from articles where status = 'online' and id > '$id' order by id limit 1";