[PDO binding parameters] Use PHP's PDO extension to perform batch update operations php pdo sqlite php pdo odbc php open pd

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

Scheme selection

The author knows the following solutions for batch updates:

1. Update one by one

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

2. CASE WHEN

　 Statements 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>);

PDO binding parameters

In order 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绑定参数

Later I found that this does not work, and the weird thing is that this can only Update the first record. 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 document:

You can see that the description says that a PHP variable will be bound to the placeholder, so if a string with ids of 1 and 2 is bound , then when MySQL parses the statement, it will parse 1 and 2 into single variables instead of treating them 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>

Summary

This is a small problem I encountered recently. In fact, it is more about illustrating that parameters should be bound one by one in the IN statement of MySQL.

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 shallow knowledge. If there is anything wrong in the article, I am sorry. Hope to be informed.

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

The above introduces [PDO binding parameters] using PHP's PDO extension to perform batch update operations, including php and pdo content. I hope it will be helpful to friends who are interested in PHP tutorials.