有没有比这更好的管理查询变量的方法? [英] Is there a better way of managing variables in query than this?

问题描述

如果我需要根据某些变量从数据库中查询某些数据，有没有比这样做更好的方法?

If I need to query certain data from the database depending on some variables is there a better way than doing this ?

<?php
if($_POST['condition'] == '1'){
  $sign = '<';
  $number = '3';
} else if($_POST['condition'] == '2'){
  $sign = '>';
  $number = '10';
}

SELECT value1, value2 FROM table WHERE id $sign $number 

?>

如果我有一个像这样的简单查询，这有效，但我也需要 ORDER BY 中的某些条件，我需要添加另一个 AND 和其他一些条件，然后很快，查询看起来更像是变量，而不是查询.

This works if I have a simple query like this, but I need certain conditions in the ORDER BY too, I need to add another AND with some other conditions and soon the query looks more like variables, than a query.

是否有另一种使用变量来改变查询的方法，如果我在查询中有很多变量是不是很糟糕?

Is there another way of using variables in order to alter the query, is it bad if I have a lot of variables in query like that ?

所有实际使用的查询都正确格式化(希望如此)并使用准备好的语句，这只是示例.

推荐答案

是的，有更好的方法.除了使用准备好的语句来防止 SQL 注入之外，您还可以使用相同的数据在前端生成选择以及构建查询.以这个函数为例:

Yes, there is a better way. Besides using prepared statements to prevent SQL injection, you can use the same data to generate the selection in the frontend as well as building the query. Look at this function for example:

public function getFilterOptions(): array
{
    return [
        ['name' => 'Age >30', 'comparator' => '>', 'comparable' => 30],
        ['name' => 'Age until 21', 'comparator' => '<=', 'comparable' => 21],
    ];
}

然后您可以使用此方法在前端生成过滤器选择:

You can then use this method to generate the filter selection in the frontend:

echo '<select name="query_filter">';
foreach (getFilterOptions() as $key => $option) {
    echo '<option value="' . $key . '">' . $option['name'] . '</option>';
}
echo '</select>';

请耐心等待这段代码，因为我不知道提问者使用的是什么模板系统，以纯 PHP 为例.

并且在您的过滤器代码中，您可以使用给定的索引直接访问过滤器选项:

And in your filter code, you can directly access the filter options with the given index:

$optionIndex = $_POST['query_filter'];
// TODO: you should check first if this index exists and handle errors appropriately
$option = getFilterOptions()[$optionIndex];

// building the query will be easy then...
// note: it is not possible to dynamically bind operators of a query
$stmt = $pdo->prepare("SELECT * FROM persons WHERE age $option['comparator'] :comparable");
$stmt->execute($option);
// or explicit: $stmt->execute(['comparable' => $option['comparable']]);
// using PDO is actually not really necessary here as the options are hard coded
// and not user-given, but it is best practice anyway...

这样做的好处是您可以将所有过滤器选项集中在一个位置，使更改变得更容易、更安全.此外，根据用户输入选择过滤器的代码比使用多个 if 和 else if 语句少很多.

The advantage of doing it this way is that you have all your filter options in a central place, making changes both easier and safer. Also the code of selecting the filters based on the user input is a lot less than if you use multiple if and else if statements.

当然，这只是一个非常基本的示例，您可以对其进行很多改进，尤其是使用更复杂的过滤器.例如，您还可以将数据库列作为过滤器选项的一部分.但我只是想给你一个关于可能的提示.

Of course this is only a very basic example, you can improve on it quite a lot, especially with more complex filters. You could for example also have the database column be part of your filter options. But I just wanted to give you a hint as to what would be possible.

这篇关于有没有比这更好的管理查询变量的方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！