Using MySQL Full-Text search with PHP

[ratherblue]

Indexes:

http://www.seventhstorm.net/indices.jpg

The PHP:


$search_terms = mysql_real_escape_string($_POST['search_prospects']);

$query = "SELECT * FROM `SALES_prospects` WHERE MATCH(`notes`) AGAINST('$search_terms')";

$qry_result = mysql_query($query) or die(mysql_error());The error:

Can't find FULLTEXT index matching the column list

Any ideas? :)

[ratherblue]

nevermind, posted too soon.

Apparently you have to have ALL the columns that are set in the FULLTEXT in your query.

[ratherblue]

I'm no longer getting any SQL errors, but my query returns 0 rows ):



$query = "SELECT * FROM `SALES_prospects` WHERE MATCH(loc_name, loc_state, loc_city, loc_zip, loc_address, name, phone, email, status, notes) AGAINST('$search_terms' )";

[joran420]

what do you see if you echo that $query?

[ratherblue]

Actually the problem was that the search only supports strings with 4+ characters. It wasn't working before because i was searching states (two letters). Gotta change an option in the mysql :/

[teiz77]

you can also add a wildcard symbol to search strings shorter then 4 characters, this might help if you don't have access to the mysql server settings.