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 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.
