lluiscab lluiscab - 3 months ago 12
MySQL Question

Selecting mysql row containing user id inside a field

I am working on some type of private conversations site and I am storing conversations on a table (Messages are stored on another table)

That's the format:


  • Id: Auto incrementing

  • Title: Varchar, represents the name of the conversation

  • Members: Saves the ids of the members with access to the conversation



My problem is that the way that I'm storing members is literally horrible. I'm using
user1Id;user2Id;user3Id
.

The problem appears when trying to get all conversations of a specific player.

My question is: Is there any better way of storing the ids? (On one field if possible) and also being able to select all conversations of a specific id?

Answer

The "proper", normalized, database way would be to have another table, member_conversation_map with two columns - the member ID and the conversation ID, and query it with, e.g., an in operator:

SELECT *
FROM   conversations
WHERE  id IN (SELECT conversation_id
              FROM   member_conversation_map
              WHERE  member_id = 123)

If that's not an option, and you positively have to have the member IDs in a single cell in the database, at least use commas instead of semicolons as your delimiter. This will allow you to use MySQL's find_in_set:

SELECT *
FROM   conversations
WHERE  FIND_IN_SET('123', members) > 0