Next record - with a bit of a twist

[meames]

I have a table of user reviews and want a way of selecting the next record from the database. The table is set out like:
ID Review
1 review1
1 review2
1 review3
2 review4
3 review5
2 review6
1 review7

Now I have a system to bring up the first review depending on the ID and then to move onto the next review, but I cannot seem to be able to bring up the next review depending on ID - ie If I was viewing 'review3' the next one I want to view is 'review7'.

Tried googling, but yet again meames don't know how to put it to get a half decent result!

Any help on this is greatly appreciated.

Cheers guys.

[vinyl-junkie]

Just add:

ORDER BY id, review

to your SELECT statement and you'll get them in the order you want to see them.

[meames]

But won't this scroll through all of the reviews, I would want it to stop and go back to the first review when it runs out of reviews for the ID.

[vinyl-junkie]

Using the ORDER BY clause will sort the records like this, which from what you're telling me is what you want.

1 review1
1 review2
1 review3
1 review7
2 review4
2 review6
3 review5

[meames]

It is very hard for me to describe, but basically, if a visitor is reading reviews with ID of 1 I want the page to circle the reviews:

1 review1
1 review2
1 review3
1 review7
1 review1
1 review2

[vinyl-junkie]

You can write code that tells the page when it's reached the last review for this id, start over. Without knowing what language you're doing this in, it's a little hard to give you a meaningful example.

[TheShaner]

So basically you don't want the other IDs to show up at all in the query? Ok, here you go:
SELECT ID, Review
FROM tableReviews
WHERE ID = 1
ORDER BY Review
Now, I've hardcoded which ID will be selected in the query, but you can easily just do something like (if using PHP):
$selectedID = $_GET['ID']; /* Use $_POST if method="POST" */
$query = "SELECT ID, Review FROM tableReviews WHERE ID = " . $selectedID . " ORDER BY Review"
-Shane