lluiscab lluiscab - 1 year ago 62
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

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 Source

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:

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:

FROM   conversations
WHERE  FIND_IN_SET('123', members) > 0
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download