CODEWITHSUNDEEP
phpmysqlsqlsql-like
zoro724
zoro724

Reputation: 87

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?

Tables 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.

Upvotes: 1

Views: 4093

Answers (7)

WChampion
WChampion

Reputation: 75

Here is a snippet of some code from one of my current projects

          $sql = 'SELECT * from tbl_fish where MATCH(fish_name,size_name,cat_name) AGAINST(:search_query)';

.............................................................. and here is what is returned

    $row_all = $statement->fetchall(PDO::FETCH_ASSOC);
            header('Content-type: application/json');
            echo json_encode($row_all);

In this, The user has to type the actual Name of the fish or the weight of it in order to Get its results which are then returned as listing the entire row associated with that name/ (ID)

In other words you could use a MATCH AGAINST clause combination to receive specific results and filter out unassociated ones.

Upvotes: 0

geeves
geeves

Reputation: 661

Use Regular Expressions:

SELECT * FROMvideoswheretagsREGEXP '[[:<:]]10[[:>:]]';

Place the number you are looking for between those groups of characters.

[[:<:]],[[:>:]] : These markers stand for word boundaries, and as such they match the beginning and ending of words, respectively. Replace the comma with the number you are looking for.

Upvotes: 0

Alex
Alex

Reputation: 1278

Perhaps you should consider using a pivot table instead of cramming all those tags into the same column, which means using regex parsing each time you'll do that query.

Since in your model a video can have many tag, and given your previous example, you could modify your database to add a pivot table :

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

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

The pivot table named tag_video :

| videoID  | tagID  |
| 1        | 2      |
| 1        | 3      |
| 2        | 1      |
| 3        | 1      |
| 3        | 2      |
| 3        | 3      |

Additionally, you should set your primary key for this pivot table on videoID and tagID (by using PRIMARY(videoID, tagID)).

Then to select videos based on one tag (ie. 1), you do :

SELECT * from `videos` as v
INNER JOIN `tag_video` as tv
ON v.videoID = tv.videoID
WHERE tv.`tagID` = 1
ORDER BY v.`videoID` DESC;

If you want to select videos based of multiple tags (ie. 1, 3 and 22), this structure will allow much faster results. You then do :

SELECT * from `videos` as v
INNER JOIN `tag_video` as tv
ON v.videoID = tv.videoID
WHERE tv.`tagID` IN (1, 3, 22)
ORDER BY v.`videoID` DESC;

Upvotes: 0

Jjsg08
Jjsg08

Reputation: 124

i think that is better seprate you content whit commas "," then you can search

WHERE `tags` LIKE '%,1,%'

The exact match will be located in any position of the field.

Note: Tha start and the end of the field should be , (,1,2,3,4,)

Upvotes: 0

Martin
Martin

Reputation: 22760

You need to pad your column value with your spacer, the trick is to use a NON-SPACE as a delimiter so that MySQL does not auto tidy it, so use fullstop / period being as the delimiter for the numeric value.

Then simply run a single LIKE clause:

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

which will only select .1., and not .10. values.

when writing and updating your query you need to remember to wrap each tag in a delimiter, . and that's it, sorted.

Upvotes: 0

Parsa
Parsa

Reputation: 3236

Include the separator in your like

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

Upvotes: 0

potashin
potashin

Reputation: 44581

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.

Upvotes: 1

Related Questions