MYSQL Search ALL

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
craigy101
Forum Newbie
Posts: 6
Joined: Thu Jul 30, 2009 12:50 pm

MYSQL Search ALL

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MYSQL Search ALL

Post 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
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: MYSQL Search ALL

Post 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
User avatar
William
Forum Contributor
Posts: 332
Joined: Sat Oct 25, 2003 4:03 am
Location: New York City

Re: MYSQL Search ALL

Post 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.
craigy101
Forum Newbie
Posts: 6
Joined: Thu Jul 30, 2009 12:50 pm

Re: MYSQL Search ALL

Post by craigy101 »

actually for my problem I just needed to use parenthesis as the order precendence was getting mixed up. all sorted ;)
Post Reply