apidata 2017-09-24 18:07:23 10034次浏览 0条评论 1 0 0
/**
 *  user: fang
 *  date: 2017-09-20
 *  封装一个方法 用于批量修改
 *  参数说明: 表名,要修改的字段数组,数据字典,条件或主键数组,条件或主键字段
 *  params: table_name,columns,rows,conditionArrs,condition_columns
 *  ```php
 *
$connection->createCommand()->batchUpdate('user', ['name', 'age'], [
['Tom', 30],
['Jane', 20],
['Linda', 25],
],[ 1, 2, 3],'id')->execute();
 *
 *
  • UPDATE table_name SET
    columns = CASE id
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    END,
    title = CASE id
        WHEN 1 THEN 'New Title 1'
        WHEN 2 THEN 'New Title 2'
        WHEN 3 THEN 'New Title 3'
    END
    

    WHERE id IN (1,2,3)
    */
    public function batchUpdate($table, $columns, $rows, $keyPrimaryArrs,$keyPrimaryColumn)
    {

    $sql  = '';
    $sql .= 'UPDATE '.$table.' SET ';
    
$rowsCount = count($rows);
$columnsCount = count($columns);
$columnName ='';
$rowFang = '';

for ( $i = 0; $i < $rowsCount; $i++ ){

    $columnName = isset($columns[$i])?$columns[$i]:$columnName;
    $sql .= $columnName.' = CASE '.$keyPrimaryColumn;

    for ( $j = 0; $j < $rowsCount; $j++ ){
        $rowFang = isset($rows[$j][$i])?$rows[$j][$i]:$rowFang;
        $keyPrimary = isset($keyPrimaryArrs[$j])?$keyPrimaryArrs[$j]:$keyPrimary;
        if (gettype($rowFang)=='integer'){
            $sql .= ' WHEN \''.$keyPrimary.'\' THEN '.$rowFang;
        }else{
            $sql .= ' WHEN \''.$keyPrimary.'\' THEN \''.$rowFang.'\'';
        }
    }

    if ($i === $rowsCount){
        $end = ' END ';
    }else{
        $end = ' END, ';
    }
    $sql .= $end;
}

$conditions = '(\''.implode('\',\'',$keyPrimaryArrs).'\')';

$sql .= ' WHERE '.$keyPrimaryColumn.' IN '.$conditions;
$sql  = str_replace('END,  WHERE','END  WHERE',$sql);

return $this->setSql($sql);

}

> 说明: 将该方法放在 yii\db\Command 类里面,调用的时候
 `\Yii::$app->db->createCommand()->batchUpdate('',[],[],[],'')`
    没有找到数据。
您需要登录后才可以评论。登录 | 立即注册