[RESOLVED] MySQL Next Record

areid

Is there any easy way to see if there is a next or previous record in a MySQL table without looping through all the records?

If I select a row, can I ask if there is a row before and/or after that row?

Thanks again!

nemonoman

areid wrote:
Is there any easy way to see if there is a next or previous record in a MySQL table without looping through all the records?

No.

areid wrote:
If I select a row, can I ask if there is a row before and/or after that row?

No.

Assuming you are talking about MySQL.

Or to be more precise, if you want to do this, you will need to gather information about the rows you have returned.

If you are displaying a row at a time, you do know already if there are more rows in the set of returned rows, or if there was a row previous to the one being displayed.

I suspect what you are asking is "If my query returns a set of XXX number of records, does MySQL sort of number those records 1 - XXX, and keep that information hanging around for later access? and can then I simply say "Show me record 3" or something similar.

No.

Piranha

Before and after actually have no meaning in a RDBMS, it is all up to how you order the result from the query. So no, since it is not possible to know what is before and what is after you can't see next/previous row.

Having said that it is of cource possible that you have some way to order the result, let's as an example say that you sort by id and want id number 5. Then you could run 3 queries, one to get where id = 5, one to count where id < 5 and one to count where id > 5. The result of the counts should of course be > 0 if there are any row.

areid

Ok thanks. I will just do a loop

Piranha

But to do a loop seems the wrong way, at least it usually is when it comes to databases. Can you please show what result you want with an example to make it possible to get solutions.

areid

I have a list of galleries in a table.
ID - NAME
3 - gallerya
1 - galleryb
4 - galleryc
2 - galleryd

Lets say I'm in galleryb. I want to know what the names of the gallery in the next and previous records. The table has a unique ID, but isn't always sequential.

Piranha

Then you should do what I suggested above. There is no reason to use a loop and go through all the records.

areid

Could you help me create that. I'm not quite sure how to go about doing that.

Piranha

I give you an example of the record you want and the query to show if there are previous ids. From that it would be easy to do another query to see if there are higher ids.

// The query that show the current record:
$sql = "SELECT something FROM somewhere WHERE id = 5";
$result = mysql_query($sql);
if ($row = mysql_fetch_assoc($result)
{
    echo something
}

// The query that show if there are records with lower id:
$sql = "SELECT count(*) as c FROM somewhere WHERE id < 5";
$result = mysql_query($sql);
if ($row = mysql_fetch_assoc($result) && $row['c'] > 0)
{
    echo "There are records with lower id";
}
else
{
    echo "There are no records with lower id";
}