Fulltext search in MySQL and PHP doesn't work

Question

Hello,

I'm currently trying to perform a search over 2 fields in my MySQL table (text type) using PHP.

SELECT  * FROM content_items WHERE MATCH (content,name) AGAINST ('".urldecode($_REQUEST['term'])."' IN BOOLEAN MODE)

I'm always getting zero results, no matter what I search for (even tried to make the query static and it still didn't work). I have Fulltext indexes on both fields.

What can cause this?

Thanks,

Roy

p.s

The search should fit any length of sting (even short ones)

Answer 1

Hi Roy,

The sql statement makes sense. I think the problem lies here:

urldecode($_REQUEST['term'])

There's a note in the PHP Manual regarding this

Warning

The superglobals $_GET and $_REQUEST are already decoded. Using urldecode() on an element in $_GET or $_REQUEST could have unexpected and dangerous results.

Cheers,

Mark

Answer 2

( assuming your query isn't throwing an error )

You might want to check the mysql configs related to full text searching. For example, by default terms under 4 characters won't matching anything

mysql> show variables like 'ft_%';
+--------------------------+----------------+
| Variable_name            | Value          |
+--------------------------+----------------+
| ft_boolean_syntax        | + -><()~*:""&| |
| ft_max_word_len          | 84             |
| ft_min_word_len          | 4              |
| ft_query_expansion_limit | 20             |
| ft_stopword_file         | (built-in)     |
+--------------------------+----------------+
5 rows in set (0.00 sec)

Answer 3

Hi,

It was a problem with the indexes. Rebuilding them solved this.

Thanks,

Roy