将PHP变量设置为MySQL通配符'％'[重复]

Question

This question already has an answer here:

• pdo prepared statements with wildcards 2 answers

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

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();

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?

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

</div>

Answer 1

Using Wildcards in Prepared Statements With 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.

How to achieve what you're trying to achieve

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();