Why won't this SQL statement work?

durin

It seems my sql searches return random results, and my AND statements are not working, for example:

The following returns a row with:

Title = Nation's birth has local ties

SELECT ID, DATE_FORMAT(Date, "%b %D %Y"), Title, Abstract FROM test.AskSam 
WHERE 
(match (Title) against ('birth')) OR 
(match (Author) against ('birth')) OR 
(match (Keywords) against ('birth')) OR 
(match (Abstract) against ('birth')) OR 
(match (Page) against ('birth')) 
ORDER BY Date DESC

however, when i try to search for birth and local it doesn't return anything with the following:

SELECT ID, DATE_FORMAT(Date, "%b %D %Y"), Title, Abstract FROM test.AskSam 
WHERE 
(match (Title) against ('local') AND match (Title) against ('birth')) OR 
(match (Author) against ('local') AND match (Author) against ('birth')) OR 
(match (Keywords) against ('local') AND match (Keywords) against ('birth')) OR 
(match (Abstract) against ('local') AND match (Abstract) against ('birth')) OR 
(match (Page) against ('local') AND match (Page) against ('birth')) 
ORDER BY Date DESC

am I doing something grossly wrong here, are the parenthesis not acceptable? any insights would be appreciated.

Kudose

Are you using variables? Maybe it needs to be $birth, etc...

durin

no, that is the actual sql statement after it has been ran through php.

durin

anyone?

bharper

Is this what you are after?

SELECT ID, DATE_FORMAT(Date, "%b %D %Y"), Title, Abstract FROM test.AskSam

WHERE

(match (Title) against ('local') OR
match (Author) against ('local') OR
match (Keywords) against ('local') OR
match (Abstract) against ('local') OR
match (Page) against ('local')) AND
(match (Title) against ('birth') OR
match (Author) against ('birth') OR
match (Keywords) against ('birth') OR
match (Abstract) against ('birth') OR
match (Page) against ('birth'))

ORDER BY Date DESC

durin

that doesn't work either bharper

tomhath

The "match" command isn't a string search, so what you describe could happen. Read what this says about "semantic value":

http://www.mysql.com/doc/en/Fulltext_Search.html