4
Contributors
3
Replies
4
Views
8 Years
Discussion Span
Last Post by mwasif
0

That should be simple enough...

If the hobbies field is a comma seperated list of hobbies, then you could use the following. The % are wildcards, hence in this case the hobby photograph would be found at any place in the string value (if it exists).

SELECT * 
FROM `users` 
WHERE `hobbies` LIKE '%photography%'

Hope this helps.

R.

1

You can do it like that, but that's not the optimised way to do it.
You have what is called multiple-to-multiple relationship (many users with many hobbies).
So the principle to connect one to the other is to have a table that defines each one (one table for users and one table for hobbies) and a third table to tie one to the other.
This would be like this:

Users_table:
user_id | username |....
1 | John | ...
2 | Bill | ...
3 | Kate | ...
...

Hobbies_table:
hobby_id | hobby_name
1 | stampcollection
2 | fishing
3 | photography
...

Users_to_hobbies_table
id | user_id | hobby_id
1 | 1 | 1
2 | 1 | 3
3 | 2 | 1
4 | 2 | 2
5 | 3 | 2
6 | 3 | 3
...

We only store the usernames and hobbies names once and than we only work with their IDs, which are very fast and very lightweight for the database to manage.

The third table tells us that:
John like stampcollection and photography
Bill likes stampcollection and fishing
Kate likes fishing and photography

Let's say you want to find users who like phishing, you would do a query like this:

SELECT u.* FROM users_table AS u, hobbies_table AS h, users_to_hobbies_table AS uh
WHERE h.hobby_name = 'photography' AND h.hobby_id = uh.hobby_id AND uh.user_id = u.user_id

Of course, for a 50 rows database is not a big deal, but imagine what would be the difference for 20,000 users and 30 hobbies for example ;)

Votes + Comments
newly designed table is 400MB smaller, and noticebly faster
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.