PHP: How to use mysql fulltext search and handle fulltext search result

Question

Hello,

I have tried to use mysql fulltext search in my intranet. I wanted to use it to search in multiple tables, and get the independant results depending on tables in the result page.

This is what i did for searching.

$query = "
        SELECT *
        FROM testtable t1, testtable2 t2, testtable3 t3
        WHERE match(t1.firstName, t1.lastName, t1.details) against(' ".$value."')
        or match(t2.others, t2.information, t2.details) against(' ".$value."')
        or match(t3.other, t2.info, t2.details) against(' ".$value."')
        ";

$result = mysql_query($query)or die('query error'.mysql_error());

while($row = mysql_fetch_assoc($result)){
    echo $row['firstName'];
    echo $row['lastName'];
    echo $row['details'].'<br />';
}

Do you have any ideas about optimizing the query and format the output of search results?

Answer 1

You can not create a fulltext index on a several tables. So I find a fulltext index on each table and or clauses (just like you do) good enough.

However you can create a view (based on the three tables) and create the fulltext index against that view.

EDIT: Unfortunately you can not create an index on a view in Mysql