PDO Insert Array Using Key As Column Name

Question

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?

Code for example:

$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).

Use [`bindValue`](http://www.php.net/manual/en/pdostatement.bindvalue.php) and iterate through an array of the columns. — Waleed Khan, Nov 24 '12 at 20:28

score 13 · Accepted Answer · answered Nov 24 '12 at 20:37

13

I would do it this way:

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

Create named parameter placeholders i.e. :username.

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

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

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

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

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

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

answered Nov 24 '12 at 20:37

Bill Karwin

538,548
86
673
828

What's the benefit of using `array_intersect_key` to "minimize" the `$_POST` array? – Blazemonger Apr 29 '13 at 21:40
1

@Blazemonger, to ensure that the user can't spoof the POST parameters and break your app. – Bill Karwin Apr 29 '13 at 22:09
i have the same issue but i am working inside a class and the values are not getting from $_POST its coming as a parameters, any idea? please. – Masoud Mustamandi Nov 29 '14 at 17:14
@MasoudMustamandi, you can't use the function argument instead of the $_POST array? – Bill Karwin Nov 29 '14 at 19:12
No, i did something like this, now it works $param_values = array_combine($columns, $values); – Masoud Mustamandi Dec 03 '14 at 06:52

PDO Insert Array Using Key As Column Name

1 Answers1

Linked