使用 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.

编辑

实际上，我想我会选择 Doctrine 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屋！