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? <br /><br />I've used several pagers out there, but none I know of would work for this <br /><br /><b>Requirements:</b><br />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?<!--content-->
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).<br /><br />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.<br /><br />Just out of curiousity, what was it about pagination scripts that made you decide it wouldn't work for you?<!--content-->
Ok, take this as an example. <br /><a href="http://section31.us/temp/new/section31/index.php?id=2" target="_blank">http://section31.us/temp/new/section31/index.php?id=2</a><br /><br />To do this, its really sad. I'm using 3 queries and I want to cut it down. <br /><br />This is what my 3 queries look like. <a href="http://section31.us/temp/new/section31/example.phps" target="_blank">Color Coded.</a><br /><br /><!--c1--><div class='codetop'>CODE</div><div class='codemain'><!--ec1-->$image = mysql_fetch_assoc( mysql_query("SELECT * FROM images WHERE id = '{$_GET['id']}' ") );<br />Â Â Â Â $prevImage = @mysql_result( mysql_query("SELECT id FROM images WHERE time < '{$image['time']}' ORDER BY time DESC LIMIT 1"), 0 );<br />Â Â Â Â $prevImage = $prevImage ? '<a href=http://www.totalchoicehosting.com/forums/lofiversion/index.php/"' . $_SERVER['PHP_SELF'] . '?id=' . $prevImage . '"><< Back</a>' : '';<br />Â Â Â Â $nextImage = @mysql_result( mysql_query("SELECT id FROM images WHERE time > '{$image['time']}' ORDER BY time ASC LIMIT 1"), 0 );<br />Â Â Â Â $nextImage = $nextImage ? '<a href=http://www.totalchoicehosting.com/forums/lofiversion/index.php/"' . $_SERVER['PHP_SELF'] . '?id=' . $nextImage . '">Next</a>' : '';<!--c2--></div><!--ec2--><!--content-->
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.<br /><br />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.<!--content-->
ok, well thanks anyway.<br /><br />Anyone else out there know how I can do this in less than 3 queries?<!--content-->
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?<!--content-->
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).<br /><br />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.<br /><br />Just out of curiousity, what was it about pagination scripts that made you decide it wouldn't work for you?<!--content-->
Ok, take this as an example. <br /><a href="http://section31.us/temp/new/section31/index.php?id=2" target="_blank">http://section31.us/temp/new/section31/index.php?id=2</a><br /><br />To do this, its really sad. I'm using 3 queries and I want to cut it down. <br /><br />This is what my 3 queries look like. <a href="http://section31.us/temp/new/section31/example.phps" target="_blank">Color Coded.</a><br /><br /><!--c1--><div class='codetop'>CODE</div><div class='codemain'><!--ec1-->$image = mysql_fetch_assoc( mysql_query("SELECT * FROM images WHERE id = '{$_GET['id']}' ") );<br />Â Â Â Â $prevImage = @mysql_result( mysql_query("SELECT id FROM images WHERE time < '{$image['time']}' ORDER BY time DESC LIMIT 1"), 0 );<br />Â Â Â Â $prevImage = $prevImage ? '<a href=http://www.totalchoicehosting.com/forums/lofiversion/index.php/"' . $_SERVER['PHP_SELF'] . '?id=' . $prevImage . '"><< Back</a>' : '';<br />Â Â Â Â $nextImage = @mysql_result( mysql_query("SELECT id FROM images WHERE time > '{$image['time']}' ORDER BY time ASC LIMIT 1"), 0 );<br />Â Â Â Â $nextImage = $nextImage ? '<a href=http://www.totalchoicehosting.com/forums/lofiversion/index.php/"' . $_SERVER['PHP_SELF'] . '?id=' . $nextImage . '">Next</a>' : '';<!--c2--></div><!--ec2--><!--content-->
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.<br /><br />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.<!--content-->
ok, well thanks anyway.<br /><br />Anyone else out there know how I can do this in less than 3 queries?<!--content-->
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?<!--content-->