-1

I have question . If i have user table and a column name be freinds . I want use some user id inside a column friends with this value : 1|2|45|18 . This means for example user id =5 have 4 freinds with these id 1|2|45|18. I don't want use two table for this situation.

How can select each users data when friends with user id = 5 ? In php we have explode function for slice each delimeter . Can i set delimeter for this target or i should use two table user and friends in MySQL ?

farzad
  • 612
  • 8
  • 24
  • 1
    As you are realizing, this is nice and easy in theory, but a nightmare to actually work with. The painful but correct way would be to use normalization - in this case, having a `friends` table with `userID` and `friendID` columns, with one row for each relationship – Pekka Nov 13 '16 at 20:09
  • There are many related SO questions when Googling `mysql friends table`, they come with a lot of information and ideas, e.g. http://stackoverflow.com/questions/3009190/mysql-friends-table – Pekka Nov 13 '16 at 20:10
  • If you don't want to use a relational database, don't use a relational database. – Strawberry Nov 13 '16 at 21:50

1 Answers1

1

It's an awful case, especially if you inherit some DB with such a scheme, but to find friends you can use:

SELECT friend.*
FROM user u 
left join user friend on  concat('|', o.friends, '|') like concat('%|', friend.id, '|%')

where u.id = 5;
2oppin
  • 1,941
  • 20
  • 33