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());


    }

}

Still amazes me, how this "method" to call SQL in PHP still survives after years and years with much more better solutions. — Henrik P. Hessel, Oct 04 '10 at 16:10
@Henrik P. Hessel what are the better solutions that you are referring to? — Marcus, Oct 04 '10 at 16:36
PDO or take a look at this question http://stackoverflow.com/questions/108699/good-php-orm-library — Henrik P. Hessel, Oct 04 '10 at 16:38
if you need a quick hack to try something out then it is the perfect way to go. PDO is an overhead if you just play around. — ITroubs, Oct 04 '10 at 16:59
No, I disagree, mixing php and sql has great potential for potential bugs. — Henrik P. Hessel, Oct 04 '10 at 17:06
`ORM` is very often overkill, having a good database abstraction so you can easily filter, switch and trace queries & code is very handy, and `PDO` can fulfill that need, but is not the only solution out there. That being said, `mysql` is ancient, slow & not advisable. At the very _least_ use mysqli, but PDO _is_ probably the future for PHP apps. — Wrikken, Oct 04 '10 at 17:48

score 0 · Accepted Answer · answered Oct 04 '10 at 17:49

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.

Nesting MySQL queries to UPDATE or INSERT with PHP

1 Answers1