[PDO binding parameters] Use PHP's PDO extension to perform batch update operations, pdo binding

[PDO binding parameters] Use PHP's PDO extension to perform batch update operations, pdo binding_PHP tutorial

WBOY

Release： 2016-07-12 08:53:52

Original

949 people have browsed it

[PDO binding parameters] Use PHP's PDO extension to perform batch update operations. pdo binding

Recently there is a need to batch update certain fields in the database table. When meeting this requirement, I used PDO for parameter binding, but encountered a pitfall.

Plan selection

The author knows the following solutions for batch updates:

1. Update item by item

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

2.CASE WHEN

Sentences similar to the following

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

Copy after login

PDO binding parameters

To prevent SQL injection, PDO extended binding parameters are used. The above numbers are generally variables, so parameter binding is required. At first, I thought about binding the string composed of id as a variable during IN. The code for the first implementation is as follows:

<span> 1</span> <?<span>php
</span><span> 2</span>         <span>$data</span> = <span>array</span>(<span>array</span>('id' => 1, 'val' => 2), <span>array</span>('id' => 2, 'val' => 3<span>));
</span><span> 3</span>         <span>$ids</span> = <span>implode</span>(',', <span>array_map</span>(<span>function</span>(<span>$v</span>) {<span>return</span> <span>$v</span>['id'];}, <span>$data</span>)); <span>//</span><span>获取ID数组</span>
<span> 4</span>         <span>$update_sql</span> = 'UPDATE tbl_test SET val = CASE id'<span>;
</span><span> 5</span>         <span>$params</span> = <span>array</span><span>();
</span><span> 6</span>         <span>$params</span>[":ids"] = <span>$ids</span><span>;
</span><span> 7</span>         <span>foreach</span>(<span>$data</span> <span>as</span> <span>$key</span> => <span>$item</span><span>) {
</span><span> 8</span>                 <span>$update_sql</span> .= "WHEN :id_" . <span>$key</span> . "THEN :val_" . <span>$key</span> . " "<span>;
</span><span> 9</span>                 <span>$params</span>[":id_" . <span>$key</span>] = <span>$item</span>['id'<span>];
</span><span>10</span>                 <span>$params</span>[":val_" . <span>$key</span>] = <span>$item</span>['val'<span>];
</span><span>11</span> <span>        }
</span><span>12</span>         <span>$update_sql</span> .= "END WHERE id IN (:_ids)"<span>;
</span><span>13</span>         <span>TEST::</span>execute(<span>$update_sql</span>, <span>$params</span>);//此处会调用bindParam绑定参数

Copy after login

Later I found out that this doesn’t work, and what’s weird is that only the first record can be updated in this way. After consulting the information, I 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 the documentation:

As you can see, the description says that a PHP variable will be bound to the placeholder, so if a string with :ids of 1, 2 is bound, then MySQL will use 1, 2 when parsing the statement. Parsed as a single variable, not as a string. This is also the principle of PDO to prevent SQL injection. Through the binding of placeholders, only the bound value is regarded as a value, not other things such as statements. In this way, MySQL will only treat the passed value as a variable. value.

Modified writing:

<span> 1</span> <?<span>php
</span><span> 2</span>          <span>$data</span> = <span>array</span>(<span>array</span>('id' => 1, 'val' => 2), <span>array</span>('id' => 2, 'val' => 3<span>));
</span><span> 3</span>          <span>$update_sql</span> = 'UPDATE tbl_test SET val = CASE id'<span>;
</span><span> 4</span>          <span>$params</span> = <span>array</span><span>();
</span><span> 5</span>          <span>$params</span>[":ids"] = <span>$ids</span><span>;
</span><span> 6</span>          <span>$in_arr</span> = <span>array</span><span>();
</span><span> 7</span> 
<span> 8</span>          <span>foreach</span>(<span>$data</span> <span>as</span> <span>$key</span> => <span>$item</span><span>) {
</span><span> 9</span>                  <span>$update_sql</span> .= "WHEN :id_" . <span>$key</span> . "THEN :val_" . <span>$key</span> . " "<span>;
</span><span>10</span>                  <span>$params</span>[":id_" . <span>$key</span>] = <span>$item</span>['id'<span>];
</span><span>11</span>                  <span>$params</span>[":val_" . <span>$key</span>] = <span>$item</span>['val'<span>];
</span><span>12</span>                  <span>$params</span>[":ids_" . <span>$key</span>] = <span>$item</span>['id'<span>];
</span><span>13</span>                  <span>array_push</span>(<span>$in_arr</span>, ":id_" . <span>$key</span><span>);
</span><span>14</span> <span>         }
</span><span>15</span>          <span>$update_sql</span> .= "END WHERE id IN (" . <span>implode</span>(',' <span>$in_arr</span>) . ")"<span>;
</span><span>16</span>          TEST::execute(<span>$update_sql</span>, <span>$params</span>);<span>//</span><span>此处会调用bindParam绑定参数</span>

Copy after login

Summary

This is a small problem I encountered recently. In fact, it is more about illustrating that when binding parameters in the IN statement of MySQL, you should bind them one by one.

Reference link:

mysql statement: batch update different values of multiple records

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

Original article with limited writing style and limited knowledge. If there is anything wrong in the article, please let me know.

If this article is helpful to you, please click Recommend. Writing articles is not easy.