CODEWITHSUNDEEP
phpmysql
squareCircle
squareCircle

Reputation: 302

Mysql set value with array

Is there possibility to update MySql table if ids in array like $ids=['key1'=>'value1','key2'=>'value']. $ids keys are ids in MySql table!

I can loop through $ids and do something like 

foreach($ids as $key=>$value)
  do_query(UPDATE table SET column=$value WHERE $id=$key);

But want something to do all updates in one query,not to do count($ids) queries!

Upvotes: 2

Views: 1793

Answers (3)

Danilo Bustos
Danilo Bustos

Reputation: 1093

try this:

$idsIn='';
$ids=['key1'=>'value1','key2'=>'value'];
$case='';
foreach($ids as $key=>$value){
  $idsIn.=($idsIn=='')?"'$key'":','."'$key'";
  $case.=" WHEN '$key' THEN '$value'";
}

$sql = "UPDATE table set column= (CASE id $case END) WHERE id in($idsIn)";

return:

UPDATE table set column= (CASE id  WHEN 'key1' THEN 'value1' WHEN 'key2' THEN 'value' END) WHERE id in('key1','key2')

Upvotes: 1

Gerardo Jaramillo
Gerardo Jaramillo

Reputation: 485

You could try to generate a coma separated string with the ids you want to update, from the array you already have, like "1,2,3,4,5" with this you can do a batch update in mysql like this:

update table
set column = 'new value'
where id in (1, 2, 3, 4, 5);

Just generate this query as a string and concatenate the ids and execute it, in php.

Upvotes: 0

Pedro Chiiip
Pedro Chiiip

Reputation: 188

You could loop through a for to generate a giant string containing your statement, and in every loop, you would add another condition for the WHERE clause. Something like this:

$length = count($ids);
$statement = "UPDATE table set column=$value";
for ($i = 0; $i<$length; $i++){
if ($i == 0){ //checking if it is the first loop
$statement.= " WHERE $id = $ids[$i]";
}
else{
$statement.= " OR $ id = $ids[$i]";
}
}

Then you would execute your query based on $statement. I believe it's not the best thing to do, but i believe it would solve your problem.

Upvotes: 0

Related Questions