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 and iterate through an array of the columns. — Waleed Khan
– Waleed Khan, Commented Nov 24, 2012 at 20:28

Bill Karwin · Accepted Answer · 2012-11-24 20:37:41Z

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, 2012 at 20:37

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Blazemonger Over a year ago

What's the benefit of using array_intersect_key to "minimize" the $_POST array?

Bill Karwin Over a year ago

@Blazemonger, to ensure that the user can't spoof the POST parameters and break your app.

Masoud Mustamandi Over a year ago

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.

Bill Karwin Over a year ago

@MasoudMustamandi, you can't use the function argument instead of the $_POST array?

Masoud Mustamandi Over a year ago

No, i did something like this, now it works $param_values = array_combine($columns, $values);

Collectives™ on Stack Overflow

PDO Insert Array Using Key As Column Name

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related