select comma separated data in MySQL

Question

I want to select comma seperated data in query but i have some problems. The page have a categories list and uniq id. And the categories link like this;

<a href="index.php?page=categories&cat=1">Books</a>

I'm catching with $_GET "cat" id and listing matching users.

My problem when click cat=1, its listing all the user categories have 1 in it. Like id 1 and 11 and 15.

This is the my user table and his selected categories.

| User           | Categories | 
+----------------+------------+
| Jhon Doe       | 1,5,11     |

This is the query;

  $catid=$_GET['cat'];
  $list=$db->query("
  SELECT * FROM users 
  WHERE categories
  LIKE '%$catid%'")->fetchAll(PDO::FETCH_ASSOC);

[`FIND_IN_SET`](https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_find-in-set), but if possible please consider restructuring your schema; CSV columns make everyone sad — iainn, May 08 '18 at 19:24
Either normalise ypur schema, or don't bother using a relational database. — Strawberry, May 08 '18 at 19:27
Seeing you're using PDO; why don't you use a prepared statement for it? — Funk Forty Niner, May 08 '18 at 19:28
I think I should create a new table with categories and member id. And I have to record each one separately. — , May 08 '18 at 19:29
I would like to thank you all for your ideas. It's time for me to separate the tables. — , May 08 '18 at 19:32
I always like to share this link for questions on this topic: https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — Don't Panic, May 08 '18 at 21:02

Uueerdo · Answer 1 · 2018-05-08T19:39:21.307

0

It is almost always a bad idea to put lists of values in a field in SQL; this is no exception. Since your list is formatted nicely, you just have to take advantage of that... keep in mind this query will be extremely slow on anything but very small databases.

WHERE categories = '$catid'  -- only item
   OR categories LIKE '$catid,%' -- first item
   OR categories LIKE '%,$catid' -- last item
   OR categories LIKE '%,$catid,%' -- somewhere in the middle
;

Edit: Corrected above for actual list format; and as tadman said in the comment, FIND_IN_SET() is more succinct when your format is accepted by it (which this one is)... it is good to keep in mind that FIND_IN_SET() could be doing something similarly expensive behind the scenes.

edited May 08 '18 at 19:39

answered May 08 '18 at 19:28

Uueerdo

15,723
1
16
21

1

`FIND_IN_SET()` and not this monstrosity. – tadman May 08 '18 at 19:34
lol, did not notice his list values were only comma-separated; the few times I have had to deal with this kind of mess, the values were either "comma-space" delimited or delimited by something other than commas; neither of which find_in_set handles. – Uueerdo May 08 '18 at 19:36

select comma separated data in MySQL

1 Answers1