Thinkphp method Rollup for batch update data

The following small series to give you a list of three ways to achieve thinkphp batch update data, write bad also please forgive me, there are suggestions to welcome, Common learning progress!

Method One:

Bulk modify data Two-dimensional array field key field reference CI Batch modification function batch_update ($table _name= ", $data =array (), $field =") {if (! $table _ name| |! $data | |! $field) {return false;} else{$sql = ' UPDATE '. $table _name;} $con =array (), $con _sql=array (), $fields =array (), foreach ($data as $key + $value) {$x =0;foreach ($value as $k + = $v) { if ($k! = $field &&! $con [$x]&& $x ==0) {$con [$x]= "set {$k} = (case {$field}";} ElseIf ($k! = $field &&! $con [$x]&& $x >0) {$con [$x]= ' {$k} = (case {$field} ";} if ($k! = $field) {$temp = $value [$field]; $con _sql[$x].= "when" {$temp} ' then ' {$v} ' "; $x + +;}} $temp = $value [$field];if (!in_array ($temp, $fields)) {$fields []= $temp;}} $num =count ($con) -1;foreach ($con as $key = $value) {foreach ($con _sql as $k + $v) {if ($k = = $key && $key < $n UM) {$sql. = $value. $v. ' end), ';} ElseIf ($k = = $key && $key = = $num) {$sql. = $value. $v. ' End) ';}} $str =implode (', ', $fields); $sql. = "WHERE {$field} in ({$str})", $res =m ($table _name)->execute ($sql); return $res;} Testing function Test () {$update_array=array (); for ($i =2; $i <7; $i + +) {$data =array (); $data [' id ']= $i; $data [' Memeber_type ']=2; $data [' Memeber_type _state ']=1; $update _array[]= $data;} $res = $this->batch_update (' Yl_member ', $update _array,id); Var_dump ($res);}

Method Two:

That is, an update record that loops through one article. One record update once, so performance is poor, it is also easy to cause blocking.

So can a SQL statement implement batch update? MySQL does not provide a straightforward way to implement batch updates, but it can be accomplished with a little bit of skill.

UPDATE categories SET Display_order = case ID when 1 then 3 if 2 then 4 when 3 then 5 Endwhere ID in (All-in)

This SQL means, update the Display_order field, if id=1 the value of Display_order is 3, if id=2 Display_order value is 4, if id=3 then Display_order value is 5.
That is, the conditional statements are written together.

The where section here does not affect the execution of the code, but it improves the efficiency of SQL execution. Make sure that the SQL statement executes only the number of rows that need to be modified, where only 3 of the data is updated, while the WHERE clause ensures that only 3 rows of data are executed.

If you update multiple values, you only need to modify them slightly:

UPDATE categories SET Display_order = case ID when 1 then 3 if 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 (All-in-a-

Practical use

$display _order = Array (1 = 4, 2 = 1, 3 = 2, 4 = 3, 5 = 9, 6 = 5, 7 = 8, 8 = 9); $ids = Implode (', ', Array_keys ($display _order)); $sql = "UPDATE categories SET display_order = case ID"; foreach ($display _order as $id = + $ordinal) {$sql. = sprintf ("When%d then%d", $id, $ordinal);} $sql. = "END WHERE ID in ($ids)"; Echo $sql;

Method Two is not recommended for use.

Method Three:

/** @param $saveWhere: Want to update the primary key ID array * @param $saveData: The data corresponding to the ID array you want to update * @param $tableName: What you want to update indicates * @param $saveWhere: Return more New successful primary Key ID array * */public function SaveAll ($saveWhere,& $saveData, $tableName) {if ($saveWhere ==null| | $tableName ==null) Return false;//gets the updated primary key ID Name $key = Array_keys ($saveWhere) [0];//Gets the length of the update list $len = count ($saveWhere [$key ]); $flag =true; $model = Isset ($model)? $model: M ($tableName);//Open transaction mechanism $model->starttrans ();//record update failure id$error=[]; for ($i =0; $i < $len; $i + +) {//Preprocess SQL statement $isright= $model->where ($key. ' = '. $saveWhere [$key] [$i])->save ($saveData [$i]), if ($isRight ==0) {//will update the failed record down $error[]= $i; $flag =false;} $flag = $flag && $isRight;} if ($flag) {//If all is established, submit $model->commit (); return $saveWhere;} ElseIf (Count ($error) >0&count ($error) < $len) {//The original preprocessing was rolled back $model->rollback (); for ($i =0; $i <count ( $error), $i + +) {//delete update failed ID and Dataunset ($saveWhere [$key] [$error [$i]]); unset ($saveData [$error [$i]]);} Reorder array subscripts $savewhere[$key]=array_merge ($saveWhere [$key]) $saveData =array_merge ($sAvedata);//Perform a second recursive update $this->saveall ($saveWhere, $saveData, $tableName); return $saveWhere;} else{//rollback $model->rollback (); return false;}}

Called in the test method:

Public Function test () {//$where[' ID ']=array (70,73,74,80,83) of the primary key array of the data table to be updated,//id the data to be updated for the primary key array $save=array (' School ' + ' dk Univisity01 ', ' Isexport ' =>0, Array (' School ' = ' dk Univisity02 ', ' Isexport ' =>0), Array (' School ' + ' dk Univisity03 ', ' Isexport ' =>0, Array (' School ' = ' dk Univisity04 ', ' Isexport ' =>0), Array (' School ' = = ', ' Isexport ' =>0),//Array (' School ' = ' Univisity05 ', ' Isexport ' =>0), $f = $this->saveall ($ Where, $save, ' want '), if (count ($f [' id ']) >0) {//returns the ID array of the successful update echo "This is success:</br>";d UMP ($f); Echo ' OK ';} else{//update failed operation Echo "This is failed:</br>";d UMP ($f); Echo ' Error ';}}

The above is a small series to introduce you to the thinkphp batch update data method Summary, I hope we have some help, if you have any questions please give me a message, small series will promptly reply to you. Thank you very much for your support for topic.alibabacloud.com!