[PDO binding parameters] use PHP's PDO extension for bulk update operations, PDO binding _php Tutorial

[PDO binding parameters] use PHP's PDO extension for bulk update operations, PDO binding

Recently there is a requirement to batch update a few fields in a database table, and in doing so, PDO is used to do parameter binding, in which a pit is encountered.

Solution Selection

The author has known to do batch update has the following several scenarios:

1. Update

This is the simplest solution, but it is undoubtedly the least efficient option.

2. Case

A statement similar to the following

UPDATE SET =  Case  when 1  Then 2  when 2  Then 3 END WHERE inch (12);

PDO binding Parameters

To prevent SQL injection, the PDO extension binding parameter is used. The above numbers are usually variables, so we need to do parameter binding. At first I was thinking of binding the ID string as a variable in the past, the first implementation of the code is as follows:

1 
 Php2         $data=Array(Array(' id ' = 1, ' val ' = + 2),Array(' id ' = 2, ' val ' + = 3));3         $ids=implode(',',Array_map(function($v) {return $v[' ID '];},$data));//Get ID Array4         $update _sql= ' UPDATE tbl_test SET val = case ID ';5         $params=Array();6         $params[": IDs"] =$ids;7         foreach($data  as $key=$item) {8                 $update _sql. = "When:id_".$key. "Then:val_".$key. " ";9                 $params[": Id_".$key] =$item[' ID '];Ten                 $params[": Val_".$key] =$item[' Val ']; One         } A         $update _sql. = "END WHERE ID in (: _ids)"; -         TEST::Execute$update _sql,$params);//The Bindparam binding parameter is called here

Later found that this is not feasible, and the more bizarre is that this can only update the first record. After reviewing the data, it is found that such a binding method is not possible, the in statement parameters should be bound one by one. Look at the description of the Bindparam function in the document:

You can see that the description is bound to a PHP variable into the placeholder, so if the binding: IDs is 1, 2 of the string, then the MySQL parsing statement will be 1, 2 parse into a single variable, and not as a string. This is also the principle of the PDO anti-SQL injection, through the binding of the placeholder, only the value of the binding as a value, not the statement and other things, so that MySQL will only pass the value of the past as a variable value.

The modified wording:

1 
 Php2          $data=Array(Array(' id ' = 1, ' val ' = + 2),Array(' id ' = 2, ' val ' + = 3));3          $update _sql= ' UPDATE tbl_test SET val = case ID ';4          $params=Array();5          $params[": IDs"] =$ids;6          $in _arr=Array();7 8          foreach($data  as $key=$item) {9                  $update _sql. = "When:id_".$key. "Then:val_".$key. " ";Ten                  $params[": Id_".$key] =$item[' ID ']; One                  $params[": Val_".$key] =$item[' Val ']; A                  $params[": Ids_".$key] =$item[' ID ']; -                  Array_push($in _arr, ": Id_".$key); -          } the          $update _sql. = "END WHERE ID in (".implode(','$in _arr) . ")"; -Test::execute ($update _sql,$params);//The bindparam binding parameter is called here

Summarize

This is a recently encountered a small problem, in fact, more is to explain in the MySQL in statement to do parameter binding should be a one-bound.

Reference Links:

MySQL statement: Batch update of different values for multiple records

Can I bind an array to a in () condition?

Original article, writing Limited, Caishuxueqian, if there is not in the text, million hope to inform.

If this article is helpful to you, please click on the recommendation, write the article is not easy.

http://www.bkjia.com/PHPjc/1122769.html www.bkjia.com true http://www.bkjia.com/PHPjc/1122769.html techarticle [PDO binding parameters] using the PHP PDO extension for bulk update operations, PDO binding recently has a batch update database table in a few fields of the requirements, when doing this requirement, use ...

