mySQL Select Statment to fix my problem

[luap599]

Hi
I have a mysql table name ( Discriptions ) that holds many normal text discriptions (doh'). How do I write a mysql query to search this table!
Example: the description 'red paint': If I enter 'paint red! My query will not find it!
Thanks for any help!
Paul

[darkagn]

The LIKE statement might prove useful.

SELECT * FROM Discriptions
WHERE Description LIKE '%paint%' or Description LIKE '%red%'

The above statement will find any description where one or both of the words paint or red appear anywhere in the string. The % is a wildcard character that represents any set of characters, another is the _ (underscore) character that is used to indicate any single character.

Example:

SELECT * FROM Discriptions
WHERE Description LIKE 'r_d'

will return any three character string it finds with r as the first character and d as the third character.

These are just suggestions, not sure if it will help but was the first thing I thought of when I read your post. Good luck :)

[Simon Tite]

You don't say which programming language you are using to compose your sql statement, but as a first shot I would suggest you program something like this:-

If the search term contains one word:

SELECT * FROM descriptions
WHERE desc LIKE '%firstword%'

If the search term contains two words:

SELECT * FROM descriptions
WHERE desc LIKE '%firstword%'
AND desc LIKE '%secondword%'

and so on, one extra AND clause for each word.

However, (although I've never tried this), there are features in mySql that allow you to create indexes on tables which support "natural language searching", so if you want a more sophisticated search ability check out this part of the mySql manual:

http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html

---
Sorry, posted this before I saw Darkagn's reply!