Sql Query Criteria

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
winsonlee
Forum Commoner
Posts: 76
Joined: Thu Dec 11, 2003 8:49 pm

Sql Query Criteria

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

what you want it fulltext searching
winsonlee
Forum Commoner
Posts: 76
Joined: Thu Dec 11, 2003 8:49 pm

Post 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 ?
winsonlee
Forum Commoner
Posts: 76
Joined: Thu Dec 11, 2003 8:49 pm

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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?
Post Reply