CODEWITHSUNDEEP
phpmysqli
MultiDev
MultiDev

Reputation: 10649

MySQLi query to loop through array and update multiple rows

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?

Upvotes: 3

Views: 4166

Answers (3)

Rick James
Rick James

Reputation: 142296

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.

Upvotes: -2

Maytham Fahmi
Maytham Fahmi

Reputation: 33407

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

Upvotes: 11

Your Common Sense
Your Common Sense

Reputation: 157893

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?

Upvotes: 29

Related Questions