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
MYSQL Search ALL
Moderator: General Moderators
Re: MYSQL Search ALL
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
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
There are 10 types of people in this world, those who understand binary and those who don't
- aceconcepts
- DevNet Resident
- Posts: 1424
- Joined: Mon Feb 06, 2006 11:26 am
- Location: London
Re: MYSQL Search ALL
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
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
actually for my problem I just needed to use parenthesis as the order precendence was getting mixed up. all sorted 