Welcome to WebmasterWorld Guest from 54.224.103.186

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

mysql - select average col value then update table

Wheres the flaw, simple solution.

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

Preferred Member

10+ Year Member

joined:Mar 4, 2004
posts:553
votes: 0


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

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

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Nov 15, 2004
posts:1867
votes: 0


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]

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

Preferred Member

10+ Year Member

joined:Mar 4, 2004
posts:553
votes: 0


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

Dylan

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members