Random row, but with next and previous button

Question

I have a MySQL database with pictures within it. When a user comes to my page I want to show him random picture and from then he can go to the next or previous picture (not random)

Database example

id | picture 
11 | foto1.gif
12 | foto2.gif
16 | foto3.gif
23 | foto4.gif
66 | foto5.gif

How can I make something like that with PHP and SQL? Thank you!

possible duplicate of [What is the best way to pick a random row from a table in MySQL?](http://stackoverflow.com/questions/142242/what-is-the-best-way-to-pick-a-random-row-from-a-table-in-mysql) — Bobby, Apr 18 '11 at 13:57
It's not a duplicate. The linked question does not mention retrieving the next/previous entry. — ThiefMaster, Apr 18 '11 at 13:58

score 2 · Answer 1 · answered Apr 18 '11 at 13:57

2

Use ORDER BY rand() in your query to get the random item.

To retrieve the next/previous item (by id), use this:

WHERE id < current_id ORDER BY id DESC LIMIT 1   (for prev)
WHERE id > current_id ORDER BY id ASC LIMIT 1   (for next)

answered Apr 18 '11 at 13:57

ThiefMaster

310,957
84
592
636

Ok, but what clickable link can I make to go to the next or prev picture – Nathaniel Apr 18 '11 at 14:03
You add something like `go=next` or `go=prev` to the query string and then create an appropriate WHERE statement in your PHP code. – ThiefMaster Apr 18 '11 at 14:06
Aha, so it seems to go without saying that the currently displayed picture's ID should somehow make it to the query string as well... – Andriy M Apr 18 '11 at 15:03
It doesn't work, because when random will give id 66, you can't go to the next picture... – Nathaniel Apr 18 '11 at 16:29
Then check for this case and return the image with the lowest id in that case – ThiefMaster Apr 18 '11 at 17:24
Ok, but I don't know what the highest number is, users can upload new pictures – Nathaniel Apr 18 '11 at 18:44
you don't need to know it. simply test if your query did not return any rows. – ThiefMaster Apr 18 '11 at 20:15

Random row, but with next and previous button

1 Answers1