Searching using multiple keywords in PHP/MySql

Question

I'm trying to search for people based on their skills by using different keywords. However, whenever I try to search, the results are narrowed down to match the exact keywords.

Example:

table for users:

ID           Name                  Skills
 1       Joe Smith         Cooking Dancing Painting 
 2       John Williams     Drawing Fishing
 3       Beth Gray         Cooking Swimming 
 4       Martin Torres     Dancing Repairing CPR
 5       Alice Lee         Cooking Drawing Welding

If you search using the word "Cooking", it will return 3 results which are 1,3 and 5 which is okay. However if you search using "Cooking Drawing", it will only return 1 result which is 5 who has both Cooking and Drawing. What I am trying to achieve is for it to return ALL the people who have at least 1 of the skills in the keywords, in this case, 1,2,3 and 5.

Thanks.

SELECT * FROM volunteerbio WHERE (volunteerSpecialSkills LIKE '%Cooking%' AND volunteerSpecialSkills LIKE '%Dancing%') — Programmer
– Programmer, Commented Feb 8, 2012 at 6:28

score 2 · Accepted Answer · 2012-02-08 06:38:08Z

2

You should use Mysql Fulltext search feature.

Here is query you can use in your code with full-text:

SELECT * FROM your_table_name
WHERE MATCH (yourcolumn1,yourcolumn2,...)
AGAINST ('your texts to search');

Note : You should apply fulltext index on columns in which you want to search.

You can make query with like with combination of AND OR Conditions :

SELECT * FROM table_name WHERE (skills LIKE '%Cooking%' AND skills LIKE '%Dancing%' ... )

OR

SELECT * FROM table_name WHERE (skills LIKE '%Cooking%' OR skills LIKE '%Dancing%' ... )

edited Feb 8, 2012 at 6:38

answered Feb 8, 2012 at 6:24

user319198

Sign up to request clarification or add additional context in comments.

1 Comment

Programmer Over a year ago

Worked great. I just had to convert my tables since I was using InnoDB.

Collectives™ on Stack Overflow

Searching using multiple keywords in PHP/MySql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related