returning a one value average

[Lau]

Hi everyone,

I'm using PHP and MySQL, and am trying to return an average (songle value).
My code isn't working, and I'm getting confused between the mysql_fetch, here's my code:

$average = "SELECT AVG(overall) as overall, AVG(gameplay) as gameplay,
AVG(graphics) as graphics, AVG(sound) as sound FROM VIDEO_GAME_DETAIL
WHERE name=" .$row['name'];
$result = mysql_query($average);
$rating = mysql_fetch_array($result);

// down in an HTML table
echo "<td>" .$rating['overall']. "</td>";


$row['name'] is the name of a PC Game from the VIDEO_GAME table. In the VIDEO_GAME_DETAIL table I have reviews for many games, with ratings. My goal here is to average the different ratings (overall, gameplay, sounds and graphics) for a one game, and stick them with the main page that lists the games (1 row per game).

I get this following error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /Users/Marconi/Grad/lleccia/www/ISYG250/proj4/reviews.php on line 54

line 54 is $rating = mysql_fetch_array($result);

Any help will be greatly appreciated!!!!

[raf]

The error usually means that no rows were returned.

You should always check the number of returned rows like

$resultavg = mysql_query($average) or die ('Queryproblem computing average');
if (mysql_num_rows($resultavg) == '0'){
echo 'No rows returned';
}elseif (mysql_num_rows($resultavg) == 1){
$rating = mysql_fetch_assoc($resultavg);
...
}else{
echo 'Expected 1 row.' . mysql_num_rows($resultavg) . ' rows returned';
}

(best always use $resultxxx where xxx is something descriptive about the query, so that you have unique names for all resultsets)
But i think your query is also not correct (appart from probably not returning records). It should contain a 'GROUP BY name 'clause

[Lau]

Thanks for the response Raf...

I actually found out that the double quote/single quote combination was the
problem...
So here's what I did: I first stored the name value from my previous query in a variable, and used that to create the new query:

$name = $row['name'];
$average = "SELECT AVG(overall) as overall, AVG(gameplay) as gameplay
, AVG(graphics) as graphics, AVG(sound) as sound
FROM VIDEO_GAME_DETAIL WHERE name = '$name'";
$result = mysql_query($average);
if (!$result)
echo "Query did not run $result";
else
$rating = mysql_fetch_array($result);