WHERE x LIKE'%$find%' - What if $find = 2 seperate values?

I am creating a search feature for a products catalog.

Here is the input code so far:

<form name="search" method="post" action="'.$PHP_SELF.'">
    <div>
    Search for: <input type="text" name="find" />
    <input type="hidden" name="searching" value="yes" />
    <input type="submit" name="search" value="Search" />
    </div>
</form>

Here is the query that queries the Mysql database for a match:

SELECT * FROM `products` WHERE upper(`desc`) LIKE'%$fin开发者_如何学JAVAd%'

This all works perfectly fine, when the user searches for a one word string.

What the problem is here, is that when a user searches for a two word string, no match is found as sometimes these strings relate to seperate features.

An example of this would be, the products catalog contains information about various chocolates:

You get bars of chocolates, boxes of chocolates, packets of chocolates, etc...

Now although some of the descriptions held in the field desc contain the words chocolate and bar, some contain variations, like "company bars 120g chocolates".

This causes a problem in the above code, because if a user searches "chocolate bar" unless the description in the desc field contains that variation explicitly, no matches will be returned.

What I would like to happen, is when a user searches "chocolate bar" any match containing the varitions "chocolate" and "bar" will be returned.

Does anyone have any input on how i would go about this task?

Possibily turn $find into an array and make " " a delimiter?

---

Maybe you could do something like this:

$keywords_array = explode(' ', $find);

$qry = "SELECT * FROM `products` WHERE `desc` LIKE '%" 
       . implode("%' OR `desc` LIKE '%", $keywords_array) 
       . "%'";

Output for $find = 'this':

SELECT * FROM products WHERE desc LIKE '%this%'

Output for $find = 'this and that':

SELECT * FROM products WHERE desc LIKE '%this%' OR desc LIKE '%and%' OR desc LIKE '%that%'

This sure is a very basic functionality, but you get the idea.

---

You should look into FULLTEXT INDEX Documentation

desc LIKE '%word%' is gonna get heavier and heavier. User can also provide 2 words or even more. Just break the query into more words and build a where clause covering all posibilities:

WHERE desc LIKE '%word1%' or desc LIKE '%word2%' OR  ....

Again this isn't very efficient or fast, FULLTEXT + MATCH AGAINST works better.

---

You would need to split the string into parts, and add them all to the where clause separately. ie:

.... WHERE `desc` LIKE '%bar%' OR `desc` LIKE '%chocolate%'

But note that this is likely to a somewhat slow query! Wildcard searches in SQL are slow anyway, and multiple wildcards will compound the problem.

You might be able to acheive quicker results using FULLTEXT. This requires additional database setup work, but more importantly it probably wouldn't be useful in the example you've given because FULLTEXT typically drops short words from the index. It's configurable, so you might be able to get it working, but the point of FULLTEXT is to build a search index for large blocks of text; it's not really intended for shorter strings.

More common solutions for this sort of problem are to use tagging, and search the tags instead of the strings. This would involve basically having a separate table with all the relevant keywords from each record, stored individually. This allows you to have very quick searching because you can get rid of the wildcards completely. You can even add additional synonym keywords that may not be in the actual text, which can help to make the searching much easier to use.

If your users really can't find what they want with the tagging solution, you can still give them an "advanced" search, which goes back to the wildcard solution. Just make sure they know it'll be slow.

---

What about drifting to regexp?

You can replace spaces with | in $find (or whatever input syntax you prefer) to simulate "OK":

"chocolate bar" => "chocolate|bar" (or even "\b(chocolate|bar)\b" if you need word boundaries)

or you can transform to following to simulate "AND"

"chocolate bar" => "chocolate.*bar"

and then execute:

WHERE desc REGEXP '$find'

Doublecheck if the performance is OK for you.

---

First problem is to split the search into single words. This can be easily done in PHP itself, but depending on your search you may also have enabled phrases, e.g. "this is a phrase" If not, it's easy to just split the search text by spaces.

For your second problem, both possible solutions have been posted already, either you can use ... LIKE 'chocolate% OR LIKE 'bar% (note: I only added the % after the word - I would avoid using it on both sides, except you want to find wordparts as as well, e.g. hocol must still match chocolade. This is very slow, as the index cannot be used!

The better solution, as mentioned would be a fulltext search, using MATCH()... AGAINST(), as this solution makes use of fulltext indexes and speeds up your search a lot!