MySQL MATCH AGAINST query not returning certain requests

Question

I have a search form on my website that is suppose to search article titles in my table for matching words. It seems to be working for certain strings but not others and I'm not sure why. For instance if you search for "assassin's creed" the search function return the right rows from the table. But if you search for "the last of us", there are no results, even though there is an article title with that string in it.

Below is my PHP/SQL command. Is there anything I need to add/change? Do I need to run a FULLTEXT index on the table's column each time I run a query? This is the first time I've used MySQL's MATCH AGAINST functionality. Usually I would use LIKE but after doing a little reading, apparently MATCH AGAINST is a much better approach.

public function fetchSearchArticles($q, $category, $platform, $sort) {

    $q = addslashes($q);
    $category = ($category != 'all')? ' AND `cat_id` = '.$this->fetch_category_id($category).' ' : '';
    $platform = ($platform != 'all')? ' AND `'.str_replace('-', '_', $platform).'` = 1 ': '';
    $sort = ($sort != 'rel')? '`article_timestamp` '.strtoupper($sort).'' : "MATCH(`article_title`) AGAINST('$q')";

    $query = $this->db->prepare ("
             SELECT * 
             FROM articles 
             WHERE `article_is_published` = 1
             $platform
             $category
             AND MATCH(`article_title`) AGAINST('+$q' IN BOOLEAN MODE) 
             ORDER BY $sort
             LIMIT 20
            ");

    try{
        $query->execute();
        $articles = $query->fetchAll(PDO::FETCH_ASSOC);
        $query->closeCursor();
        return $articles;

    } catch(PDOException $e){
        die($e->getMessage());
    }
}

Side note: using PDO with `addslashes()` is like having a carbon powered Ferrari, but less polluting and maybe more dangerous. — Álvaro González, Aug 12 '14 at 08:31
Just [prepared statements](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — Álvaro González, Aug 12 '14 at 08:39

avisheks · Accepted Answer · 2014-08-12T08:23:07.797

1

I believe the reason its not working for "the last of us" because, they are all called stop words in context of FULL TEXT search.

You can verify the same or an alternative solution I could suggest you is to disable stopwords from fulltext. You just need to locate .cnf file and add this,

ft_stopword_file = ""

Restart mysql engine and rebuild indexes;

Hope that works

edited Aug 12 '14 at 08:23

answered Aug 12 '14 at 08:15

avisheks

1,136
9
27

Thank you @avisheks, your suggestion worked very well. Do I need to run the index rebuild every time someone searches? – Nick Law Aug 12 '14 at 08:40
1

@NicholasLaw - You need to rebuild indexes when you change indexing options. If you had to calculate indexes every time you run a query, indexes would be a useless tool. – Álvaro González Aug 12 '14 at 08:50

MySQL MATCH AGAINST query not returning certain requests

1 Answers1