如何避免用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 => value对:

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 BaseDataMapper
{
    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屋！