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 · Accepted Answer · 2021-04-12 09:07:21Z

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, 2021 at 9:07

answered Feb 29, 2016 at 12:56

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dharman · Accepted Answer · 2021-04-14 10:45:11Z

11

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

edited Apr 14, 2021 at 10:45

Dharman♦

33.9k27 gold badges106 silver badges157 bronze badges

answered Aug 5, 2015 at 11:07

Maytham Fahmi

33.2k17 gold badges132 silver badges159 bronze badges

1 Comment

Rick James Over a year ago

For extra speed, wrap that code in START TRANSACTION; and COMMIT;

Rick James · Accepted Answer · 2022-02-26 23:08:20Z

-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, 2022 at 23:08

answered Feb 26, 2022 at 18:42

Rick James

144k15 gold badges144 silver badges255 bronze badges

2 Comments

Your Common Sense Over a year ago

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.

Rick James Over a year ago

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

Collectives™ on Stack Overflow

MySQLi query to loop through array and update multiple rows

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related