mysql select question

Hi!

I am wondering how I can find out the index of a row in a MySql table given a certain order. For example I have a table containing information of some images on my computer. After

SELECT * FROM Images WHERE Id='12' ORDER BY Date

I get an Image and want to find the next image (next in the Date order). So I would like something as

index = GETINDEX FROM Images WHERE Id='12' ORDER BY Date
nextimage = SELECT * FROM Images WHERE ORDER BY Date LIMIT index+1, index+1

Someone know how to code this into a proper SQL query?

thanks,
pieter

$imageNo=4; // Image number you want to view

$from=$imageNo-1;
$sql="SELECT * FROM images ORDER BY Date LIMIT $from,$imageNo";

Yes, this is the way to get a certain row with a certain index. But how do I find out the correct value of $imageNo?

The images have a unique Id, but the order of the Id's is not the order by Date. So given a (unique) Id number, how do I efficiently find out the index of this image if ordered by Date?

thanks
,pieter

If you want to get a specific image, use the primary key of the table.

If you want to get a number of images ordered by date, select and order by date.

If you want the fourth image, or 5 images starting from the fourth, use limit like CodeKadiya said.

Don't mix these things up. It sounds like you want to work with one image, then get the next one. In this case it is better to select all the images you will use in one query, then simply use them.

Don't use to many queries, or too fancy queries, use the ones that work the best/easiest.

Especially with database programming, try to make the code as easy to understand as possible, because often you want to change something, and it is easier if the code is straightforward.

As a more specific answer, (select date from table where index = '12'), then select where date is after that date, and use limit.

You might need to RTFM to see how to compare dates. You must look in the manual to see what you need to do.

You can also place an index on the date column, that way you can select images by the date easily.

What do you mean by 'find out'? You want to display it or what?? You can change the value of $imageNo dynamically and get the picture in that index. Just echo $imageNo to see what image it is....

Yes... he can just use this to get if he want to get 7th image from the table...
$sql="SELECT * FROM images ORDER BY Date LIMIT 7,7";

And he can change the 7 dynamically by assinging into a variable and changing it's value.