Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Location
    Toronto
    Posts
    33

    Question Unanswered: Can I count AND get this RS in a single statement?

    I am running a SELECT to perform a user search:
    - has 4 LEFT JOINS
    - WHERE query depends on user search criteria
    - LIMIT 50

    The data itself has categories and sub categories. While displaying the results in the middle of the screen, I want to show for these results how many are in each category, and allow user to click on category to drill down. Ebay is a good example of this:

    A search on books returns 100+ results, LIMIT 50. I also need the following breakdown from the results:
    Books Search
    - Fiction (45)
    - History (76)
    - Non-Fiction (25)

    The question:

    Can I do this category breakdown count within the statement as above? Or do I need to have two statements, one SELECT for the results, and a second SELECT to return the category count?

    This query will be performed a LOT so it has to be efficient.

    Thanks so much!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you are returning the record set to a scripting language like php, you could simply loop over the results for your counts

    or, since you want to show the count as part of a link, then you are on a different page that does not use the detailed result set, so you would use a simpler count query

    finally, look up the FOUND_ROWS function, you will need it too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Location
    Toronto
    Posts
    33
    Hi r937, thx for the reply but:
    - Looping in script is not possible because I use a limit on results.
    - This is all happening on one page. See ebay as an example.
    - Found_Rows is used to count total rows (which I already do), it can't be used to count the categories.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if the result set consists of more than one row, then yes, you can loop in php

    "see ebay as an example" doesn't quite do it for me

    FOUND_ROWS gives you how many rows the query would have returned if you didn't use LIMIT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2004
    Location
    Toronto
    Posts
    33
    Let me clarify. I'm displaying this list to the user:
    Book 1
    Book 2
    Book 4
    ...
    Book 50

    (limit=50, total from FOUND_ROWS=146)

    Also looking to display this breakdown to user:
    - Fiction (45)
    - History (76)
    - Non-Fiction (25)

    Can I get both the book list AND the breakdown from the same SELECT statement?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by zkenet
    Can I get both the book list AND the breakdown from the same SELECT statement?
    only if you count them while you're printing them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2004
    Location
    Toronto
    Posts
    33
    Well, can't count while printing since I use a limit, and count must be on all.

    Regardless, I already made this work nicely using two queries. The breakdown query is very fast so I believe I'm good for now...

    Thx for help.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    look up the FOUND_ROWS function in the mysql docs
    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
  •