COUNT Causing Problems

Databases
1

Hi all

What I have below works good and returns all the reviews depending on the venue_id selected.

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.

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 :cool:

2

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?

3

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.

:slight_smile:

4

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()

5

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 :cool:

6

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

7

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 :cool:

<?php echo "$num_rows"; ?>