up vote 0 down vote favorite

My users put search terms in an input field. The script I'm using stores that in a $find variable. It then searches mysql like so:

"select * from mytable WHERE title LIKE '%$find%' or description LIKE '%$find%'";

I want to be able to pull a result, even if 1 word is in the title and another in the description, which currently doesn't happen. So.. I'm guessing I need to break the $find variable into an array with a function. After that, how would I do the search in mysql? Since I never know how many words will be in the search (they might decide to search for 8 words at once), how do I reference the array in the mysql query?

Thx in advance!

share|improve this question
If $find is a long string (i.e. multiple words), the query will only find results that have the exact same string as is. You need to separate the words and append each one to the query, or use MySQL Full-text search feature. – Aziz Apr 28 '12 at 21:06

1 Answer

up vote 1 down vote accepted

You should use a FULLTEXT index and build a proper boolean search query on it.

You MAY achieve the effect you want without it, but this would be very slow compared to using FULLTEXT index. You would have to use explode to get the list of words, then build a WHERE condition like concat(title, description) LIKE '%word1%' AND concat(title, description) LIKE '%word2%' etc.

(In my first answer I stated that achieving this effect is impossible without FULLTEXT. I was wrong and edited this answer.)

share|improve this answer
Kind of surprises me on one hand.. I was thinking I could just bust it up with a str_split or something and then refer to the result of that for the search, but.. if that's not doable, no biggie. As for the FULLTEXT index.. thank you! I didn't know about that but just Google it and it's exactly the solution I was looking for. Looks easy enough to create. Many thanks! – Jeff Apr 28 '12 at 21:15
2  
I agree FULLTEXT is the way to go, but it's not true that all possible permutations would need to be included. One could just do WHERE title LIKE '%word1%' AND title LIKE '%word2%' ... AND title LIKE '%wordn%'. Very simple to build from php with explode/implode. – bfavaretto Apr 28 '12 at 21:16
@bfavaretto - you're right, the hours is too late for me! However one more comment: you would have to use concat(title, description) like '%word1%' etc. (since there are multiple columns to check). I will edit my answer. – wrygiel Apr 28 '12 at 21:49

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.