在PDO UPDATE Prepared语句中使用数组 [英] Using Arrays in a PDO UPDATE Prepared statement

问题描述

我有这一系列的标题.

I have this array of titles.

$first = array('title','first_name','surname','phone','email',
'add1','add2','add3','add4','add5');

这些都是数据库中的列标题，也是网页上不同表单项的名称.通过在$first中列出它们，我可以使用以下数组使用此数组链接到数据库和在表单提交时创建的POST值.

These are both the column titles in a database, and the names of different form items on a web page. By listing them in $first I can use this array to link to the database and the POST values created on form submission by using the following code.

$sql = "INSERT INTO first_page_data (" . implode(',',$first) . ") VALUES (" . implode(',', array_fill(0, count($first), '?')) . ")";
$stmt = $db->prepare($sql);
foreach($first as $key=>$val){
    $stmt->bindValue($key+1, $_POST[$val], PDO::PARAM_STR);

$stmt->execute();

此代码将使用$first数组创建有效的SQL查询(并且还将为未命名的占位符创建正确数量的问号).然后它将遍历将POST值绑定到正确的问号的数组.

This code will create a valid SQL query using the $first array (and will also create the correct amount of questionmarks for the unnamed placeholders). It will then loop through the array binding the POST values to the correct questionmark.

此代码可以正常工作，但是我现在尝试将其改编为UPDATE查询而不是INSERT，这是我遇到绊脚石的地方:

This code works with no issues, but im now trying to adapt it for an UPDATE query rather than an INSERT, which is where im hitting a stumbling block:

INSERT查询具有数据库列标题的完整列表，然后具有它们的值的完整列表，而UPDATE在title:value对中需要它们(据我所知！).

The INSERT query has a full list of database column titles and then a full list of their values, whereas the UPDATE needs them in title:value pairs (as far as I can see anyway!).

是否有一种使用2个单独的列表进行更新的方法(按照INSERT)，还是必须将$first数组和POST数组拆分为对"?

Is there a way of updating using 2 separate lists (as per INSERT) or do I have to split the $first array and the POST array up into 'pairs'?

解决方案

Solution

以下是有效的SQL语句:

The following is the working SQL statement:

$sql = "UPDATE first_page_data SET " . implode(", ", array_map(function($v){ return "$v=?"; }, array_values($combined))) . " WHERE email = ?";

它很大程度上基于已接受的答案，并进行了一些调整才能使其正常工作！

Its based heavily on the accepted answer, with a few tweaks to get it working!

推荐答案

您想要一个看起来像这样的查询:

You want a query that looks something like this:

UPDATE table SET title=?, first_name=?, ... WHERE id=?;

要构建SET表达式，您可以执行以下操作:

To build the SET expression, you can do something like this:

implode(", ", array_map(function($v){ return "$v=?"; }, array_keys($first)));

然后，您可以像在代码中一样添加参数.

Then you can add your parameters the same way you did in your code.

请务必记住，查询中的WHERE子句还有一个额外的参数，因此请确保也添加该参数.参数的顺序很重要，因此请确保最后一个是您的WHERE条件.

It is important to remember that there's an extra parameter in the query for the WHERE clause, so make sure you add that one too. The order of the parameters is important, so make sure the last one is your WHERE condition.

这篇关于在PDO UPDATE Prepared语句中使用数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！