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 — Marc B
– Marc B, Commented Mar 13, 2014 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. — Alejandro Urbano Alvarez
– Alejandro Urbano Alvarez, Commented Mar 13, 2014 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
– Mike Brant, Commented Mar 13, 2014 at 16:47

eroteev · Accepted Answer · 2014-03-13 16:59:13Z

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, 2014 at 16:59

answered Mar 13, 2014 at 16:42

eroteev

6101 gold badge7 silver badges17 bronze badges

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

2 Comments

Eligos Over a year ago

Assuming, of course, that the value you want to set them all to is the same.

Andrea_86 Over a year ago

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 .= ",?"

Collectives™ on Stack Overflow

MySQL update multiple rows via PHP

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related