How to identify stopwords before making mysql query

Question

I'm trying to set up a text search for a webapp. My mysql query is the following:

SELECT * FROM t1 WHERE MATCH (c1,c2,c3) AGAINST (:keyStr IN BOOLEAN MODE)

I want all words in :keyStr to be matched, so keyStr looks like:

:keyStr='+word[0]* +word[1]* +word[2]* + ... +word[n]*'

If any word[x] is a stopword or less than the min word length the query returns a null. I think the best solution is to remove the '+' from a stopword or remove the stopword entirely from :keyStr.

Is there any good ways to do this? Do I need to check if any word[x] is in stopwords_list before the query is made?

http://stackoverflow.com/questions/796688/how-to-reset-stop-words-in-mysql — aspiring, Feb 23 '13 at 18:35

Rob M. · Answer 1 · 2013-02-24T00:11:48.233

An easy way to accomplish this with javascript is:

var apart = "Some +example search that I made up".toLowerCase().replace(/[\+\-\?]*/g, '').split(' '),
    stop_words = ['the', 'that', 'a', 'example'],
    min_word_length = 1;

// filter the array to remove stop words
apart.filter( function( item ) {
    if ( item.length < min_word_length ) return false;
    return stop_words.indexOf( item ) === -1;
});

Edit: though in a pseudo-code sense, the above code would work, here is a PHP solution

$query = "This is my example search query";

$search = explode(' ', $query);

$stop_words = array('is', 'a', 'the');

$terms = array_filter($search, function($item){
    return !in_array($item, $stop_words);
});

$formatted = array_map(function($term){
    return '+'.mysql_escape_string($term).'*';
}, $terms);

// :keyStr = implode(' ', $formatted);

This would work. But, mysql is already checking every word against that stopword list and for min_word_length. Checking for stopwords on the client side seems like I'm doing the job twice. — Jose Calderon, Feb 23 '13 at 22:27

How to identify stopwords before making mysql query

1 Answers1