Nesting MySQL queries to UPDATE or INSERT with PHP

Question

Using MySQL and PHP I'm making an index of the "amounts" table and grouping it by product, type, month, day, and hour. Then either updating a matching record in the "amounts_merged" table or inserting a new record if it does not already exist.

Here is the solution I came up with. But I'm not sure this is the best way to go about this. Any suggestions would be great.

$sql = "SELECT product, type, month, day, hour, AVG(amount) AS average_amount FROM amounts GROUP BY product, type, month, day, hour";
$result1 = @mysql_query($sql, $con) or die(mysql_error());

while($row = mysql_fetch_array($result1)) {

    $average_amount = $row[average_amount];
    $product = $row[product];
    $type = $row[type];
    $month = $row[month];
    $day = $row[day];
    $hour = $row[hour];

    $sql = "UPDATE amounts_merged SET average_amount = '$average_amount' WHERE product = '$product' AND type = '$type' AND month = '$month' AND day = '$day' AND hour = '$hour'";
        $result2 = @mysql_query($sql, $con) or die(mysql_error());

        $updated_rows = mysql_affected_rows();

        if ($updated_rows == 0) {

            $sql = "INSERT INTO amounts_merged (product, type, month, day, hour, average_amount)
                        VALUES ('$product', '$type', '$month', '$day', '$hour', '$average_amount')";
            $result3 = @mysql_query($sql, $con) or die(mysql_error());


    }

}

Answer 1

Add a unique key and let MySQL handle it:

ALTER TABLE amounts_merged  ADD UNIQUE (product,type,month,day,hour)

In PHP:

 $sql = "INSERT INTO amounts_merged 
    (product,type,month,day,hour,average_amount)
    SELECT product, type, month, day, hour, AVG(amount) AS average_amount
    FROM amounts
    GROUP BY product, type, month, day, hour
    ON DUPLICATE KEY UPDATE average_amount = VALUES(average_amount);"
 mysql_query($sql, $con);

Some loose remarks:

• Don't quote variables that are integers or floats (don't know if they are, but I have my suspicions...
• Prepared statements are even more handy for both running more update queries then 1, and in preventing SQL injection.
• Having seperate columns for month/date/hour in your original amounts table is probably making live harder then need be, as with just having a timestamp/datetime you can get the data easily from those fiels, and you can still use all date functions easily on the original field.