PHP implementation Stitching SQL statement batch update multiple fields

This article mainly and everyone to share PHP implementation splicing SQL statement batch update multiple fields, I hope to help everyone.

One, SQL statements

1. batch Update a field:

UPDATE ' user '
SET ' name ' = case ID
When 1 Then ' Zhang San '
When 2 Then ' John Doe '
When 3 Then ' Harry '
END
WHERE ID in (All-in-a-

This SQL means to update the name field of the user table, if id=1 then the value of name is Zhang San, if id=2 then the value of name is John Doe

2. Batch update multiple fields:

UPDATE ' user '
SET
' Name ' = case ID
When 1 Then ' Zhang San '
When 2 Then ' John Doe '
When 3 Then ' Harry '
END,
Age = Case ID
When 1 then 16
When 2 then 17
When 3 then 18
END
WHERE ID in (All-in-a-

Second, the case
/

/@todo Remove the phone and fax in the table '-'//The minimum ID of the table is 1, the maximum ID is 121329;fax and the phone is a varchar type//100 data per query, splicing SQL statements Bulk update public Function updeaction    () {set_time_limit (0);    $mGather = new Clientgathermodel ();    $mGather->setdb (' gather ');    Read online gather library [distinguish between online and offline databases] $cnt = 0;    $minId = 1;    $maxId = 121329;        Inserts up to 100 data at a time, the program splicing for ($x = $minId; $x <= $maxId; $x +=100) {$littleMax = $x +99;        $QUERYSQL = "SELECT * from Crm_client_gather where id>= $x and ID <= $littleMax the ORDER by ID ASC";        $result = $mGather->query ($QUERYSQL);        $data = [];        $sql = "";                if ($result) {foreach ($result as $k = = $v) {$fax = $v [' Fax ']? $v [' Fax ']: '; $phone = $v [' Phone ']?                $v [' phone ']: ';                                    $id = $v [' id ']; if ($fax | | $phone) {if ($fax && strstr ($fax, '-')) {$fax = substr ($fax, S    Trpos ($fax, '-') +1);    Intercept the first-subsequent content}                if ($phone && strstr ($phone, '-')) {$phone = substr ($phone, Strpos ($phone, '-')    ) +1); Intercept the first-following content}} $data [] = [' id ' = ' + $id, ' fax ' =&gt            ; $fax, ' phone ' = ' $phone];                } if ($data) {$sql. = "UPDATE crm_client_gather SET fax = case ID";                $FAXARR = Array_column ($data, ' fax ', ' id ');                foreach ($faxArr as $k = + $v) {$sql. = "When {$k} then ' {$v} '";                                    } $sql. = "END,";                $PHONEARR = Array_column ($data, ' phone ', ' id ');                $sql. = "Phone = case ID";                foreach ($phoneArr as $k = + $v) {$sql. = "When {$k} then ' {$v} '";                                    } $sql. = ' END ';                $IDARR = Array_column ($data, ' id ');        $sql. = "WHERE ID in (";        foreach ($idArr as $k = + $v) {if ($idArr [count ($IDARR)-1] = = $v) {$sq                    L. = $v;                    }else{$sql. = $v. ', ';                                    }} $sql. = ")";                                    $mGather->query ($sql);                if ($littleMax > $maxId) {$biggerId = $maxId;                }else{$biggerId = $littleMax; The echo "id:[{$x}]-[$biggerId] updated [. Count ($data)."] Data ";//Print Progress}}}}

Related recommendations:

MySQL statement pre-compilation in PHP understanding

Mysql SQL statement Annotation Daquan instance sharing

A detailed description of multiple SQL statements