Trying to do a php/mysql search and need to break string into array and search

Question

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!

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
– Aziz, Commented Apr 28, 2012 at 21:06

wrygiel · Accepted Answer · 2012-04-28 21:54:31Z

1

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

edited Apr 28, 2012 at 21:54

answered Apr 28, 2012 at 21:07

wrygiel

5,1703 gold badges27 silver badges29 bronze badges

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

3 Comments

Jeff Over a year ago

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!

bfavaretto Over a year ago

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.

wrygiel Over a year ago

@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.

Collectives™ on Stack Overflow

Trying to do a php/mysql search and need to break string into array and search

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related