Insert/update helper function using PDO

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.

Thanks to everyone.
Every answer was helpful and I wish I could split the bounty.

At the end, to my surprise, I was able to make it the same way as before, based on on accepted answer

$fields = array("login","password");
$_POST['password'] = MD5($_POST['login'].$_POST['password']);
$stmt = $dbh->prepare("UPDATE users SET ".pdoSet($fields,$values)." WHERE id = :id");
$values["id"] = $_POST['id'];
$stmt->execute($values);

It can be wrapped into a helper function, but I doubt there is necessity. It will shorten the code by just one line.

pdoSet code:

function pdoSet($fields, &$values, $source = array()) {
  $set = '';
  $values = array();
  if (!$source) $source = &$_POST;
  foreach ($fields as $field) {
    if (isset($source[$field])) {
      $set.="`$field`=:$field, ";
      $values[$field] = $source[$field];
    }
  }
  return substr($set, 0, -2); 
}

i would extend the Core PDO Class andd a method like so:

class Database extends PDO
{
    public function QueryFromPost($Query,$items)
    {
        $params = array();
        $Query .= ' WHERE ';

        foreach($items as $key => $default)
        {
             $Query .= ' :' . $key. ' = ' . $key;
             if(isset($_POST[$key]))
             {
                  $params[':' . $key] = $_POST[$key];
             }else
             {
                 $params[':' . $key] = $default;
             }
        }
        $s = $this->prepare($Query);
        return $s->execute($params);
    }
}

Then use like so

$db = new Database(/*..Default PDO Params*/);
$statement = $db->QueryFromPost('SELECT * FROM employees',array('type' => 'plc'));
foreach($preparedStatement->fetchAll() as $row)
{
    //...
}

But as its already been said that you should be VERY weary of what your trying to do, you need to validate your data, its been sanitized but you not validated.

I've been patching something trivial together for what I consider recurring parameter binding cases. http://fossil.include-once.org/hybrid7/wiki/db

Anyway; it provides some alternative prepared statement placeholders. Your example could be shortened into:

db("UPDATE table SET :, WHERE id=:id", $columns[], $where[]);

This case only works with named parameters, so $set would be array("name"=>..) and $where=array("id"=>123). The :, gets expanded on the first array you pass. It's replaced with comma-separated name=:name pairs (that's why its mnemonic is :,).

There are a few more placeholders :, :& :: and :? for different use cases. Only the ?? is really somewhat of a standard. So it needs some getting used to, but it significantly simplifies prepared statements and array binding (which PDO doesn't do natively).

Insert/update helper function using PDO

Related

Recent Posts