Insert/update helper function using PDO

Question

I have a very simple helper function to produce SET statement for traditional plain mysql driver usage:

function dbSet($fields) {
  $set='';
  foreach ($fields as $field) {
    if (isset($_POST[$field])) {
      $set.="`$field`='".mysql_real_escape_string($_POST[$field])."', ";
    }
  }
  return substr($set, 0, -2); 
}

used like this

$id = intval($_POST['id']);
$fields = explode(" ","name surname lastname address zip fax phone");
$_POST['date'] = $_POST['y']."-".$_POST['m']."-".$_POST['d'];
$query  = "UPDATE $table SET ".dbSet($fields)." stamp=NOW() WHERE id=$id";

it makes code quite DRY and easy but flexible at the same time.

I gotta ask if anyone willing to share a similar function, utilizing PDO prepared statements feature?

I am still in doubts, how to accomplish this.
Is there a straight and simple way to use PDO prepared statements to insert data? What form it should be? Query builder helper? Or insert query helper? What parameters it should take?

I hope it can be easy enough to be used as an answer here on SO. Because in the every topic we can see prepared statements usage recommendation, but there is not a single good example. Real life example, I mean. To type bind_param() 20 times is not a good programming style I believe. And even 20 question marks too.

Answer 1

I usually have a class extending PDO, but my class is pretty custom. If I get it cleaned up and tested I will post it at a later time. Here is a solution to your system, however.

function dbSet($fields, &$values) {
    $set = '';
    $values = array();

    foreach ($fields as $field) {
        if (isset($_POST[$field])) {
            $set .= "`$field` = ?,";
            $values[] = $_POST[$field];
        }
    }

    return rtrim($set, ',');
}

$fields = explode(" ","name surname lastname address zip fax phone date");
$_POST['date'] = $_POST['y']."-".$_POST['m']."-"$_POST['d'];

$query  = "UPDATE $table SET ".dbSet($fields, $values).", stamp=NOW() WHERE id=?";
$values[] = $id;

$dbh->prepare($query);
$dbh->execute($values);  

This may not be perfect and could use tweaking. It takes into account that $dbh is setup with a PDO Connection. Pending any minor syntax issues I made, that should work.

EDIT

Really though, I think I would go for Doctrine ORM (or another ORM). As you setup the model and add all the validation there, then it is as simple as:

$table = new Table();
$table->fromArray($_POST);
$table->save();

That should populate the contents easily. This is of course with an ORM, like Doctrine.

UPDATED

Did some minor tweaks to the first code, such as putting isset back and using rtrim over substr. Going to work on providing a mock up of a PDO Extension class just gotta layout the way to do it and do some unit tests to make sure it works.