[PDO binding parameters] use the PDO extension of PHP to perform batch update operations. pdo binding _ PHP Tutorial

[PDO binding parameters] use the PDO extension of PHP to perform batch update operations. pdo binding. [PDO binding parameters] the PDO extension of PHP is used for batch update operations. pdo binding recently requires that several fields in the database table be updated in batches. to meet this requirement, use [PDO binding parameters] use PHP's PDO extension for batch update operations, pdo binding

Recently, we have a requirement to update several fields in a database table in batches. when we do this, we use PDO for parameter binding, where we encounter a pitfall.

Solution Selection

I have known several solutions for batch update:

1. update one by one

This is the simplest solution, but it is undoubtedly the most efficient solution.

2. CASE WHEN

Statements similar to the following

UPDATE tbl_test SET val = CASE id WHEN 1 THEN 2 WHEN 2 THEN 3 END WHERE id IN(1, 2);

PDO binding parameters

To prevent SQL injection, the PDO extension is used to bind parameters. If the preceding number is a variable, you need to bind the parameter. At the beginning, I thought about binding the string consisting of id as a variable at the time of IN. the first implementation code is as follows:

1
 1, 'Val' => 2), array ('id' => 2, 'Val' => 3); 3 $ ids = implode (',', array_map (function ($ v) {return $ v ['id'] ;}, $ data )); // obtain the ID array 4 $ 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']; 10 $ params [": val _". $ key] = $ item ['Val']; 11} 12 $ update_ SQL. = "end where id IN (: _ ids)"; 13 TEST: execute ($ update_ SQL, $ params); // bindParam binding parameters are called here

Later, it was found that this was not feasible, and it was strange that only the first record could be updated. After checking the information, we found that this binding method is not feasible. the parameters of the IN statement should be bound one by one. Take a look at the description of the bindParam function in this document:

As you can see, it indicates that a PHP variable is bound to the placeholder. Therefore, if you bind a string with ids 1 and 2, mySQL parses 1 and 2 as a single variable instead of a string. This is also the anti-SQL injection principle of PDO. through the placeholder binding, only the bound value is treated as a value, rather than other things such as statements, in this way, MySQL only treats the passed value as the value of a variable.

Modified statement:

1
 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. ""; 10 $ params [": id _". $ key] = $ item ['id']; 11 $ params [": val _". $ key] = $ item ['Val']; 12 $ params [": ids _". $ key] = $ item ['id']; 13 array_push ($ in_arr, ": id _". $ key); 14} 15 $ update_ SQL. = "end where id IN (". implode (',' $ in_arr ). ")"; 16 TEST: execute ($ update_ SQL, $ params); // bindParam binding parameters are called here

Summary

This is a small problem recently encountered. IN fact, it is more explained that parameter binding should be performed one by one IN the MySQL IN statement.

Reference link:

Mysql statement: batch update different values of multiple records

Can I bind an array to an IN () condition?

The original article is limited in writing, so it is easy to learn. if there is anything wrong with the article, please let us know.

If this article is helpful to you, click here for recommendations. it is not easy to write articles.

Pipeline recently has a requirement to update several fields in a database table in batches. when this requirement is met, use...