Multiple queries MySQL PHP

Question

Is there is proper way to do this. I want to calculate the average rating for a table and update the result in another table simultaneously. Im new to PHP and MYSQL and I would appreciate any help

$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
{
}

Thanks!

Use parametrised queries! Never insert a variable directly into a query string! http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php — Levi Botelho, Aug 04 '12 at 06:38

score 0 · Answer 1 · answered Aug 04 '12 at 06:29

0

UPDATE restaurant 
SET rating= (SELECT ROUND(AVG(rating),0) FROM review WHERE name ='$restaurant')
WHERE name ='$restaurant'

answered Aug 04 '12 at 06:29

juergen d

201,996
37
293
362

score 0 · Answer 2 · answered Aug 04 '12 at 06:31

I would combine the two into one like this:

$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
{
}

Having said that, you should move over to either PDO or mysqli as the mysql_* functions are depreciated.

score 0 · Answer 3 · answered Aug 04 '12 at 06:47

Another option is to use a mysql trigger. For example (don't hold me to the syntax):

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

Again as mentioned by others use PDO or MySQLi.

Multiple queries MySQL PHP

3 Answers3