PHP PDO INSERT on duplicate UPDATE

A MySQL INSERT on duplicate key UPDATE is an all-round excellent query because if a key already exists it will update set values instead of inserting or returning an error.

This query works on tables that have Unique indexes and primary keys.

An example of this being you are inserting products and their identifier is pid (product id). The product specs may change through time and you’re unsure if it’s even in your database.

Using the insert on duplicate key update query means that if that product (pid) is already in your database then the specs will be updated.

<?php
$query = $db->prepare('INSERT INTO products (pid, weight, price, h_w) VALUES(:var, :var2, :var3, :var4)
    ON DUPLICATE KEY UPDATE weight = :var2, price = :var3, h_w = :var4');
$query->bindParam(':var', $avar, PDO::PARAM_INT);
$query->bindParam(':var2', $avar2, PDO::PARAM_STR);
$query->bindParam(':var3', $avar3, PDO::PARAM_STR);
$query->bindParam(':var4', $avar4, PDO::PARAM_STR);
$query->execute();

The above prepared statement updates the: weight, price and h_w if the product already exists in the database.

In certain situations, this query can prevent you from needing to do a row count to determine if an INSERT is needed or an UPDATE instead.

However, the documentation does state to avoid using this query on tables that have multiple unique indexes.