MySQL update multiple rows via PHP

Question

I have to update the same value of multiple rows in a table and i would like to avoid multiple mysql_query using a foreach loop that scan every value of an array. I try to explain using an example. This is the solution of the problem using a foreach loop

$array=array(1,2,3);
foreach($array as $val){
$sql = "UPDATE mytable SET val_to_update = XX WHERE id = $val";
mysql_query($sql);
}

I didn't like to start hammering database with a crazy number of queries, because the number of element of the array is not fixed, and can also be large. I have considered using the IN clause of SQL language but without knowing the number of parameters can not seem to find a solution. Thinked at something like this, but I do not know if it is achievable:

$sql= "UPDATE Illustration SET polyptychID = $id_polyptych WHERE illustrationID IN (?,?,?);

and then bind all the parameters using a foreach loop for scan the array of parameters. The problem, as I said, is that i don't know the number, so i can't place the right number of ? in sql query and, if I'm not mistaken, the number of occurrences of ? parameters must be the same as the binded parameters. Anyone have solved a problem like this?

possible duplicate of [php PDO insert batch multiple rows with placeholders](http://stackoverflow.com/questions/15069962/php-pdo-insert-batch-multiple-rows-with-placeholders) — Marc B, Mar 13 '14 at 16:40
As Marc said, use a for loop to generate the placeholders in a String, then add it to the query, and finally use another loop to bind the data. — aurbano, Mar 13 '14 at 16:42
If you want to use prepared statements with bound values, you will need to use mysqli, PDO , or some other DB connection library that supports prepared statements. You should be doing this anyway, as mysql_* functions are deprecated. — Mike Brant, Mar 13 '14 at 16:47

eroteev · Accepted Answer · 2014-03-13T16:59:13.623

1

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)";

edited Mar 13 '14 at 16:59

answered Mar 13 '14 at 16:42

eroteev

620
1
7
17

Assuming, of course, that the value you want to set them all to is the same. – Eligos Mar 13 '14 at 16:45
Of course is the same. That's what i want, thanks! I forgotten the implode() function, that the same as edit the IN of the sql string in a for loop and placing the right number of placeholders, something like $sql .= ",?" – Andrea_86 Mar 13 '14 at 17:06

MySQL update multiple rows via PHP

1 Answers1