Getting around deleted auto-incremented rows

[weekendrockstar]

Sorry for the crappy title but it was the best that I could come up with for this. It could be quite easy to someone but I'm wracking my brains out on it.

Background:

I have a script that shows dynamic articles. The rows for each has the author, title, date, the body, etc... The pages (database rows) are indexed by an auto-incremented number upon saving.

I wanted each page to have links at the bottom of just the previous or the next article. Not full-blown pagination as that is done elsewhere. Just a previous link if there were articles written before the one being displayed and a next if it wasn't the most recent article created.

So I used:

$id = $_REQUEST['id'];
$previous_page = $id - 1;
 if(mysql_num_rows(mysql_query("SELECT id FROM articles WHERE id = '$previous_page'"))){
   echo "<a href=\"page.php?id=$previous_page\">Previous Page</a>";
}else{
   echo "<font color=#cccccc>Previous Page</a>";
 }

$next_page = $id + 1;
 if(mysql_num_rows(mysql_query("SELECT id FROM articles WHERE id = '$next_page'"))){
   echo "<a href=\"page.php?id=$next_page\">Next Page</a>";
}else{
   echo "<font color=#cccccc>Next Page</a>";
 }

This works perfectly fine if every article written were still in the database. But if someone deletes an article, that row is gone along with the article_id (the table's index).

So if the database has a row for articles 1,2,4,6 because article 3 and 5 were deleted then there'd be problems on the page displaying article 4. Even though there are both older and newer added articles the links will not be displayed because the code would be looking for pages 3 and 5 which do not exist.

So I need some help making the code check if there is a row with a lower and/or higher 'id' so that the links are shown if there were earlier or later articles if some have been deleted.

And lastly I'd need to know the number of the next lowest and next highest row to have the correct 'id' in the link.

I will try to clarify more if necessary. Definitely appreciate any help and all help/guidance!

[blocblue]

Hi,

I can see your problem. One option would be to do something like:

$id = (int)$_REQUEST['id'];
$nextSql = "SELECT * FROM articles WHERE id > {$id} ORDER BY id ASC LIMIT 1";
$prevSql = "SELECT * FROM articles WHERE id < {$id} ORDER BY id DESC LIMIT 1";

So the above would find the article with an id after the current article, and an id before the current article.

Does this help?

R.

[weekendrockstar]

Yes! Thank you Blocblue!

I had to do some messing around to get it to do what I want so I'll post my final code below which I always try to do so that anyone that may need this info has an idea of how to put your suggestion to work. I am having one problem with it which I will mention after my code. But it's not a huge deal. I will work on figuring it out but even if I don't then it's not a big deal as you'll see.

$id = $_REQUEST['id'];
 $previous_page = mysql_query("SELECT * FROM artiles WHERE id < {$id} ORDER BY id DESC LIMIT 1");
$next_page = mysql_query("SELECT * FROM articles WHERE id > {$id} ORDER BY id ASC LIMIT 1");
  
  while($row = mysql_fetch_array($previous_page)) {
   $page_id = $row['id'];
  if (mysql_num_rows($previous_page)== 1) {
  	echo "<a href=\"page.php?id=$page_id\">Previous Page</a>";
  }else{
  	echo "<font color=#cccccc>Previous Page</font>";
  }
 }


  while($row = mysql_fetch_array($next_page)) {
   $page_id = $row['id'];
  if (mysql_num_rows($next_page)== 1) {
  	echo "<a href=\"page.php?id=$page_id\">Next Page</a>";
  }else{
  	echo "<font color=#cccccc>Next Page</font>";
  }
 }

Now for my "problem".

As I look at the IF statement containing mysql_num_rows it should print the link if the previous or next 'row' exists in the database. However, the ELSE statement is behaving as suspected.

If I am on the first or last page where either the previous or next link should be grayed-out non-linked text, nothing is printed. It's not a huge deal as it isn't printing a link if it shouldn't. The first article has a next link but nothing is printed about a previous. So it doesn't break the script or anything by printing a false link or anything. But I was just curious why it's not printing the greyed-out text.

I will keep this thread open for a bit in case someone can point out the 'error' before I figure it out myself. Either way I will close this thread shortly and try to give blocblue credit for solving it.

A ton of thanks!

[blocblue]

Hi,

I think your problem can be easily resolved if you rearrange your code slightly. The problem is occurring because you're not checking for a row being returned until you're already inside your while loop, which the code won't enter if a row wasn't returned. Instead, try:

$id = $_REQUEST['id'];
$next_page = mysql_query("SELECT * FROM articles WHERE id > {$id} ORDER BY id ASC LIMIT 1");

if (mysql_num_rows($next_page)== 1) {
    while(($row = mysql_fetch_array($next_page))) {
        echo "<a href='page.php?id={$row['id']}'>Next Page</a>";
    }
} else {
    echo '<span style="color:#ccc;">Next Page</span>';
}

This checks whether a row was returned and then goes about displaying it if it was. Otherwise the link isn't rendered. I have also changed the <font> tag to a <span>, as <font> tags are now deprecated.

I think this should work.

Rob.