View Full Version : php/mysql next and previous rows

12-20-2010, 11:14 AM
I have a dynamic PHP page that shows information from the db depending on which image the user has clicked on.

now i want next and previous buttons to go to the next and previous rows in the db and display the new content.

i know i can use $next = $id + 1; but i dont know how to implement it into my current query, and what if there new ID number has been deleted, they may not exactly increment 1,2,3,4,5.. etc.

Ideally i would like to query it by a text entry rather than a number.

my current query

$result = mysql_query("SELECT id, category, path, client, main_imgurl, role, url FROM `portfolio` WHERE path = '$work'");
if (!$result) { echo 'Could not run query: ' . mysql_error(); exit; }

$row = mysql_fetch_row($result);

12-20-2010, 02:33 PM
You don't need to use the ID.

When you did your query, the "result" is an array of rows.
That array is an unbroken sequence (0,1,2,3 ... etc).

If your result contains 50 photos, the array will be from 0 to 49.
Here is an idea of what I mean ... sort of a simplistic example that will
get you in the right direction.

There might be more efficient ways of doing this ... perhaps you may
want to investigate the MySQL LIMIT parameter. I just throw together the
example below as an example of using the array.

The query itself is really the key to all of this. If you specifically query for
one thing, your query result might only be one row. You can't have a prev and next
if you only have one result. The point of having a "page" or "prev and next", is if
you expect enough results to warrant a navigation system.


// You need to do the query right away, so you get a count of how many rows are in the result ...
$result = mysql_query("SELECT id, category, path, client, main_imgurl, role, url FROM `portfolio` WHERE path = '$work' ");

// You need to know how many are in your result, so you can wrap-around the first and last photo ...
$num_rows = mysql_num_rows($result);

// default starting values ...

// if the URL contains any variables (prev and next), use them instead ...
// remember that arrays start at zero (0), so if you have 50 photos,
// they will be in the array as elements 0,1,2,3 ... etc.


// Display only the current photo ...
while($row = mysql_fetch_array($result)) {
echo "<img src='{$row['main_imgurl']} /><br />";

// determine the "new" prev and next values ...
// wrap around if you are at the beginning or the end of your list of photos ...

<a href='photo.php?p=$prev'>PREV</a>
<a href='photo.php?p=$next'>NEXT</a>