MySQL WHERE clause not working

Hey guys, thanks to all who helped me previously (enjoy your added rep.)

I have this PHP code:

$get_rating = mysql_query("SELECT rsite, AVG(rvalue) FROM rating GROUP BY rsite");	
	while($ratings = mysql_fetch_array($get_rating)) {
	print $ratings['rsite'];
	print ': ';
	print $ratings['AVG(rvalue)'];
	print '<br />';

}

PHP:

It works perfectly fine, and echoes something like:

However, now I only want the script to echo the average of 'rsite' 3, and I add in the WHERE clause:

$get_rating = mysql_query("SELECT rsite, AVG(rvalue) FROM rating GROUP BY rsite WHERE rsite=3");	
	while($ratings = mysql_fetch_array($get_rating)){
	print $ratings['rsite'];
	print ': ';
	print $ratings['AVG(rvalue)'];
	print '<br />';

}

PHP:

But the result is nothing. Just a blank space

I also tried:

$get_rating = mysql_query("SELECT rsite, AVG(rvalue) FROM rating WHERE rsite=3");	
	while($ratings = mysql_fetch_array($get_rating)){
	print $ratings['rsite'];
	print ': ';
	print $ratings['AVG(rvalue)'];
	print '<br />';

}

PHP:

...but nothing

Thanks for any help at all!

 

try this...

$get_rating = mysql_query("SELECT rsite, AVG(rvalue) FROM rating WHERE rsite=3 GROUP BY rsite");    

Code (markup):

 

Your condition needs to be checked first (before grouping). So just put the WHERE clause in front of the GROUP BY statement instead of the other way around like it is now.
(also see http://www.w3schools.com/sql/sql_groupby.asp )

 

Tried this, but it still doesn't work

 

Try adding single quotes around your value - and since you are only retrieving one record there's no reason to be declaring GROUP BY

$get_rating = mysql_query("SELECT rsite,AVG(rvalue) FROM rating WHERE rsite='3'");

Wait, you are using Fetcharray on a single item request. Well there's your problem. Hang on, I'll be at my workstation in a few minutes.

 

Use mysql_fetch_row instead of mysql_fetch_array

$get_rating = mysql_query("SELECT rsite, AVG(rvalue) FROM rating WHERE rsite='3'");
$ratings = mysql_fetch_row($get_rating);

    print $ratings[0];
    print ': ';
    print $ratings[1];
    print '<br />';

PHP:

 

Actually, I was just thinking it was the 'while' that could be causing the issue - I'd still use fetch_array, but with an IF statement so that if it does have an error, you can trap it thus:

$query="SELECT rsite,AVG(rvalue)
	FROM rating
	WHERE rsite='3'
";
$results=mysql_query($query);  
if ($ratings=mysql_fetch_array($results)) {
	echo $ratings['rsite'],': ',$ratings['AVG(rvalue)'],'<br />';
} else {
	echo 'Something went wrong in retrieving ratings.<br />';
}

Code (markup):

Yes, I separated out the query just to make it easier to read. ... and since we're not returning values from printing, it's probably better to use echo.

Try running that, if the error message comes up something's probably wrong with the query.