Welcome to WebmasterWorld Guest from 54.147.10.72

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

mysql - select average col value then update table

Wheres the flaw, simple solution.

     

dkin

7:14 am on Jun 3, 2007 (gmt 0)

10+ Year Member



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

7:18 am on Jun 3, 2007 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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

7:23 am on Jun 3, 2007 (gmt 0)

10+ Year Member



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

Dylan

 

Featured Threads

Hot Threads This Week

Hot Threads This Month