使用PDO进行PHP全文搜索

Question

I have started working with MySQL Fulltext search with the php pdo. I have set a fulltext index like so:

CREATE FULLTEXT INDEX ft_title ON booklist (title); 

I also tried:

ALTER TABLE booklist ADD FULLTEXT ft_title (title);

And both seemed to have properly set the index.

I then queried the database as follows:

$stmt = $this->pdo1->prepare("SELECT * FROM booklist WHERE MATCH(title) AGAINST(:value)");

    try {
        $stmt->execute(array(':value' => $val));
    } catch (Exception $e) {
        return false;
    }

    $code = $stmt->fetchAll(PDO::FETCH_ASSOC);

No matter which value is given to $val, the resulting array always stays empty. No error messages are given. Also (obviously) many matching entries do exist in the database.

I am lost and would appreciate any help.

Results of "SHOW VARIABLES LIKE '%ft%'":

ft_boolean_syntax   + -><()~*:""&|
ft_max_word_len     84
ft_min_word_len     4
ft_query_expansion_limit    20
ft_stopword_file    (built-in)

Answer 1

How many records to you have in your table that match your search?

By default mysql full text search will not return any results if the matches appear in more than 50% of records.

This means if you have only one record in your table, or only 2 records, then no matter what you search for, you will always get no results.

To make it short, you need at least 3 records in your table and only one of those records must match your search criteria to get any results back.

This is a common situation when you just developing your project and have only 1 or 2 rows in a table, then trying a full test search does not return anything.