Set PHP variable to MySQL wildcard '%'

Question

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.

I want it to be like that.. if there's no tag is selected then to show all posts regardless of the tag. — Salam, Aug 13 '17 at 16:37

scorgn · Accepted Answer · 2017-08-13T16:53:33.607

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

Using the `LIKE` operator actually solved it! My query now is `SELECT * FROM recipes WHERE tags LIKE ?`. One query only and it worked with what I've done before `$tags = '%';` — Salam, Aug 13 '17 at 16:53
Please note that with that you will be excluding all rows that have a `NULL` value for the `tags` column, and also all rows that have an empty string for the `tags` column. — scorgn, Aug 13 '17 at 16:55
Got the Idea. I used your method now with `if` and `else` 2 queries to avoid missing the rows with `NULL` and empty strings. — Salam, Aug 13 '17 at 16:58

Set PHP variable to MySQL wildcard '%'

1 Answers1

Linked