MySQL UNION query correct handling for 3 or more words

I've to ask开发者_开发问答 your help to solve this problem.

My website has a search field, let's say user writes in "Korg X 50"

In my database in table "products" i have a filed "name" that holds "X50" and a field "brand" that hold "Korg". Is there a way to use the UNION option to get the correct record ?

And if the user enters "Korg X-50" ?

Thank you very much !

Matteo

May be you should use full-text search

SELECT brand, name, MATCH (brand,name) AGAINST ('Korg X 50') AS score
FROM products WHERE MATCH (brand,name) AGAINST ('Korg X 50')

As far as I understand you don't need UNION but something like

SELECT * FROM table1 
WHERE CONCAT(field1, field2) LIKE '%your_string%' 

On client side you get rid of all characters (like space, hyphen, etc) in your_string that appears in user input and cannot be in field1 or field2.

So, user input Korg X 50 as well as Korg X-50 becomes KorgX50.

you will need to get some form of searchable text.

either parse out the input for multiple key words and match each separately, or perhaps try to append them all together and match to the columns appended in the same way.

you will also need either a regex, or maybe a simpler search and replace to get rid of spaces and dashes after the append before the comparison.

in general, allowing users to search for open ended text strings is more complicated than 'what union do i use'... you will ideally also be worried about slight misspellings and capitalization, and keyword order.

you may consider pulling all keywords out from your normal record into a separate keyword list associated with each product, then use that list to perform your searches.

If you do not want to parse user input and use as it is, then you will need to use a query like this

select * from products where concat_ws(' ',brand,name) = user_input -- or
select * from products where concat_ws(' ',brand,name) like %user_input%

However, this query won't return result if user enters name "Korg X-50" and your table contains "Korg" and "X50", then you need to do some other thing to achive this. You may look at http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_soundex however it won't be a complete solution. Look for text indexing libraries for that ex: lucene