rownum for album images

[Kim Gustafson]

I'm trying to figure out a way to get the row number of a query result set. I'm well aware of what LIMIT can do.

I'm querying an image db for all the images that belong to an album. I need to know what row the current image is in an album so that I can tell the user they are on image 43 of 63(total) for example.

A rownum field added to the select query would tell me this, but I don't see a way of doing this in mysql.

My album images query looks somthing like:

SELECT images.* FROM albums,images,matches WHERE matches.albumid=albums.id AND matches.imageid=images.id AND albums.id=101 ORDER BY date ASC


I could do something like this to add a rownum field to the beginning of each row:

SET @row:=0; SELECT @row:=@row+1 as row, images.* FROM albums,images,matches WHERE matches.albumid=albums.id AND matches.imageid=images.id AND albums.id=101 ORDER BY date ASC

Any danger there? Seems the variable value is not local to each query.


Ideally I would like to do a query that just return the current image row that the user is viewing, along with the context of what image they are viewing in an album...without querying for all the album images, but adding " AND imageid.7229" to the above query always just result in a rownum of 1, of course.


Just trying to be the most efficient here.

Any thoughts?