Update multiple database columns using PDO

Posted by: Alex on December 18, 2014

Updating and inserting large sets of data into a database (mySQL etc) can be very tedious when the data is linked explicitly to a column name, especially when there are lots of fields.

The below codebyte is a very simple way, using PDO, of assigning and binding your column names to data that is to added or updated.

One typical example is handling a large user form, where easily 10 or more form fields will need to be inserted into the database. With this codebyte you simply just use the $data array to layout and bind the column name to the variable containing corresponding data.

Just replace the $conn variable with the variable of your PDO connection, and the table_name with the table you’d like to update.

$data = array(
  // Just Examples:
  // 'column name' => 'value',
  'username' => $username,
  'first_name' => $first_name,
  'last_name' => $last_name,
  'sign_up_date' => time()

function buildBindedQuery($fields){
  $lastField = key($fields);
  $bindString = ' ';
  foreach($fields as $field => $data){
    $bindString .= $field . '=:' . $field;
    $bindString .= ($field === $lastField ? ' ' : ',');
  return $bindString;

$query = "INSERT INTO table_name SET" . buildBindedQuery($data);
// Replace $conn with the variable of your PDO connection
$result = $conn->prepare($query);