Prev/next Script

[section31]

whats the best way to do a prev/next script that cycles through images...would that take 3 queries? 1 query for the image, the 2nd query for the one after that, and 3rd query for the one before?

 

I've used several pagers out there, but none I know of would work for this

 

Requirements:

Images are going to be accessible via the primary key in the database. So a call like this index.php?id=5 will bring up image 5. Then I would like the script to check if a previous image exists before that by date, and if it does show prev button, else fade out or unlink prev button. The same goes for the next button. How can I do all of this in under 3 queries?

Edited May 12, 2005 by section31

[owatagal]

Honestly I would have said to adapt a pagination script, because those tend to come with error checking already (is the previous link greater than 0, is the next link within the total number of results, etc).

 

The biggest change you'd have to make would be taking out anything that let you change the LIMIT feature of the results, so that it could only be 1.

 

Just out of curiousity, what was it about pagination scripts that made you decide it wouldn't work for you?

[section31]

Ok, take this as an example.

http://section31.us/temp/new/section31/index.php?id=2

 

To do this, its really sad. I'm using 3 queries and I want to cut it down.

 

This is what my 3 queries look like. Color Coded.

 

>$image = mysql_fetch_assoc( mysql_query("SELECT * FROM images WHERE id = '{$_GET['id']}' ") );
$prevImage = @mysql_result( mysql_query("SELECT id FROM images WHERE time < '{$image['time']}' ORDER BY time DESC LIMIT 1"), 0 );
$prevImage = $prevImage ? '<a href="' . $_SERVER['PHP_SELF'] . '?id=' . $prevImage . '"><< Back</a>' : '';
$nextImage = @mysql_result( mysql_query("SELECT id FROM images WHERE time > '{$image['time']}' ORDER BY time ASC LIMIT 1"), 0 );
$nextImage = $nextImage ? '<a href="' . $_SERVER['PHP_SELF'] . '?id=' . $nextImage . '">Next</a>' : '';

Edited May 12, 2005 by section31

[owatagal]

Ok, I see. I'm not sure I see any easy way to cut the queries down, although I'm sure it could be done. If it helps, I googled "blog previous next links PHP" to see how other people were handling it. I found some hacks for WordPress, b2evolution, and MovableType; the hacks I looked at all run one query to get the next link and a second query to get the previous link. And that's in addition to whatever queries were running on the page itself. So I don't think your three queries are excessive; they seem like a normal approach to the problem. Sorry I can't help; for what it's worth, if I ever add a prev/next feature to my blog, I'd drop the two extra queries in there without hesitation.

 

Edit to add: I know you aren't dealing with a blog, per se, but the setup is basically the same--chronologial entries, etc etc.

Edited May 13, 2005 by owatagal

[section31]

ok, well thanks anyway.

 

Anyone else out there know how I can do this in less than 3 queries?

[section31]

I just realized I could use subqueries, but we don't have mysql version 4.1 yet. So am I pretty much stuck with 3 queries?