Getting rows which include a value with MySQL

Question

I have a MySQL query which gets including some vars like that:

messages TABLE receiver cols

• user1 rows : 1,3,5
• user2 rows : 2,3
• user3 rows : 1,4

I want to get rows which includes '3' value. So I will get 'user1' and 'user2'.

I tried that but naturally it doesn't work.

mysql_query("SELECT * FROM messages WHERE receiver='3'");

How can I do this?

Answer 1

Try this:

mysql_query("SELECT * FROM messages WHERE receiver like '%3%'");

Answer 2

The easiest way would be to restructure your database.

Have a table messages with id, text, etc., a table receivers with id, name and a table msg2rec (or however you wanna call it) with id_msg and id_rec.

That way you can do:

SELECT m.text, r.name
FROM msg2rec m2r 
LEFT JOIN messages m ON m2r.id_msg = m.id 
LEFT JOIN receivers r ON m2r.id_rec = r.id 
WHERE m2r.id_rec = 3

---

Alternatively, if you keep your structure you should use:

 SELECT text 
 FROM messages 
 WHERE rec RLIKE (",3,") OR rec RLIKE("^3,") OR rec RLIKE(",3$")

There's probably one single regexp that does the same as the 3 above, but I can't figure it out right now. Anyway, I would go with the first option: cleaner, easier to mantain and to work with

Answer 3

Like others have mentioned, trying to search denormalized data "1,2,3,4,44,5,55" in a relational database is not a good approach. Relational databases are built for handling normalized data.

You could.. note, could split the string into a subset (Split function in MySQL), then do a IN-search operation, but this is neither a recommended approach.

The best way to tackle the problem is from the top. Since trying to build around it, instead of actually fixing it, is never a good way to go.

The solution; Normalize the data.

Message
- [PK] Id
- Body...

Reciever
- [PK] Id
- Name...

MessageReceiver
- [FK] MessageId
- [FK] ReceiverId

Having this table structure, you could easily query:

SELECT M.* FROM MessageReceiver MR
INNER JOIN Message M ON M.Id=MR.MessageId
WHERE MR.ReceiverId=3

Solving it this way will save you a lot of headache in the future.