...

View Full Version : Search database for two words in a field?



Pennimus
09-02-2006, 11:05 PM
Is this possible?

Say the table is :


ID string
01 apple orange pear
02 apple banana
03 orange banana mango pear


And I want to return only rows containing both 'orange' and 'pear'. Looking at the standard list of operators (http://www.developerfusion.co.uk/show/3998/7/) I'm uncertain. Can you do something like this?



WHERE apple IN string && pear IN string

Mwnciau
09-02-2006, 11:35 PM
WHERE MATCH (string) AGAINST ('+orange +pear');

Fumigator
09-02-2006, 11:37 PM
You're getting the IN comparison mixed up. Using IN, you check to see if the column is equal to a value in the IN list:


WHERE string in('apple', 'banana', 'orange')

But the string must equal the value in the list.

You might want to try the LIKE function:


WHERE string like '%apple%'
AND string like '%banana%'

Performance will suffer depending on how huge your tables are.

Ideally you would use fulltext searching.

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

Fumigator
09-02-2006, 11:39 PM
Mwnciau's syntax is an example of fulltext searching :)

Make sure you create a fulltext index if you go that route.

mic2100
09-02-2006, 11:40 PM
i hope this get u on your way i think it should work




SELECT * FROM table WHERE string LIKE '%apple%' AND string LIKE '%pear%'

Lee Stevens
09-02-2006, 11:43 PM
mic2100's answer should be right or this:


SELECT * FROM table WHERE string {LIKE '%apple%' AND '%pear%'}

Fumigator
09-03-2006, 12:10 AM
SELECT * FROM table WHERE string {LIKE '%apple%' AND '%pear%'}


That won't work.

Pennimus
09-03-2006, 12:16 AM
Thanks guys. I will go with the FULLTEXT route - my understanding is that indexing slows down the insertion of data, not the extraction, which is fine by me.

Do my fields need to have both INDEX and FULLTEXTapplied to them, or just FULLTEXT?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum