Member Avatar for RikTelner

Let's say I have such table:

+----+------------------+
| id | participantsId   |
+----+------------------+
| 1  | 1,24,192,3481,12 |
+----+------------------+

I'd like to select every single result in this table, where user with ID 3481 has partcipated. How can I do that? Ye old SELECT * FROM example WHERE paricipantsId=$id won't work.

Couple things worth noting, that in this particular field, there may be just alone 3481, or tens or even hundreds of other IDs. And they are most likely to be seperated by comma, since there are no strings allowed, only integers (for input from PHP, of course field will have to be a string).

How could I select every row that has participantID 3481 amongst many others in the same cell?

Edited by RikTelner
  • 2 Contributors
  • 1 Reply
  • 158 Views
  • 43 Minutes Discussion Span
  • Latest Post Latest Post by cereal
Member Avatar for cereal

With MySQL you can use FIND_IN_SET():

SELECT * FROM `example` WHERE FIND_IN_SET(192, `paricipantsId`);

Docs: http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set

Or, if you need something more complex and you switch to MariaDB, then you can use some new features for dynamic columns:

However, instead of storing this information in CSV style, consider to normalize it, in general this should give better performance, for more information check point 8 of diafol's tutorial:

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.