Only with SELECT
As remembered by @bfavaretto, MySQL has a specific function for your need:
SELECT * FROM chat WHERE FIND_IN_SET( '09', participantes );
The most general solution for several SQL dialects is to find it this way:
SELECT * FROM chat WHERE CONCAT( ",", participantes, "," ) LIKE '%,09,%';
However, if you have a lot of data the performance will not be the most surprising 1 . Of course in PHP you will put a variable in place of the 09
of the example (be careful to avoid SQL Injection).
Remember to hit your items consistently, because in your example you have one, two and three digit things, and some with zero before and others not, which is a sign of something slightly strange 2 if in real case you really are that way.
It would be better ...
... use an auxiliary table relating the participants to the rooms, so you would have something in this model:
sala | participante
------+---------------
1 | 12
1 | 7
1 | 342
1 | 1
2 | 12
2 | 28
3 | 1
...
So you just have to study how to JOIN to find the data . In addition to better organizing your data, you'll benefit from using indexes to make your SELECT
more efficient, and you'll probably be able to take advantage of this table for other parts of your system, which now depend on the comma-separated field. p>
1. horrible, in the case of like
, a little better in find_in_set
2. zica