mysql - select average col value then update table

Wheres the flaw, simple solution.

[dkin]

Hey everyone, Im trying to use 2 tables at the same time, one houses comments, the other the main info for my product, now, on the comments table it has ratings, so what Im trying to do is when someone submits a rating I want to get the average for all ratings, then update my other table with the info, this is what I've written, but it doesn't seem to be working.

mysql_connect("localhost", "user", "pass") or die(mysql_error());
mysql_select_db("db") or die(mysql_error());
$presult = mysql_query("SELECT AVG(rating) FROM comments where gid = '$gid'") or die ("query 1: " . mysql_error());
$prow = mysql_fetch_array($presult) or die ("query 1: " . mysql_error());
$result = mysql_query("UPDATE games SET rating = '$prow[rating]' WHERE id = $gid") or die(mysql_error());

Like I said its prolly a simple solution, but its 3 am here lol.

Thanks all

Dylan

[Habtom]

Try the following:

$presult = mysql_query("SELECT AVG(rating) as avg_rating FROM comments where gid = '$gid'") or die ("query 1: " . mysql_error());
$prow = mysql_fetch_array($presult) or die ("query 1: " . mysql_error());
$result = mysql_query("UPDATE games SET rating = '". $prow[avg_rating] ."' WHERE id = '". $gid ."'") or die(mysql_error());

I assume $gid is passed by well.

Habtom

[edited by: Habtom at 7:19 am (utc) on June 3, 2007]

[dkin]

excellent, this makes everything so much easier, thank you.

Dylan