laravel 批量更新

gcl_guyunlige

已于 2023-05-12 11:08:45 修改

阅读量692

点赞数

文章标签： laravel java 数据库

于 2023-05-11 09:44:25 首次发布

本文链接：https://blog.csdn.net/gaokcl/article/details/130615000

版权

//-------------------------------------------------------------------
    // @批量修改  $data--二维数组   $field--update的where条件字段：一般是id
    // 单条 gender=0 ： !empty($fields) 改为 isset($fields)
    //-------------------------------------------------------------------
    public static function batchUpdate($table, $data, $field, $params = array())
    {
        if (!is_array($data) || !$field || !is_array($params)) {
            return false;
        }
        $updates = self::parseUpdate($data, $field);
        $where = self::parseParams($params);
        $fields = array_column($data, $field);

        if (isset($fields)) {
            foreach ($fields as &$value) {
                if (!empty($value)) {
                    $value = '"' . addslashes($value) . '"';
                }
            }
            $fields = !empty($fields) ? implode(',', $fields) : '';
        }
        $sql = '';
        if (isset($fields)) {
            $sql = sprintf("UPDATE %s SET %s WHERE `%s` IN (%s) %s", $table, $updates, $field, $fields, $where);
        }
        return $sql;
    }

    private static function parseUpdate($data, $field)
    {
        $sql = '';
        $keys = array_keys(current($data));
        foreach ($keys as $column) {

            $sql .= sprintf("`%s` = CASE `%s` \n", $column, $field);
            foreach ($data as $line) {
                $sql .= sprintf("WHEN \"%s\" THEN \"%s\" \n", trim($line[$field]), trim($line[$column]));
            }
            $sql .= "END,";
        }
        return rtrim($sql, ',');
    }

    private static function parseParams($params)
    {
        $where = array();
        foreach ($params as $key => $value) {
            $where[] = sprintf("`%s` = '%s'", trim($key), trim($value));
        }
        return $where ? ' AND ' . implode(' AND ', $where) : '';
    }