SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    COUNT Causing Problems.

    Hi all

    What I have below works good and returns all the reviews depending on the venue_id selected.
    Code SQL:
    SELECT comments,
    	DATE_FORMAT(review_date, '%M %D %Y') AS subdir,
    	publisher, rating,  FROM tbl_reviews WHERE venue_id='"...

    Example return
    comments: some comments about the venue
    date: 12th dec 12
    rating: 4

    comments: some comments about the venue
    date: 13th dec 12
    rating: 2

    and so on...

    But now I need to COUNT the number of reviews for that particular venue_id.

    Code SQL:
    SELECT comments,
    	DATE_FORMAT(review_date, '%M %D %Y') AS subdir,
    	publisher, rating, COUNT(venue_id) AS VenueReviewTotal FROM tbl_reviews WHERE venue_id='"...

    Example return
    comments: some comments about the venue
    date: 12th dec 12
    rating: 4

    and stops, only shows one review even if there are 15

    The review count is correct
    VenueReviewTotal 15

    What is happening, why does this happen when I introduce the COUNT function?

    Thanks,
    Barry
    Last edited by computerbarry; Dec 5, 2012 at 11:51. Reason: spelling
    The more you learn.... the more you learn there is more to learn.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by computerbarry View Post
    What is happening, why does this happen when I introduce the COUNT function?
    the technical answer: you forgot the GROUP BY clause

    the let's-rethink-this answer: if you're displaying all the reviews for a given venue, why do you need to count them at the same time? can't you do that with your application language as you're printing the reviews?

    alternatively, do you just want the count and not the individual reviews?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937

    do you just want the count and not the individual reviews?
    I need both, basically, I just want to add a total so users can see how many reviews we have for that venue.

    I'm using PHP for the display, didn't realize I could do it this way.
    Does it make any difference, speed, best practices?

    While I was waiting I created a separate SELECT statement which seems to work, but, a lot more code, seems a bit overkill.

    The more you learn.... the more you learn there is more to learn.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by computerbarry View Post
    I'm using PHP for the display, didn't realize I could do it this way.
    Does it make any difference, speed, best practices?
    if you're gonna print the detailed results of a query anyway, it's a lot faster to do it with the application language

    i personally don't do php, but i know there's a function for it, i think it's called mysql_num_rows()
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cheers.

    Will look into the GROUP BY and see if I can get PHP to display the result.
    Thanks for the heads up.

    Nice to see your still going strong r937
    The more you learn.... the more you learn there is more to learn.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by computerbarry View Post
    Will look into the GROUP BY and see if I can get PHP to display the result.
    you can look up GROUP BY but don't use it here

    just keep the "works good" query at the top of your first post
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard
    Join Date
    Dec 2005
    Posts
    1,738
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, just added the simple PHP echo, ha works a treat r937.
    Its good when you know how, just saved me another call to the server and reduced my SQL

    Code PHP:
    <?php echo "$num_rows"; ?>
    The more you learn.... the more you learn there is more to learn.


Bookmarks

Posting Permissions

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