FULLTEXT Searching w/ Prepared Statements

I’m having trouble getting a query to return correct results and thought someone here may be able to help. I’m using FULLTEXT searching in MySQL.

This query returns the correct results:


$this->stmt = $this->_dbh->prepare("SELECT * FROM posts WHERE MATCH (post_title, post_description) AGAINST (:search_term));

But when I add an AND conditional, it fails:


$this->stmt = $this->_dbh->prepare("SELECT * FROM posts WHERE MATCH (post_title, post_description) AGAINST (:search_term) AND post_price < 6.00");

The post_price on the matching item is 5.25 BTW, so it should return that row, but it doesn’t… I don’t get any errors, just an empty result set. post_price is datatype DECIMAL. Thoughts on this?

Do a “SHOW CREATE TABLE” for the table concerned, what does it show for the for the engine bit?

I am completely unsure about what I’m telling you here, and I haven’t tested it, but I can imagine MySQL interprets your query differently than you would expect. Can you try adding parentheses to the query, like so:


SELECT
  * -- do you actually need all columns?
FROM
  posts
WHERE
  ( MATCH (post_title, post_description) AGAINST (:search_term) )
AND 
  ( post_price < 6.00 );

Again: I’m not sure how MySQL would parse your statement; I don’t use MySQL al lot these days and if I do, I never use fulltext search. Just a thought though, you might want to try it out.

see above

I’ve tried SELECT * FROM posts WHERE post_price <= ‘10’ w/ and w/o quotes by itself and it works, and I have tried the Match / Against by itself and it works but when I use AND to run them both, no matter what else I do I get zero results… I tried setting post_price to INT and float and decimal and still no luck.

This also returns 0 results:


$this->stmt = $this->_dbh->prepare("SELECT * FROM posts WHERE post_price < 40.00");

but there are 35 items that match that. post_price datatype is DECIMAL, am I missing something here?

I know, just default MySQL which is MyISAM right?

InnoDB and MyISAM aren’t database software, they are the “storage engines” used to store the data. See here for more info

What database/storage engine is the table using, ie MyISAM, InnoDB, etc?

Have you tried to enclose the fts condition in brackets ?

$this->stmt = $this->_dbh->prepare(“SELECT * FROM posts WHERE (MATCH (post_title, post_description) AGAINST :search_term) AND post_price < 6.00”);

Can you attempt to run that query directly against the database? What result does that give?

Yeah, I still get no results.