sometimes mysql fulltext search doesn't return any results when it should be

Question

My MySQL database table contains 2510 records. When I try to search for a string in a column, using fulltext search, sometimes, I don't get any results. Just an empty html table.

For example, I'm searching for the author 'Peter Schmidt'. If I search for 'Peter' I'll get the right author, but if I search for 'Schmidt', the html table shows other authors, but not the right one. The author's column consist of 'surname, name' (Schmidt, Peter).

This is a piece of my code:

    $author = mysql_real_escape_string($_GET['author']);
    $sql = "SELECT * FROM books WHERE MATCH(author) AGAINST ('$author' IN BOOLEAN MODE)";
    $query = mysql_query($sql);
    if (!$query) {
        echo 'We cannot find the author you are searching for. Please try again.';
        echo '<a href="'.$_SERVER['PHP_SELF'].'" class="back" style="margin:0;" title="Go back">&raquo; Go back</a>';

    } else {
        echo '<p>These authors match your query:</p><table>'
        while ($result = mysql_fetch_array($query)) {
                echo '<tr><td>'.$result['author'].'</td></tr>';
        }
        echo '</table>'
    }

What causes this issue?

What happens when you type in `Schmidt,` into your search query? Wondering if the comma is getting in your way. — Crackertastic, Oct 07 '14 at 12:52
I think that the problem is the comma. Just try to add a * after the $autor: AGAINST ('$author*' IN BOOLEAN MODE) and see what happends — Monte, Oct 07 '14 at 12:54
Okay, next question is, does `Schmidt` appear in more than half of the records. I wouldn't imagine it does, but I have to ask. — Crackertastic, Oct 07 '14 at 12:54
I've already tried to use the like operator, but there's no difference. — Steven, Oct 07 '14 at 12:55
@Crackertastic No, it doesn't. Just a few authors have that surname. — Steven, Oct 07 '14 at 12:57
"author LIKE 'Schmidt%'" is not working? Well, probably there is another problem. — Monte, Oct 07 '14 at 12:57
Once try after using trim, I mean trim the get data and try. — Srikanta, Oct 07 '14 at 12:58
@Monte both 'like' or 'match-against' work. But they don't show me the right author. — Steven, Oct 07 '14 at 12:59
Design related question - Is there a reason that the surname and name fields are not separate fields? It would make life easier using the `FULLTEXT` indexes. — Crackertastic, Oct 07 '14 at 13:00
@Crackertastic I know, I got the MS access database from a relative and I had to transfer it to a mysql-database. — Steven, Oct 07 '14 at 13:03
Did Monte's suggestion of using the wildcard in your `AGAINST` work? — Crackertastic, Oct 07 '14 at 13:06
When you get the results for "Schmidt" search, there is no "Schmidt, Peter" in any of these? It's pretty strange. What if you search for "Schmidt, Peter"? — Monte, Oct 07 '14 at 13:07
@Crackertastic No, if I use '$author*', it still doesn't give me the right result. — Steven, Oct 07 '14 at 13:11
Print Schmidt from database and search input in hexadecimal. They might be different. — Marek, Oct 07 '14 at 13:12
@Monte No, there isn't. If i search for 'Schmidt, Peter', I still get an empty html table. — Steven, Oct 07 '14 at 13:12
try if [this](http://stackoverflow.com/questions/2962776/using-full-text-search-in-order-to-find-partial-words-sql-server-2008) might help you — , Oct 07 '14 at 13:19
@SaiPhani That doesn't work. If I try that, the query doesn't even work. — Steven, Oct 07 '14 at 13:24
I have a quick question: If you run `SELECT * FROM books WHERE MATCH(author) AGAINST ('+Peter' IN BOOLEAN MODE);`, does it find that record ? — RolandoMySQLDBA, Oct 07 '14 at 14:15

Jevgenijs Vaikulis · Accepted Answer · 2014-10-07T14:12:18.010

0

Try not to use BOOLEAN MODE matching if you dont know syntax for it. As also check for proper encoding in php/mysql connection:

<?php
mb_internal_encoding("UTF-8");
mysql_query("SET character_set_client = utf8;");
mysql_query("SET character_set_results = utf8;");
mysql_query("SET collation_connection = utf8_general_ci;");
$author = mysql_real_escape_string($_GET['author']);
$sql = "SELECT * FROM books WHERE author LIKE '%$author%'";
$query = mysql_query($sql);
if (!$query) {
    echo 'We cannot find the author you are searching for. Please try again.';
    echo '<a href="'.$_SERVER['PHP_SELF'].'" class="back" style="margin:0;" title="Go back">&raquo; Go back</a>';
} else {
    echo '<p>These authors match your query:</p><table>'
    while ($result = mysql_fetch_assoc($query)) {
            echo '<tr><td>'.$result['author'].'</td></tr>';
    }
    echo '</table>'
}
?>

edited Oct 07 '14 at 14:12

answered Oct 07 '14 at 13:41

Jevgenijs Vaikulis

676
4
7

I've tried to use the like operator, but it makes no difference. – Steven Oct 07 '14 at 13:47
Please dump table information with command: mysql> show table status where Name='table_name'; – Jevgenijs Vaikulis Oct 07 '14 at 13:51
So, collation for this table is utf8_general_ci. Please check you have defined php to use utf8 - add mb_internal_encoding("UTF-8"); somewhere in beggining. As also check mysql connection for proper collation. – Jevgenijs Vaikulis Oct 07 '14 at 14:03
Name: books, Engine: MyISAM, Version: 10, Row_format: Dynamic, Rows: 2510, Avg_row_length: 233, Data_length: 586680, Max_data_length: 281474976710655, Index_length: 194560, Data_free: 0, Auto_increment: 2512, Collation: utf8_general_ci – Steven Oct 07 '14 at 14:06

sometimes mysql fulltext search doesn't return any results when it should be

1 Answers1