MySQLi query to loop through array and update multiple rows

Question

I have an array like:

$postdata[1] = 'This';
$postdata[2] = 'That';
$postdata[3] = 'The other';

And I want to loop through the array and update all of the rows where ID corresponds to the array key. Like:

foreach ($postdata as $key => $value) {
  if ($key == 1) {
    $update = $db->query("UPDATE site_email_templates SET Content='$postdata[1]' WHERE ID = 1");
  } else if ($key == 2) {
    $update = $db->query("UPDATE site_email_templates SET Content='$postdata[2]' WHERE ID = 2");
  } else if ($key == 3) {
    $update = $db->query("UPDATE site_email_templates SET Content='$postdata[3]' WHERE ID = 3");
  }
}

What would be the simplest way to do this, not particularly knowing how many array keys there are, and keeping it all in one query?

Your Common Sense · Answer 1 · 2021-04-12T09:07:21.897

29

You need to use prepared statements in order to avoid errors and vulnerabilities of all sorts and also to get some minor performance gain

$stmt = $db->prepare("UPDATE site_email_templates SET Content=? WHERE ID = ?");
$stmt->bind_param("ss", $content, $id);
foreach ($postdata as $id => $content)
{
    $stmt->execute();
}

Reference: How can I prevent SQL injection in PHP?

edited Apr 12 '21 at 09:07

answered Feb 29 '16 at 12:56

Your Common Sense

156,878
40
214
345

score 11 · Accepted Answer · edited Apr 14 '21 at 10:45

Note: My answer is based on the PDO driver which in many aspects is better than mysqli. If you need mysqli solution please check the other answer provided by @Your Common Sense

The code below is tested on real environment and served with prepared statement preventing SQL-injection:

$sql = "UPDATE `site_email_templates` SET `Content` = (:content) WHERE `Id` = (:id)";
$stmt = $dbConn->prepare($sql);
foreach ($postdata as $id => $content)
{
    $stmt->execute([':id' => $id, ':content' => $content]);
}

For more details about SQL injection you can read more:
https://www.owasp.org/index.php/SQL_Injection

For extra speed, wrap that code in `START TRANSACTION;` and `COMMIT;` — Rick James, Feb 26 '22 at 23:10

Rick James · Answer 3 · 2022-02-26T23:08:20.040

-2

For maximal speed, IODKU can do all the updates in a single statement. Caution: You should not use this for updating if you don't know that the ids exist.

INSERT INTO t
    (id,           -- A PRIMARY or UNIQUE key
     col1, col2)   -- column(s) to change
    VALUES
    (111, 22, 33),
    (222, 33, 44),
    ...
    ON DUPLICATE KEY UPDATE
        col1 = VALUES(col1),
        col2 = VALUES(col2);

You must provide some way to "bind" or "escape" the values to avoid sql-injection.

edited Feb 26 '22 at 23:08

answered Feb 26 '22 at 18:42

Rick James

135,179
13
127
222

WARNING for all readers: DO NOT user this approach to update rows. This is essentially an INSERT query which means it will insert NEW rows if a primary key value not found. In a concurrent environment, such as a web-server, it will inevitably make your data INCONSISTENT, adding rows that were deleted by another process. – Your Common Sense Feb 26 '22 at 20:54
@YourCommonSense - True. I added such a caveat. Doing the update task with a series of Updates would lead to errors if the ids were missing. – Rick James Feb 26 '22 at 23:09

MySQLi query to loop through array and update multiple rows

3 Answers3

Related