Home > Article > Backend Development > [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.