Page 1 of 1

Sql Query Criteria

Posted: Sat May 12, 2007 7:42 pm
by winsonlee
input search string : "HP COMPUTERS"

I would like to make a search query to my database and return the search result if it mathes.
Currently it only returns the result when it matches the word "HP COMPUTERS" within the description.
What i want is it also returns the result if the field contains "HP personal COMPUTERS" or "COMPUTERS personal HP".
The sql bellow just doesnt fit the things I wanted.

SELECT product_name, description FROM product WHERE description LIKE '%HP COMPUTERS%'


Is it necessary that i need to use
SELECT product_name, description FROM product WHERE description LIKE '%HP%' AND description LIKE '%COMPUTERS%' ..... ?

If I have 10 words, that means I will be having 10 likes..

Is there a simpler way of doing this ?

It will be great if I can make the search query case not sensitive.

That means even if i type Hp Computers it will still get the result although the field name contains "HP personal Computers.

Posted: Sat May 12, 2007 7:58 pm
by John Cartwright
what you want it fulltext searching

Posted: Thu May 17, 2007 8:51 am
by winsonlee
SELECT *FROM product_development
WHERE MATCH (product_code, product_desc)
AGAINST ('maximate cordless drill')
LIMIT 0 , 30;

I tried using the above query but it returns me the result of fields that contains the word "cordless drill" regardless if it contains the word maximate.


how can i modify the above query so that the result will contains all the 3 words instead of either of the word ?

Posted: Thu May 17, 2007 8:59 am
by winsonlee
i found my answer at

http://dev.mysql.com/doc/refman/5.0/en/ ... olean.html

i can do it using the + operator

Posted: Thu May 17, 2007 6:54 pm
by RobertGonzalez
You realize your second post directly contradicts your first?
What i want is it also returns the result if the field contains "HP personal COMPUTERS" or "COMPUTERS personal HP".
The sql bellow just doesnt fit the things I wanted.
how can i modify the above query so that the result will contains all the 3 words instead of either of the word ?
Ultimately, what were you after?