Why is my MySQL Full Text search not working?

Question

I'm using MySQL FullText search and It works, but I can't get the following result.

Ex:

SELECT * FROM countries WHERE MATCH (name)
     AGAINST ("Chinese*" IN BOOLEAN MODE);

It should give me the country record "China", but it doesn't work, any advice to make this function better/work?

My Full Code:

 protected function fullTextWildcards($term)
    {

        // removing symbols used by MySQL
        $reservedSymbols = ['-', '+', '<', '>', '@', '(', ')', '~'];
        $term = str_replace($reservedSymbols, '', $term);

        $words = explode(' ', $term);

        foreach($words as $key => $word) {
            /*
             * applying + operator (required word) only big words
             * because smaller ones are not indexed by MySQL
             */

            if(strlen($word) >= 3) {

                $words[$key] = '' . $word . '*';
            }
        }

        $searchTerm = implode( ' ', $words);

        return $searchTerm;
    }

    /**
     * Scope a query that matches a full text search of term.
     *
     * @param \Illuminate\Database\Eloquent\Builder $query
     * @param string $term
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function scopeSearch($query, $term)
    {
        $columns = implode(',',$this->searchable);

        $query->whereRaw("MATCH ({$columns}) AGAINST (? IN BOOLEAN MODE)" , $this->fullTextWildcards($term));
        return $query;
    }

if search keyword is Chinese, `$this->fullTextWildcards($term)` return `Chinese*` ? — kenken9999, Aug 13 '18 at 17:03
why concat empty string in front of $word, $words[$key] = '' . $word . '*'; — kenken9999, Aug 13 '18 at 17:07
Can you try getting a SQL query as a string to run it on phpmyadmin? And share the query with us. — Ketan Yekale, Aug 13 '18 at 19:59
i think if not many records in the db, the query won't show any records match `China` — kenken9999, Aug 13 '18 at 21:02

score 0 · Answer 1 · answered Aug 14 '18 at 08:05

0

My Solution was breaking the words smaller:

if(strlen($word) >= 7){
                    $split_string = str_split($word, 4);

                    $join = implode( '*', $split_string);

                    $words[$key] = '' . $join . '*';
                }else{
                    $words[$key] = '' . $word . '*';
                }

answered Aug 14 '18 at 08:05

Pedro

1,459
6
22
40

str_split() will split into bytes, rather than characters when dealing with a multi-byte encoded string. if $word = '你好嗎你好嗎你好嗎', your code won't work – kenken9999 Aug 14 '18 at 08:34
i wont have chinese charecters, only one language, englishj – Pedro Aug 14 '18 at 08:35
hmm but how can you sure peoples won't input non-english in the search box – kenken9999 Aug 14 '18 at 08:38

Why is my MySQL Full Text search not working?

1 Answers1