CODEWITHSUNDEEP
phpmysqlsqlselect
lluiscab
lluiscab

Reputation: 45

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:

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?

Upvotes: 1

Views: 93

Answers (3)

SattyamBhatt
SattyamBhatt

Reputation: 328

One way to store is using a json array: Define member_ids to VARCHAR and store priviliges ids like:

["11","1","2","3"]

Using json_decode you can get the members when required.

Upvotes: 0

Mureinik
Mureinik

Reputation: 311358

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

Upvotes: 2

Manuel Mannhardt
Manuel Mannhardt

Reputation: 2201

Why not make a member to conversation table? In there you can store members and conversations like this

member_id | conversation_id
        1 |               1
        2 |               1
        3 |               1
        1 |               2
        2 |               2

Meaning member 1, 2 and 3 are part of conversation 1 and member 1 and 2 are of conversation 2

Thats how i do stuff like this and its working good this way.

If you still want to store them in one field, your way would be the best, since you can split them by their seperator and have all ids in an array that way. But as you pointed out, getting conversations by an user id is getting messy pretty quick using this method.

Upvotes: 2

Related Questions