MySQL query that counts and also checks for match?

I have this queries that counts all rows taht match Site = $site:

$count= mysql_query("SELECT COUNT(DISTINCT `Site`) FROM `Ratings` WHERE `Site` = '$site'");

The query above gets the number of ratings for a $site. But I also need to know if the user already rated the site. I'm using use another COUNT for this:

   SELECT COUNT(*) FROM `Ratings` WHERE `Site` = '$site' A开发者_开发知识库ND `UID` = '$uid'"

Is it possible to get the number of site matches and check if the user rated the site in one query? Thanks

Yes, it isn't very straightforward but you can do this in one query. Try this:

SELECT COUNT(*) as 'num_ratings', IF(`UID` = '$uid','YES','NO') as 'already_rated' FROM `Ratings` WHERE `Site` = '$site' ORDER BY already_rated DESC

This query will return you 1 row with 2 columns. The first column num_ratings will be the number of ratings for that particular site, and the already_rated will tell you if that particular user has rated the site. (It will be either 'YES' or 'NO')

The ORDER BY already_rated DESC insures that if the user has rated the site, it will return YES, because YES comes after NO in the alphabet. This is necessary because when you use COUNT() the rows are being grouped together.

EDIT: After further testing, the solution above WILL NOT WORK. Use this one instead.

SELECT COUNT(*) as 'num_ratings' IF(GROUP_CONCAT(IF(`UID` = '$uid', 'YES','NO') SEPARATOR ',') LIKE '%yes%', 'YES','NO') as 'already_rated' FROM `Ratings` WHERE `Site` = '$site'

I have only superficial knowledge of MySQL and I have seen that it does certain things differently than other DBMSs (sometimes better), but I still think that you are not counting ratings per site with your query, but rather the number of 'distinct' sites in your Ratings table, which is always 1 if constrained with a Site parameter. You need a GROUP BY in order to count ratings by site, as in:

SELECT
  count(*)
FROM `Ratings`
WHERE `Site` = '$site' 
GROUP BY `Site`

You have different options for including a check if the user rated a site. One is a correlated subquery, which gives you the information for each site in the result set.

SELECT
  count(*),
  (SELECT count(*) from `Ratings` where `Site` = r.`Site` and `UID` = '$uid') 
         as has_rated
FROM `Ratings` r
WHERE `Site` = '$site' 
GROUP BY `Site`

Another would be a self-join - something that usually has a performance advantage over a subquery (the subquery has to be evaluated for each row in the result set and the join does not). But since you are constraining your query by site and always expecting only one row in the result set, it would make very little difference.