-1

I have a table name Users, each user has hobbies inside Hobbies column. I want to find users with matching hobbies,

The hobbies are stored like this:

id | username | hobbies
 1 | Ben      | Hiking, Surfing
 2 | Jane     | Surfing,
 3 | Dan      | Reading

I want to sort the table by the matching user's hobbies - so the result will be something like :

HobbieName (Surfing) | id | username
                    | 1  | Ben
                    | 2  | Jane

Is it possible to do with MySql query or should I sort the data by PHP ?

RoyBarOn
  • 919
  • 3
  • 24
  • 63

1 Answers1

0

I wouldn't say so from the given dataset, a multi table layout would make it a simple process, Users, Hobbies, UserHobbies

In Users you just need the user data including a unique id (user_id). In Hobbies you just need the hobby name and a unique id (hobby_id). In UserHobbies you want id, user_id, hobby_id.

With this set up every user has multiple rows in UserHobbies, one for each hobby. You get the user hobbies first, join users on user_id and hobbies on hobby_id.

This gives you a data set that you can then order as you with, so in this case, order by hobby id, or hobby_name if you want display alphabetically.

This should help you in the right direction to building database and subsequently your query.

By using multiple tables you also open up the option for including other aspects in a similar manner at a later date without any alteration of the data you currently hold.

CodingInTheUK
  • 930
  • 7
  • 16
  • Thanks, so if i understand you correctly , you suggest to create another 2 tables, Hobbies and UserHobbies which thier ids will be used as foriegn keys, so when i'll look for a user - then i'll join the 2 tables and get the id's of the hobbies - and then i will be able to present them....i was looking for easier way...but if i'll have not other choise then that's what i'll do - thanks. – RoyBarOn Aug 13 '19 at 22:40
  • to be fair it doesnt get much easier, unless you have a limited number of hobbies and wont be adding more. You could do it with two tables by putting user hobbies ids as a serialised list in a text field or some other database safe method. but then you would have to handle every hobbies field of every user, within your php, its often better to hand off the data gathering to the database. – CodingInTheUK Aug 14 '19 at 08:53