Mysql用数组设置值

Question

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!

Answer 1

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