MYSQL - Get Next and Previous Record by ID - HTML for hyperlinks

Question

So I'm trying to add a little bit of convenience to a CRUD by adding next and previous links to navigate between records in my database.

Here are my queries:

$id=$_GET['id'];
$id = $currentid;
$prevquery= "SELECT * FROM inventory WHERE id < $currentid ORDER BY id DESC LIMIT 1"; 
$prevresult = mysql_query($prevquery);

$nextquery= "SELECT * FROM inventory WHERE id > $currentid ORDER BY id ASC LIMIT 1"; 
$nextresult = mysql_query($nextquery);
?>

Here is my HTML:

<a href="http://www.url.com/crud/edit.php?id=<?php echo $prevresult; ?> ">Previous</a>

<a href="http://www.url.com/crud/edit.php?id=<?php echo $nextresult; ?> ">Next</a>

Now I tested these queries in PHPMyAdmin and they produced the result I wanted, but I can't get my hyperlinks to actually be supplied with the correct IDs... they're just blank after the =. What am I doing wrong?

Solution

mysql_query() returns a result set (resource). To get the actual rows from the result set, you need to use a function like mysql_fetch_row().

Your code for the "next" link would look something like:

PHP

$nextquery= "SELECT * FROM inventory WHERE id > $currentid ORDER BY id ASC LIMIT 1"; 
$nextresult = mysql_query($nextquery);
if(mysql_num_rows($nextresult) > 0)
{
    $nextrow = mysql_fetch_row($nextresult);
    $nextid  = $nextrow['id'];
}

HTML

<a href="http://www.url.com/crud/edit.php?id=<?php echo $nextid; ?> ">Next</a>

and the previous link would be done similarly.

Obligatory note: For new code, you should seriously consider using PDO.

Advanced note: You could combine your queries into a single query like:

SELECT
  (
    SELECT id
    FROM inventory WHERE id < $currentid ORDER BY id DESC LIMIT 1
  ) AS previd,
  (
    SELECT id
    FROM inventory WHERE id > $currentid ORDER BY id ASC LIMIT 1
  ) AS nextid

And then adjust the logic accordingly.

Comments

[Madara's Ghost]

Please, don't use mysql_* functions for new code. They are no longer maintained and the community has begun the deprecation process. See the red box? Instead you should learn about prepared statements and use either PDO or MySQLi. If you can't decide, this article will help to choose. If you care to learn, here is good PDO tutorial.

[user3553260]

shouldn't it be $currentid = $id;?

[antiquarichat]

That's probably a step in the right direction, but my error log is saying: [25-Jun-2012 12:22:47] PHP Warning: Wrong parameter count for mysql_num_rows() in /url/usr/public_html/crud/edit.php on line 144 [25-Jun-2012 12:22:47] PHP Warning: Wrong parameter count for mysql_num_rows() in /url/usr/public_html/crud/edit.php on line 152

[jedwards]

Ya, you have to pass it the result resource, try the updated code.

[antiquarichat]

Now getting: PHP Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in....

[jedwards]

That means there is an error -- add print_r(mysql_error()); after the mysql_query() line and see what it says.

[antiquarichat]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY id ASC LIMIT 1' at line 1

[jedwards]

Print your query too, to make sure it is formatted correctly: print_r($nextquery).

[jedwards]

Since you're limiting your result set to a size of 1, you don't need the while loop, it would, however, hide the error that would occur when the query returned a result set of size 0. It may not matter to you since you're still learning, but you should at least understand why the while loop is unnecessary (and possibly bug-introducing). Anyway, congrats on getting it to work!