dousi2029 2017-02-07 17:26
浏览 30
已采纳

Mysql用数组设置值

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!

  • 写回答

3条回答 默认 最新

  • douhui3760 2017-02-07 18:09
    关注

    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')
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?