SQL Like Operator not working as expected in PHP

Admin 1
Viewed 246

I am trying to fetch all the data from the database with a certain content tags value. If I run the code below, it only returns the row with id 1, even though the word tag is part of the tags value in both rows. I also added %or% operator but it is fetching the exact row. When I search for tag it is showing 2 rows, but using the value in $mytags below, it match es only 1 row.

I want the expression to get a row if anything in $mytags matches.

SQL

$mytags = "tag, tag1";
SELECT * FROM table WHERE tags LIKE '%$mytags%' ORDER BY id DESC
id body tags
1 Loreum Content 1 tag, tag1
2 Loreum Content 2 tag
sql-like mysql sql php
1 Answers

Your expectation of how LIKE works seems to be wrong. If you want those lines selected that match any of your comma separated tags in the $mytags variable, you need to do this in a different way.

If you wanted to use LIKE you would need to expand and use OR:

SELECT * FROM table WHERE tags LIKE '%tag%' OR tags LIKE '%tag1%' ORDER BY id DESC

But a better way to achieve this for a variable number of tags is using REGEXP:

SELECT * FROM table WHERE tags REGEXP 'tag|tag1' ORDER BY id DESC

All you need to do for that is change your separator in $mytags to | (no spaces):

$mytags = 'tag|tag1'
user_140157370
Related