CODEWITHSUNDEEP
mysqlsqlregexmysql-regexp
sheikhamir
sheikhamir

Reputation: 17

MySql - Select records like a particular number present in a string and separated by commas

I have a table named product_info containing the following data:

+-------------+------------+----------------+
|    title    |    slug    |     tags_id    |
+-------------+------------+----------------+
|   Maserti   |  /maserati |   10,34,7,110  |
|   Ferrari   |  /ferrari  |   10,34,7,107  |
| Rolls Royce | /rollroyce | 6,10,121,64,78 |
| Caterpillar |    /cat    |   4,8,210,214  |
|  Peterbilt  | /peterbilt |  8,100,210,214 |
+-------------+------------+----------------+

Next I have a string of php:

$tag = ['id' => 10, 'slug' => 'luxurycars', 'title' => 'LuxuryCars'];

I want to search my table and see if I have any records with this tag. You can see the ids of the tags are present in the tags_id row.

As you can see, the id of the tag is '10'. I have tried many queries but the result is always not what I want it to be. For Example: I want to search for '10' in tags_id and I want it to match only '10' but it matches 10, 107, 110 and 210 and any numbers which resemble or have 10 in them.

What I expect is when I search for '10' (which is a tag named LuxuryCars), I want Maserati, Ferrari and Rolls Royce in result (obviously by being searched by tags_id as they have in the table)

I dont want Peterbilt and Caterpillar in my result just because they have a tag 210 associated to them.

Please help! Thanks for your time!

Upvotes: -2

Views: 297

Answers (1)

GMB
GMB

Reputation: 222632

To search for a single value in a comma-separated list, you can use FIND_IN_SET():

SELECT *
FROM mytable
WHERE FIND_IN_SET('10', tags_id);

From the documentation:

FIND_IN_SET(str,strlist)

Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by , characters.

Upvotes: 1

Related Questions