[RESOLVED] Get full count, then query with offset, limit

[pipe_girl]

Hello,

I am optimizing all of my scripts and condensing multiple queries into fewer, where possible. For pages running a script where the total count is returned and page number generated (for example a keyword search), I am querying the database twice - one to count the number of results, and another to display the data with an offset and limit (i.e. 20 results per page). I dislike that I am running two queries here. Any suggestions as to how I can do this all in one shot?

Thanks.

[NogDog]

This seems to work OK:

SELECT `col1`, `col2`, (SELECT COUNT(*) FROM `table_name`) AS `count`
FROM `table_name` ORDER BY `col1` LIMIT 0, 20

You can grab the "count" value from any row, then mysql_data_seek() back to row 0 if necessary before looping through the results.

[pipe_girl]

That works great to get the count of all rows in the table, but not rows based on certain criteria.

Edit: Disregard. I added criteria to the count select statement and it worked just groovy. Thanks!