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

Answer 1

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