将PHP变量设置为MySQL通配符'％' [英] Set PHP variable to MySQL wildcard '%'

问题描述

我正在尝试使用PDO查询帖子，其中数据库列标签" =某物.

I'm trying to query posts using PDO where the database column 'tags' = something.

我的问题是:即使没有设置$_GET['tag']请求，这也是我的代码，我也希望我的查询能够正常工作.

My problem is: I want my query to work even if there's no $_GET['tag'] request is set and here's my code.

if (!isset($_GET['tag'])) {
    $tags = '%';
} else {
    $tags = $_GET['tag'];
}

$get_recipes = $con->prepare ("SELECT * FROM recipes WHERE tags = ?");
$get_recipes->execute(array($tags));
$recipes = $get_recipes->fetchAll();

将PHP变量$tags设置为MySQL通配符%是否有效?如果不可能的话，该怎么做才能使查询正常工作?

Is it valid to set the PHP variable $tags to the MySQL wildcard %? if not possible then what should I do to make my query work?

当我运行该代码并且没有编写$_GET['tag']时，查询将不会从数据库中获取任何帖子.

When I run that code and there's not $_GET['tag'] is written the query will not fetch any posts from the database.

推荐答案

在带有PDO的预准备语句中使用通配符

在MySQL中使用通配符时，必须使用LIKE运算符.在PDO中将通配符与参数绑定是正确的.

When using a wildcard in MySQL you must use the LIKE operator. It is correct to bind the wildcard with parameters in PDO.

您将像这样准备您的声明.

You would prepare your statement like so.

$get_recipes = $con->prepare ("SELECT * FROM recipes WHERE tags LIKE ?");

然后，您将使用%字符绑定参数，就像这样.

And then you would bind your parameter using the % character, like so.

 $get_recipes->execute(array('%'));

虽然这是按照您所建议的方式使用通配符的正确方法，但这并不是完成您要尝试执行的操作的正确解决方案.

While that is the correct way to use a wildcard in the way you've proposed, that is not the correct solution to do what you're trying to do.

如何实现您要实现的目标

在代码中，如果未设置$_POST['tags']，则似乎要选择所有行；如果已设置，则要选择将tags列设置为$_POST['tags']值的所有行.为此，您需要像这样在条件内准备语句.

In your code it looks like you want to select all rows if $_POST['tags'] is not set, and if it is set you want to select all rows that have the tags column set to the value of $_POST['tags']. To do this, you would want to prepare your statement inside the conditional, like so.

if (!isset($_GET['tag'])) {
    $get_recipes = $con->prepare ("SELECT * FROM recipes");
    $get_recipes->execute();
} else {
    $get_recipes = $con->prepare ("SELECT * FROM recipes WHERE tags = ?");
    $get_recipes->execute(array($_GET['tag']));
}

$recipes = $get_recipes->fetchAll();

这篇关于将PHP变量设置为MySQL通配符'％'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！