使用PDO插入/更新帮助器功能 [英] Insert/update helper function using PDO

问题描述

我有一个非常简单的辅助函数，可以为传统的普通mysql驱动程序用法生成SET语句:

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

像这样使用

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

我要问是否有人愿意利用PDO准备好的语句功能来共享类似的功能?

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

我仍然对如何实现这一目标感到怀疑.
是否有使用PDO准备好的语句插入数据的简单明了的方法? 它应该是什么形式?查询构建器助手?还是插入查询助手?应该采用什么参数?

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?

我希望它可以很容易用作SO的答案.因为在每个主题中我们都可以看到准备好的语句用法建议，但是没有一个很好的例子.我的意思是现实生活中的例子.我认为，键入bind_param()20次不是一种好的编程风格. 甚至还有20个问号.

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.

推荐答案

我通常有一个扩展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);  

这可能并不完美，可能需要进行调整.考虑到$dbh是通过PDO连接设置的.待我提出的任何次要语法问题，都应该可以.

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.

编辑

尽管如此，我认为我会选择教义ORM(或另一个ORM).当您设置模型并在其中添加所有验证时，它就很简单:

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

那应该很容易地填充内容.当然，这对于ORM(如Doctrine)而言.

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

已更新

对第一个代码进行了一些细微的调整，例如放回isset并在substr上使用rtrim.要提供一个PDO扩展类的模型的工作，就必须布局该方法并进行一些单元测试以确保它可以工作.

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.

这篇关于使用PDO插入/更新帮助器功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！