有合适的方法吗。我想计算一个表的平均评分,同时更新另一个表中的结果。我是PHP和MYSQL的新手,如果有任何帮助,我将不胜感激
$query=mysql_query("INSERT INTO review (username, restaurant, rating, review) VALUES ('$username','$restaurant','$rating','$review')");
if($query)
{
$avg_query="SELECT ROUND(AVG(rating),0) FROM review WHERE name ='"$restaurant'"";
$avg_result=mysql_query($avg_query);
$avg_row=mysql_fetch_array($avg_result);
$rating=$row['ROUND(AVG(rating),0)'];
if($avg_result)
{
$update_query= "UPDATE restaurant SET rating='"$rating'" WHERE name ='"$restaurant'"";
$update_result=mysql_query($update_query);
}
}
else
{
}
谢谢!
UPDATE restaurant
SET rating= (SELECT ROUND(AVG(rating),0) FROM review WHERE name ='$restaurant')
WHERE name ='$restaurant'
我会将两者合并为一个,如下所示:
$query=mysql_query("INSERT INTO review (username, restaurant, rating, review) VALUES ('$username','$restaurant','$rating','$review')");
if($query)
{
$avg_query="UPDATE restaurant a SET rating=(SELECT ROUND(AVG(rating),0) FROM review WHERE name =a.name) WHERE name ='".$restaurant."'";
$avg_result=mysql_query($avg_query);
}
else
{
}
话虽如此,当mysql_*
函数贬值时,您应该转到PDO或mysqli。
另一个选项是使用mysql trigger
。例如(不要让我拘泥于语法):
CREATE TRIGGER after_insert_review
AFTER INSERT ON review
FOR EACH ROW
BEGIN
UPDATE restaurant
SET rating = (SELECT ROUND(AVG(rating),0) FROM review WHERE name = NEW.restaurant)
WHERE name = NEW.restaurant;
END
正如其他人所提到的,再次使用PDO或MySQLi。