[SOLVED] Mysql FullText Search MATCH AGAINST Multiple words

[tontox5]

Hello guys,

 

Here is my code for searching my database, however it only returns the first word in the search box, and ignores the second. Could you help me modify my query so it will return results with BOTH words?

 

if (isset ($_REQUEST['search'])) {
$search=$_REQUEST['search'];
$sql="SELECT * FROM listings WHERE MATCH (headline) AGAINST ('%$search%') ORDER BY listing_id DESC LIMIT $start_here,10";
$sql2="SELECT * FROM listings WHERE MATCH (headline) AGAINST ('%$search%') ORDER BY listing_id DESC";
}

 

 

Thanks!

Nick

 

[fenway]

You don't need % with AGAINST

[tontox5]

I figured out how to use Multiple words but I am getting a results blackhole for some terms.

 

One of the headlines is

Ducati 848 in Pearl White (Year: 2007)

The fulltext search will find

Ducati Pearl White

Ducati White

Ducati 2007

2007

 

But NOT anything with 848.

 

Also, another headline is

2006 Yamaha R1

 

The search will work with

2006

2006 Yamaha

 

But not R1

 

I don't get it...

 

Here's my code.

 $boolean_query = "('";
  for($n=0;$n < $word_count;$n++){
    $boolean_query .= '+'.$search_words[$n].'* ';
  }
  $boolean_query .= "' IN BOOLEAN MODE)";
  
$sql="SELECT * FROM listings WHERE MATCH (headline) AGAINST" . $boolean_query .  "ORDER BY listing_id DESC LIMIT $start_here,10";

 

Anyone have any ideas?

 

[tontox5]

Figured it out. Its the ft_min_word_len in mysql variables.

 

I edited the my.cnf file in /etc/ and added the line

set-variable = ft_min_word_len=2

Saved, make sure the permissions are not root only read

 

Then stopped and restarted the mysql database with these commands

# /etc/init.d/mysqld stop

# /etc/init.d/mysqld start

 

Then ran the command...REPAIR TABLE tbl_name QUICK; to reset the indexes.

 

After that two and three letter strings were indexed perfectly.