如何使用 PHP SQL 准备语句避免代码重复? [英] How to avoid code repetition with PHP SQL prepared statements?

问题描述

在我看到的大多数 SQL PHP 准备语句示例中，例如:

In most examples of SQL PHP prepared statements that I see, such as:

$sql = 'INSERT INTO tasks(task_name, start_date, completed_date) VALUES(:task_name, :start_date, :completed_date)';
$stmt = $this->pdo->prepare($sql);
$stmt->execute([
        ':task_name' => $taskName,
        ':start_date' => $startDate,
        ':completed_date' => $completedDate,
    ]);

字段名称几乎重复...... 4 次！

the field names are nearly repeated ... 4 times!

• 在 INSERT INTO(...) 之后:task_name(SQL 中的列名)
• 在 VALUES(...) 之后::task_name
• 字典中的一次键::task_name
• 字典中的一次值:$taskName(局部变量)

• once after the INSERT INTO(...): task_name (column name in SQL)
• once after the VALUES(...): :task_name
• once in the dictionary key: :task_name
• once in the dictionary value: $taskName (local variable)

我知道每一个都有不同的含义，但这种冗余仍然很烦人:如果我们想更改查询中的某些内容，我们必须更改它 4 次！

I understand that each of these have a different meaning, but still, this redundancy is really annoying: if we want to change something in the query, we have to change it 4 times!

如何有一个更好的准备好的语句，避免在 PHP 中出现如此多的冗余?

推荐答案

这是一个很好的问题，我有几个答案.

That's a very good question and I have several answers for it.

首先，您可以使用几种技巧来减少冗长，例如省略查询中的 fields 子句(并在 values 子句中为缺失的字段添加默认值)和使用位置占位符:

First of all, you can use several tricks to reduce the verbosity, like omitting the fields clause in the query (and adding default values in the values clause for the missing fields) and using positional placeholders:

$sql = 'INSERT INTO tasks VALUES(null, ?, ?, ?)';
$this->pdo->prepare($sql)->execute([$taskName, $startDate, $completedDate]);

我称它们为技巧，因为它们并不总是适用.

I call them tricks because they aren't always applicable.

请注意，您必须为表中的所有列提供一个值.它可能只是一个 null 值，或者，为了使其 100% 等效于省略的字段，您可以将其设置为 DEFAULT(field_name) 这样它就会插入一个默认值在表定义中定义.

Note that you must provide a value for all the columns in the table. It could be simply a null value or, to make it 100% equivalent for the omitted field, you could have it as DEFAULT(field_name) so it will insert a default value defined in the table definition.

下一级是为插入创建一个辅助函数.执行此操作时，必须敏锐地意识到SQL 注入> 通过字段名称.

The next level would be creation of a helper function for inserts. When doing this, one must be acutely aware of the SQL Injection through field names.

因此，这样的辅助函数必须有自己的辅助函数:

Hence, such a helper function must have a helper function of its own:

function escape_mysql_identifier($field){
    return "`".str_replace("`", "``", $field)."`";
}

有了这样一个函数，我们可以创建一个辅助函数，它接受一个表名和一个包含field name =>的数据数组.值对:

Having such a function we can create a helper function that accepts a table name and a data array contains field name => value pairs:

function prepared_insert($conn, $table, $data) {
    $keys = array_keys($data);
    $keys = array_map('escape_mysql_identifier', $keys);
    $fields = implode(",", $keys);
    $table = escape_mysql_identifier($table);
    $placeholders = str_repeat('?,', count($keys) - 1) . '?';
    $sql = "INSERT INTO $table ($fields) VALUES ($placeholders)";
    $conn->prepare($sql)->execute(array_values($data));
} 

我故意不在这里使用命名占位符，因为它使代码更短，占位符名称中可能存在不允许使用的字符，而对于列名称，例如空格或破折号完全有效；也因为我们通常不关心它在内部是如何工作的.

I am intentionally not using named placeholders here because it makes the code shorter, there could be characters disallowed in placeholder names while being perfectly valid for the column names, a space or a dash for example; and also because we generally don't care how it works inside.

现在你的插入代码会变成

Now your insert code will become

prepared_insert($this->pdo, 'tasks',[
    'task_name' => $taskName,
    'start_date' => $startDate,
    'completed_date' => $completedDate,
]);

删除了这么多重复

但是我也不喜欢上面的解决方案，它有一些怪癖.

However I don't like the above solution either, there are some quirks in it.

为了满足自动化的需要，我宁愿创建一个简单的 ORM.不要被这个词吓到了，它不像某些图片那样可怕.我最近发布了一个完整的工作示例，因此您也可以将其用于您的案例，尤其是考虑到您已经在使用 OOP.

To fulfill the need for the automation, I would rather create a simple ORM. Don't be scared by the term it is not as monstrous as some picture it. I have a complete working example posted recently so you can use it for your case as well, especially given you are already using OOP.

只需引入一个 insert() 方法

public function insert()
{
    $fields = '`'.implode("`,`", $this->_fields).'`';
    $placeholders = str_repeat('?,', count($this->_fields) - 1) . '?';

    $data = [];
    foreach($this->_fields as $key)
    {
        $data[]  = $this->{$key};
    }
    $sql = "INSERT INTO `{$this->_table}` ($fields) VALUES ($placeholders)";
    $this->_db->prepare($sql)->execute($data);
}

之后，您必须准备课程，

After that you will have to prepare your class,

class Task extends BaseAcctiveRecord
{
    protected $_table = "tasks";
    protected $_fields = ['task_name', 'start_date', 'completed_date'];
}

然后——所有的魔法都发生在这里！- 您根本不必编写插入代码！ 只需创建类的新实例，为其属性赋值，然后调用 insert() 方法:

and then - all the magic happens here! - you won't have to write the insert code at all! Instead just create a new instance of your class, assign values to its properties and then just call the insert() method:

include 'pdo.php';
$task = new Task($pdo);
$task->task_name = $taskName;
$task->start_date = $startDate;
$task->completed_date = $completedDate;
$user->insert();

这篇关于如何使用 PHP SQL 准备语句避免代码重复?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！