使用键作为列名的PDO插入阵列 [英] PDO Insert Array Using Key As Column Name

问题描述

我正在使用PDO将PHP数组的$ _POST内容插入到表中.我正在看下面的代码行，而其中之一就是必须有更好的方法来做到这一点".如果键名称与表中的列名称匹配，是否有更简单的方法来插入所有键?

I am inserting the $_POST contents of my PHP array into a table with PDO. I was looking at the following lines of code and I had one of those "there has to be a better way to do this" moments. If the key name matches the column name in the table, is there a more simple way to insert all of it?

例如代码:

$statement = $db->prepare("INSERT INTO `applications`(`username`, `email`, `password`, `name`) VALUES (?,?,?,?)");

$statement->execute(array($_POST['username'], $_POST['email'],$_POST['password'],$_POST['name']));

此代码有效，但似乎有点多余(尤其是随着越来越多的列添加).

This code WORKS but it just seems a bit over-the-top (especially as more and more columns are added).

推荐答案

我会这样做:

首先声明各列.我们将使用它们来提取$ _POST的子集以用作列.否则，用户可能会传递与表的任何列都不匹配的虚假请求参数，这会破坏我们的SQL.

Declare the columns first. We'll use these to extract a subset of $_POST for use as columns. Otherwise a user could pass bogus request parameters that don't match any columns of the table, which would break our SQL.

$columns = array('username','email','password','name');
$column_list = join(',', $columns);

创建命名参数占位符，即:username.

Create named parameter placeholders i.e. :username.

$param_list = join(',', array_map(function($col) { return ":$col"; }, $columns));

分别形成SQL，因为如果它在自己的变量中，则更易于阅读和调试.

Form the SQL separately, because it's easier to read and debug if it's in its own variable.

$sql = "INSERT INTO `applications` ($column_list) VALUES ($param_list)";

始终检查从prepare()和execute()返回的错误状态.

Always check for error status returned from prepare() and execute().

$statement = $db->prepare($sql);
if ($statement === false) {
  die(print_r($db->errorInfo(), true));
}

在这里，我们只接受与要插入的列相匹配的$ _POST字段.

Here we take only the fields of $_POST that match the columns we want to insert.

$param_values = array_intersect_key($_POST, array_flip($columns));

并将该数组传递给execute().再次，检查错误返回状态.

And pass that array to execute(). Again, check for error return status.

$status = $statement->execute($param_values);
if ($status === false) {
  die(print_r($statement->errorInfo(), true));
}

这篇关于使用键作为列名的PDO插入阵列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！