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

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

WBOY

Release： 2016-07-29 08:52:30

Original

1708 people have browsed it

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

Copy after login

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

Copy after login

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:

php pdo中文手册,php pdo教程,php pdo mysql,php pdo.dll,php pdo mssql.dll,php pdo扩展,php pdo mysql.dll,php pdo.dll下载,php pdo mssql,php pdo类,php pdo mysql类,php pdo fetch,php pdo sqlite,php pdo odbc,php开启pd

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>

Copy after login

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.