zoro724 zoro724 - 2 years ago 63
PHP Question

mysql LIKE statement to do exact match

I am trying to use a LIKE statement in mysql, i have code that is sort of half working but its not working as intended (or at least how i imagine it to work)

Below is the code i have

SELECT * FROM `videos` WHERE `tags` LIKE '%1%' ORDER BY `videoID` DESC

So to try and explain the code inside the table "videos" i have a column "tags", tags is populated with some data like "1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 " each video has its own set of tags made out of the above combination, now i am trying to select all videos with the tag 1 present however the above will select all videos with the numeric 1 so 1, 10, 11, if i change the code to

SELECT * FROM `videos` WHERE `tags` LIKE '%1' ORDER BY `videoID` DESC

It will pick 1, 10, 11 if i change it to

SELECT * FROM `videos` WHERE `tags` LIKE '1%' ORDER BY `videoID` DESC

It will pick both 1 and 11 however i just want it to pick 1 like wise if I'm picking all videos with 11 i just want the videos with 11 present in "tags" to be featured.

Anyone got an ideas on where i'm going wrong on this?

I have 2 tables tags, and videos see below for an example

Tags table

| tagID | tagTitle | tagImage |
| 1 | Anime | PATH |
| 2 | FPS | PATH |
| 3 | RPG | PATH |

Video table

| videoID | tags | videoTitle |
| 1 | 2 3 | EXAMPLE |
| 2 | 1 | EXAMPLE |
| 3 | 1 2 3 | EXAMPLE |

I opted to go for 2 tables rather than 1 so the main videos table was not littered with tons of full tag names which can get very long with "beat em up and hack and slash" games "4" is much shorter.

Scenario (if this helps)

I'm making a website where i will store my YouTube videos so they can be found from more than once place on the site you can pick videos by tag (horror, rpg, etc) once you pick your tag you get taken to the page that will display all the videos under that tag.

If you need me to expand on anything just let me know.

Thanks in advance.

Answer Source

You can try with multiple like:

where `tags` like '% 1 %'
   or `tags` like '1 %'
   or `tags` like '% 1'
   or `tags` = '1'

It should match 1 in the middle, start or end of the string. In case tags are ordered inside each string (assumption made upon your example), you can use just second like and exact match = check.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download