mysql search

[rajeesh_rsn]

hi i had a database table and one of its column is filled with hobbies of the users. ( eg: stampcollection, fishing, photography ). How to do a search in this column. I am using php. If visitor search by any hobby then need to show users in database who had that hobby. Some times a single person had more than one hobby ........

Thanks...

[blocblue]

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.

[johny_d]

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 ;)

[mwasif]

Take a look at MySQL FULLTEXT searching capability.