Merge multiple foreach MySQL insert statements into one with PHP array data

Question

I have a PHP array with different items and their respective values, and would need to update each row for each item in a database. Is there a way to only have one query to execute, instead of multiple foreach queries?

foreach($array as $key=>$item){
    $image_id = $item['id'];
    $order_id = $item['order_id'];
    $sql = "UPDATE images SET order_id='$order_id' WHERE id='$image_id'";
    $query = mysqli_query($connection, $sql);
} // end for each

You should parameterize your query instead of injecting values directly. But you would have to execute it multiple times none the less, it'll be better with a prepared statement though. — Qirel, Feb 28 '19 at 09:13
This answer [might be useful to you](https://stackoverflow.com/a/3466/2310830) — RiggsFolly, Feb 28 '19 at 09:17
What's the absolute maximum number of images that will ever get updated at any time? — SpacePhoenix, Feb 28 '19 at 09:42

score 3 · Accepted Answer · answered Feb 28 '19 at 09:23

You can't really combine it into one query using UPDATE, as the value and condition come in pairs (the value and condition is different for each row you are updating).

There is a hacky fix with INSERT INTO..ON DUPLICATE KEY UPDATE, but I would refrain from using that - see the question linked by RiggsFolly in the comments.

When using a prepared statement, you can execute the same query multiple times (you would have to query it multiple times anyways, even with mysqli_query(), but this is optimized and more secure when using a prepared statement).

$sql = "UPDATE images SET order_id=? WHERE id=?";
$stmt = $connection->prepare($sql);
foreach($array as $key=>$item){
    $stmt->bind_param("ss", $item['order_id'], $item['id']);
    $stmt->execute();
}
$stmt->close();

If your IDs are integers instead of strings, replace the respective s with an i.

Merge multiple foreach MySQL insert statements into one with PHP array data

1 Answers1