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!
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')
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.
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.
