Page 1 of 1

MYSQL Search ALL

Posted: Tue Aug 04, 2009 12:58 pm
by craigy101
Hello I have a question regarding a PHP/MySQL search feature I am trying to implement

I'm implementing a search that will either search for ALL or ANY of the keywords the user specifies, I've created PHP code that generates the following MySQL query - (i've assumed the user has searched for two words 'facebook' and 'celebrity' and the columns that are being searched are 'keywords' and 'otherkeywords'.)

select * from articles where keywords like '%facebook%' || otherkeywords like '%facebook%' or keywords like '%celebrity%' || otherkeywords like '%celebrity%'

This works for the ANY option. When I tried creating one for the ALL keywords option, i first of all assumed it would be just a case of changing the OR to AND but this is not the case it would appear as it still produces the same results as the above query.

I've tried looking on various websites but can't find an answer to this. Does anybody know a query that will produce a match for all keywords (using the example above would be fine) thanks for any help

craig

Re: MYSQL Search ALL

Posted: Wed Aug 05, 2009 1:35 am
by VladSun
ANY == OR
so your ANY query is far simple than the one you wrote:
[sql]SELECT     * FROM     articles WHERE     keywords LIKE '%facebook%'     OR     keywords LIKE '%celebrity%'     OR     otherkeywords LIKE '%facebook%'     OR     otherkeywords LIKE '%celebrity%'[/sql]

ALL == AND
[sql]SELECT     * FROM     articles WHERE     keywords LIKE '%facebook%'     AND     keywords LIKE '%celebrity%'     AND     otherkeywords LIKE '%facebook%'     AND     otherkeywords LIKE '%celebrity%'[/sql]

I'm not sure whether ALL means that all of the keywords should present while it doesn't matter wichich column conatins them so your query may be this one:
[sql]SELECT     * FROM     articles WHERE     keywords LIKE '%facebook%'     AND    (         keywords LIKE '%celebrity%'     OR         otherkeywords LIKE '%celebrity%'     )    OR     otherkeywords LIKE '%facebook%'     AND    (         keywords LIKE '%celebrity%'     OR         otherkeywords LIKE '%celebrity%'     )[/sql]

While it "works" for searching two keywords it's getting more and more complex as the number of keywords to match grows.

Have you tried MySQL full search functions? http://devzone.zend.com/article/1304

Re: MYSQL Search ALL

Posted: Wed Aug 05, 2009 7:50 pm
by aceconcepts
As you're creating a search function you may want to look at full text search: http://dev.mysql.com/doc/refman/5.0/en/ ... earch.html

Re: MYSQL Search ALL

Posted: Thu Aug 06, 2009 9:47 am
by William
Although most people are unable to install software on their servers (shared hosts, etc), if you plan on doing any full text searches in MySQL it might be worth looking into http://www.sphinxsearch.com/ instead.

Re: MYSQL Search ALL

Posted: Sun Aug 09, 2009 6:04 pm
by craigy101
actually for my problem I just needed to use parenthesis as the order precendence was getting mixed up. all sorted ;)