I have a moderately complex query and I want to page the results, limiting to 50 on a page. I know that in order to do so, I need to know the total number of records so that I know whether there are more records remaining so I can show a 'NEXT PAGE' link.

I'm having trouble constructing a COUNT query from my original query because the query accesses 4 tables, two of which are many-to-one association tables. Here is the original query, without a LIMIT clause:

SELECT e.id, e.title, e.subheading, eta.start_timestamp, eta.end_timestamp, e.zip, e.bold, e.outline, e.color, e.subheading, COUNT(esa.id) AS subcat_count
FROM demo_event_time_assoc eta,
demo_events e,
demo_event_subcategory_assoc esa,
demo_zip_codes z
WHERE eta.event_id=e.id
AND esa.event_id=e.id
AND z.zip=e.zip
AND e.active=1
AND esa.subcategory_id IN (1,2,3,4,5,6,7,8,9,10)
AND (
(eta.start_timestamp >= 1162281600 AND eta.start_timestamp <= 1162368000)
OR
(eta.end_timestamp < 1162281600 AND eta.end_timestamp > 1162285200))
AND (3963.20477315*(2*asin(sqrt(POWER(sin((0.595334589 56 -z.lat_radians)/2),2) + cos(0.59533458956)*cos(z.lat_radians)*POWER(sin((-2.06592416764 - z.long_radians)/2),2)))) < 50) GROUP BY eta.id ORDER BY subcat_count DESC, eta.id



I tried replacing the SELECT clause with just COUNT(*) and dropping the order by clause but it returns a list of rows rather than a single row with just the count:

SELECT COUNT(*)
FROM demo_event_time_assoc eta,
demo_events e,
demo_event_subcategory_assoc esa,
demo_zip_codes z
WHERE eta.event_id=e.id
AND esa.event_id=e.id
AND z.zip=e.zip
AND e.active=1
AND esa.subcategory_id IN (1,2,3,4,5,6,7,8,9,10)
AND (
(eta.start_timestamp >= 1162281600 AND eta.start_timestamp <= 1162368000)
OR
(eta.end_timestamp < 1162281600 AND eta.end_timestamp > 1162285200))
AND (3963.20477315*(2*asin(sqrt(POWER(sin((0.595334589 56 -z.lat_radians)/2),2) + cos(0.59533458956)*cos(z.lat_radians)*POWER(sin((-2.06592416764 - z.long_radians)/2),2)))) < 50)

GROUP BY eta.id

if you're using LIMIT, have a look at the FOUND_ROWS function

In order to page the results, i need two queries.
1) A query to find the TOTAL number of rows that match the search criteria. This is what I want the COUNT() query for. I need it in order to display something like 'Results 51-100 of 1,456 total'.

2) A query to fetch the rows that match the current search criteria that are in the current page range.

I am familiar with the php function mysql_num_rows() which can return the number of rows that exist in a result, but this would require that instead of querying for COUNT() that I run a big query to fetch ALL rows, run this function in the query result, and then dispense with the query result so I can run a separate query to only fetch the rows that interest me. I'm thinking that this might be a little inefficient.

Is there some way to use FOUND_ROWS() to just find the number of rows that would match my main query?

please have another look at the FOUND_ROWS function

there's a nice example in the manual

whoa! That is rad! thanks so much. I just turned over a new leaf i think.

For what it's worth, this is only available for mysql version 4+. is there any way to tell what version of mysql i'm running?

yes, there is -- just run this query: