You are here

Home » Blogs » d.cochran's blog

Update MySQL PDO Function

published by d.cochran on Thu, 08/02/2012 - 17:00

Below is a PDO update function that I wrote to save me time from
constantly constructing the PDO update query whenever I need it. Now I
simply pass my values and fields to the function and all is taken care
of.

<?php
//START: SQLUpdate
//$fields = array of fields in DB
//$values = array of values respective to the $fields
if (!function_exists("SQLUpdate")) : function
SQLUpdate($table,$fields,$values,$where) {
 
  //Connect to DB
  include('connect.php');
 
  //build the field to value correlation
  $buildSQL = '';
  if (is_array($fields)) {
 
        //loop through all the fields and assign them to the correlating $values
        foreach($fields as $key => $field) :
      if ($key == 0) {
            //first item
            $buildSQL .= $field.' = ?';
          } else {
            //every other item follows with a ","
            $buildSQL .= ', '.$field.' = ?';
          }
    endforeach;
 
  } else {
    //we are only updating one field
        $buildSQL .= $fields.' = :value';
  }
 
  $prepareUpdate = $db->prepare('UPDATE '.$table.' SET '.$buildSQL.'
WHERE '.$where);
 
  //execute the update for one or many values
  if (is_array($values)) {
    $prepareUpdate->execute($values);
  } else {
        $prepareUpdate->execute(array(':value' => $values));
  }
 
  //record and print any DB error that may be given
  $error = $prepareUpdate->errorInfo();
  if ($error[1]) print_r($error);
 
} endif;
//END: SQLUpdate
?>

Here are some examples of how to use it:

<?php
//updating one item
SQLUpdate('db_table', 'db_field', 'db_value','id = 1');
 
 
//updating multiple items
$fields[] = 'name';
$fields[] = 'description';
$fields[] = 'value';
$fields[] = 'stock';
 
$values[] = $_POST['name'];
$values[] = $_POST['description'];
$values[] = $_POST['value'];
$values[] = $_POST['stock'];
 
SQLUpdate('db_table', $fields, $values,'type = "product"');
 
?>

Tags: