Mysqli Query to Loop Through Array and Update Multiple Rows

MySQLi query to loop through array and update multiple rows

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

Mysqli prepared statement: store rows in an array and loop through this array later

This is how you need to do it

 ids=array();
 while($row = $stmt->fetch()){
    ids[]= $row['userID'];
 }

ids is an array and will have all the ids that you need.
But I would say "Dont do it like that", just use a table join to get the username for example may be your case would be like

SELECT   replies.admissionID,users.name,replies.userID,replies.description,
replies.link,replies.postingdate,replies.compensation FROM replies, users 
WHERE 
users.name=replies.userID
and replies.projectID=?

Iterating through column values for multiple rows mysqli

Lose the foreach and use $row, not $result

while ( $row = $result->fetch_assoc() ){
    $tablebuild .= "<tr><td>";
    $tablebuild .= $row['product_id'];
    $tablebuild .= "</td><td>";
    $tablebuild .= $row['cost'];
    $tablebuild .= "</td><td>";
    $tablebuild .= $row['image'];
    $tablebuild .= "</td></tr>";
}

MySQL update multiple rows via PHP

If you are sure, that the array is containing integers, why don't you do it like this:

$array=array(1,2,3);
if (sizeof($array) > 0 {
  $sql = "UPDATE mytable SET val_to_update = XX WHERE id IN(".implode(',', $array).")";
  mysql_query($sql);
}

If you want to use prepared statement you could create your sql using this code:

$array=array(1,2,3);
    if (sizeof($array) > 0 {
       $placeholders = array();
       for($i=0; $i<sizeof($array); $i++) {
         $placeholders[] = '?';
       }
      $sql = "UPDATE mytable SET val_to_update = XX WHERE id IN(".implode(',', $placeholders).")";
  // .....
}

If the values in the $array exists in another table you could use something like this:

$sql = "UPDATE mytable SET val_to_update = XX WHERE id IN (SELECT id FROM another_table WHERE condition = 1)";

How can I update multiple rows in MySQL using PHP and the mysqli_query() command by passing dynamic variables?

This is the perfect opportunity to make use of a prepared statement. These are optimised to be run multiple times with different values. For example...

$stmt = $con->prepare('UPDATE INVENTORY SET PRODUCT_STOCK = (PRODUCT_STOCK - ?) WHERE ID = ?');
if (!$stmt) {
    throw new Exception($con->error, $con->errno);
}
$stmt->bind_param('ii', $product_quantity, $product_id);

foreach ($id_str_array as $value) {
    list($product_id, $product_quantity) = explode('-', $value);

    if (!$stmt->execute()) {
        throw new Exception($stmt->error, $stmt->errno);
    }
}

UPDATE RECORDS USING PHP LOOP

You're generating the SQL string in a loop:

for ($id = 1; $id <=$student_count_count; $id++)
{  
    $sql = ...;
}

But you're only executing it once, because this is outside the loop:

if (mysqli_query($mysqli, $sql)) {

Move the query command inside the loop:

for ($id = 1; $id <=$student_count_count; $id++)
{  
    $sql = ...
    if (mysqli_query($mysqli, $sql)) {
        ...
    } else {
        ...
    }
}

You're also missing braces on your while loop:

while($row2 = mysqli_fetch_row($rcount_student_lists))
$student_count_count = $row2['0'];

Without braces, the while only loops the one line following it. To loop over more than one line, you need to wrap the lines in braces:

while($row2 = mysqli_fetch_row($rcount_student_lists))
{
    $student_count_count = $row2['0'];
    for ($id = 1; $id <=$student_count_count; $id++)
    {
        ...
    }
}

Also, please read about SQL injection. Instead of building queries with string concatenation, use prepared statements with bound parameters. See this page and this post for some good examples.

Updating multiple rows in MySQL without a loop

You can run one UPDATE query per group provided that the group can share the same WHERE criteria and the same update values.

UPDATE tbl SET value = TRUE WHERE id IN(1,2,3,4,5,6);
UPDATE tbl SET value = FALSE WHERE id IN(7,8,9,10,11);

Or you can use the WHEN clause or even some IF clauses provided that the criteria are simple enough.

UPDATE tbl SET value = IF(id = 1) WHERE id IN(1,2);
UPDATE tbl
    SET value = CASE
                    WHEN id IN (1,2,3,4,5,6) THEN TRUE
                    WHEN id IN (7,8,9,10,11) THEN FALSE
                    ELSE value
                END
    WHERE id IN (1,2,3,4,5,6,7,8,9,10,11);

Having possibly thousands of WHEN cases, may be a hassle to build/change, I'd go with the first option:

Flip the old key=>value array and keep all ids connected to a value:

foreach($list AS $id => $value) {
    $list2[$value][] = $id;
}

Iterate through the value=>keys array and build UPDATE queries that can bulk update the value for all keys at once.

---

Related Topics