CODEWITHSUNDEEP
mysqlsearchfull-text-search
VideoMan123
VideoMan123

Reputation: 87

Mysql full text search exclude results

i have a question i do a full text search on my site and want exclude some results here is the code i use

SELECT *, MATCH(p_title, p_desc) AGAINST ('".$string."') AS score 
FROM posts 
WHERE MATCH(p_title, p_desc) AGAINST ('".$string."') 
ORDER BY score DESC LIMIT 12

Now when I add for example WHERE id != 1 AND WHERE test != 1 to end of SELECT this does not work, i googled but didn't find anything related to this. Is this at all possible?

I found a way with php 

if($id != $list['id'] AND $list['test'] != 1) { 
// CODE
}

But this is not really what I want. Thanks for help

Upvotes: 1

Views: 1121

Answers (1)

Johan
Johan

Reputation: 76703

If you change

SELECT *, MATCH(p_title, p_desc) AGAINST ('".$string."') AS score     
FROM posts 
WHERE MATCH(p_title, p_desc) AGAINST ('".$string."') 
ORDER BY score DESC LIMIT 12

To:

SELECT *, MATCH(p_title, p_desc) AGAINST ('".$string."') AS score     
FROM posts 
WHERE MATCH(p_title, p_desc) AGAINST ('".$string."') 
  AND (id <> 1) AND (test <> 1)
ORDER BY score DESC LIMIT 12

It should work, of course you can't add an extra WHERE clause after the first one.
If you want to add extra items to filter on then you need to use AND's.

Also note that doing a SELECT * will slow your query way down. Better list only the fields that you want to list, that will make your query much faster.
Make sure you have an index on p_title and p_desc to keep the speed acceptable.

If you want the search to be smarter and also return items related to $string you can change the code to:

SELECT *, MATCH(p_title, p_desc) AGAINST ('".$string."') AS score     
FROM posts 
WHERE MATCH(p_title, p_desc) AGAINST ('".$string."' WITH QUERY EXPANSION) 
  AND (id <> 1) AND (test <> 1)
ORDER BY score DESC LIMIT 12

This will let MySQL do a second pass where terms in the found in the first pass are used to find extra items that do not match against $string, but that do match against the data found in the first pass.

See: http://dev.mysql.com/doc/refman/5.5/en/fulltext-query-expansion.html

Upvotes: 2

Related Questions