SQL Question - Query Returning No Results

A

Anonymous

Guest
Code: 
SELECT *,  MATCH(features) AGAINST('15 11' IN BOOLEAN MODE)  as relevance FROM products 
WHERE 
MATCH(features) AGAINST('15 11' IN BOOLEAN MODE) 
AND (price1 <= 9999 OR (saleprice <= 9999 AND sale_expiry > UNIX_TIMESTAMP())) 
AND (price1 >= 0 OR (saleprice >= 0 AND sale_expiry > UNIX_TIMESTAMP()))

I am trying this query to find a product with the features 15 and 11. The way I expect this query to work it would be returning results.

The features field containts a string of all the features for a specific product (e.g. '12 15' for MP3 and Camera).

There IS a fulltext index with the features and when the MATCH AGAINST part of the query is taken out there are plenty of results returned, so the problem is completely in the match against I assume.

I have done something like this before and it worked just right and relevance was returned with a score of how many features were found. For instance if the search string had 4 features in it, and a certain product had 3 of those, it would return "3" as relevance, just like it should.
 
Try change it to '+15 +11', so it can search for both features.
If your features field has a value (string) like '12 15', your query will fail 'cause neither 15 or 11 are equal to that.

Or just change your product 'ID' to samething like '12_15' and search for '15_11' !?
Hope you've understood the point.
 
Thanks, but I've got no luck with that. I think the + does the same thing as having a space anyway. I'm starting to think it's a problem with my data now, because I've just noticed check boxes which get checked if the feature is there on the product are now not working.

I don't think the data type or index of the DB should affect this though.
 
SELECT * FROM products WHERE MATCH (features) AGAINST('11')

Let me just check my knowledge - That query above,should return any products that contain the word '11'?
 
Well, I've figured it out.

Fulltext searching can only find words of 4 characters or more. I just clicked when I was looking at the SQL from another site I did with similar code and saw all the features with 4 digit id's.
 
You must log in or register to reply here.
Back
Top