finding row numbers in a mysql query

[ironikart]

Is there a built-in function that returns the row number of a result in a query?

I have a query that takes information from the database, uses ORDER BY to preferentially sort the information and then uses LIMIT to only take a slice of the information based on page numbers. The information I want to get is where the set of results places in the over-all information contained in the database without having to write a php script that sorts the information and places the "ranking" number in a seperate field.

Is this possible??

[sfullman]

you're on to something, you have a WHERE clause that limits the data, then a ORDER clause to order it, and then a LIMIT clause after that to take a slice.

However, there's no way to get a return that says "here's your records and Oh by the way this is page n of x".

best way to do this is,
1) strip the SELECT fields. If your query is not too complicated (doesn't use calculated fields and no "AS" aliases), you can do this:

$sql=preg_replace('/^{SELECT\s+[a-z0-9]+(,\s[a-z0-9]+)\s+/i','SELECT} COUNT(*) ',$sql);

2) strip the limit clause as:

$sql=preg_replace('/\s+LIMIT+[0-9]+(\s,\s[0-9]+\s)$/i','',$sql);

//then run your query
$result=mysql_query($sql) or die();
$ct=mysql_fetch_array($result);
$count = $ct[0];

I refer to the limit parameters as the index and batch, respectively. Once you have the index, the batch, and the COUNT you'd have without the LIMIT clause, you have every parameter you need to figure out what the next and previous batch is, first and last batch, etc.

Hope this helps, I've been through this one already.

Sam Fullman