MySQL implements a SQL complete update of multiple data

Original function:

• Update Categories SET display_order= 3,title = ' New Title 1 ' where id=1;

• Update Categories SET display_order= 4,title = ' New Title 2 ' where id=2;

• Update Categories SET display_order= 5,title = ' New title 3 ' where id=3;

Functional improvements:

UPDATE categories SET    display_order = 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‘    ENDWHERE id IN (1,2,3)

PHP automatically generates SQL

$data = [[' id ' = = 1, ' parent_id ' = +, ' title ' = ' A ', ' sort ' and ' = 1], [' id ' = 2, ' parent_id ' =    , ' title ' + ' A ', ' sort ' and ' 3 ', [' id ' = + 3, ' parent_id ' = ' + ', ' title ' = ' A ', ' sort ' 5],  [' id ' = 4, ' parent_id ' = ' + ', ' title ' = ' B ', ' sort ' = ' + ' 7], [' id ' = 5 ', ' parent_id ' = + 101, ' title ' = ' A ', ' sort ' + 9]; For example, we would like to have parent_id 100, title a record based on different ID batch update:/** * Bulk Update function * @param $data array of data to be updated, two-dimensional array format * @param array $params The same condition as the array value, the one-dimensional array that corresponds to the key value * @param a condition with a string $field string value, the default is ID * @return bool|string */function ba   Tchupdate ($data, $field, $params = []) {if (!is_array ($data) | | |! $field | |!is_array ($params)) {return false;    } $updates = ParseUpdate ($data, $field);    $where = Parseparams ($params); Gets all the values of the key named $field column, with single quotation marks on both sides of the value, saved in the $fields array//Array_column () function needs php5.5.0+, if less than this version, you can implement,//reference address: http://php.net/m anual/zh/function.array-column.php#118831 $fields = Array_column ($data, $field);    $fields = Implode (', ', Array_map (function ($value) {return "'". $value. "";    }, $fields));   $sql = sprintf ("UPDATE '%s ' SET%s ' where '%s ' in (%s)%s", ' Post ', $updates, $field, $fields, $where); return $sql;} /** * Convert a two-dimensional array into case-then-batch update condition * @param $data array two-dimensional array * @param $field string Column name * @return String SQL statement */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", $line [$field], $line [$column]);    } $sql. = "END,"; } return RTrim ($sql, ', ');}   /** * Parse WHERE Condition * @param $params * @return array|string */function parseparams ($params) {$where = [];   foreach ($params as $key + $value) {$where [] = sprintf ("'%s ' = '%s '", $key, $value); } return $where? ' and '. Implode (' and ', $where): ';}

Call:

echo batchUpdate($data, ‘id‘, [‘parent_id‘ => 100, ‘title‘ => ‘A‘]);

Generate SQL

WHEN ‘1‘ THEN ‘1‘ WHEN ‘2‘ THEN ‘2‘ WHEN ‘3‘ THEN ‘3‘ WHEN ‘4‘ THEN ‘4‘ WHEN ‘5‘ THEN ‘5‘ END,`parent_id` = CASE `id` WHEN ‘1‘ THEN ‘100‘ WHEN ‘2‘ THEN ‘100‘ WHEN ‘3‘ THEN ‘100‘ WHEN ‘4‘ THEN ‘100‘ WHEN ‘5‘ THEN ‘101‘ END,`title` = CASE `id` WHEN ‘1‘ THEN ‘A‘ WHEN ‘2‘ THEN ‘A‘ WHEN ‘3‘ THEN ‘A‘ WHEN ‘4‘ THEN ‘B‘ WHEN ‘5‘ THEN ‘A‘ END,`sort` = CASE `id` WHEN ‘1‘ THEN ‘1‘ WHEN ‘2‘ THEN ‘3‘ WHEN ‘3‘ THEN ‘5‘ WHEN ‘4‘ THEN ‘7‘ WHEN ‘5‘ THEN ‘9‘ END WHERE `id` IN (‘1‘,‘2‘,‘3‘,‘4‘,‘5‘)  AND `parent_id` = ‘100‘ AND `title` = ‘A‘

Reference article:

PHP Batch Update

MySQL implements a SQL complete update of multiple data