Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2006
    Posts
    3

    Unanswered: I need COUNT query for fairly complex query

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you're using LIMIT, have a look at the FOUND_ROWS function
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2006
    Posts
    3
    Quote Originally Posted by r937
    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?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please have another look at the FOUND_ROWS function

    there's a nice example in the manual
    SQL_CALC_FOUND_ROWS and FOUND_ROWS() can be useful in situations when you want to restrict the number of rows that a query returns, but also determine the number of rows in the full result set without running the query again. An example is a Web script that presents a paged display containing links to the pages that show other sections of a search result. Using FOUND_ROWS() allows you to determine how many other pages are needed for the rest of the result.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2006
    Posts
    3
    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?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, there is -- just run this query:
    Code:
    select version() as v
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •