[RESOLVED] FULLTEXT search not working?

This has been confusing and frustrating me for quite some time and so I decided to seek help here. I’ve been following several FULLTEXT search tutorials such as: [url]http://devzone.zend.com/26/using-mysql-full-text-searching/[/url]

So for this case I have a table named ‘[COLOR=”Blue”][FONT=”Courier New”]pn_users[/FONT][/COLOR]‘ and two fields named ‘[COLOR=”Blue”][FONT=”Courier New”]fname[/FONT][/COLOR]‘ and ‘[COLOR=”Blue”][FONT=”Courier New”]lname[/FONT][/COLOR]‘(I have also been trying 4 fields but cut it down to 2 thinking that was the problem).

Both the fields being used are of the TEXT data type and I went in and executed the following SQL query for my database:

[CODE]
ALTER TABLE pn_users ADD FULLTEXT(fname, lname);
[/CODE]

This successfully set up a non-unique FULLTEXT key named [COLOR=”Blue”][FONT=”Courier New”]fname[/FONT][/COLOR] for my table [COLOR=”Blue”][FONT=”Courier New”]pn_users[/FONT][/COLOR].

Now the problem seems to come in when I want to use this FULLTEXT for a search query. Below is the code I am using to attempt to search with. An AJAX function sends a ‘search’ string to this php script.

[code=php]
<?php

$keyword = str_replace(“,”, “”, $_POST[‘search’]);
$search_results = ”;

include(“script_mysql.php”);
$sql = “SELECT *, MATCH(fname, lname) AGAINST(‘$keyword’) AS score FROM pn_users WHERE MATCH(fname, lname) AGAINST(‘$keyword’) ORDER BY score DESC”;
$result = MySQL_query($sql);

while($row = MySQL_fetch_array($result)) {
$search_results .= $row[‘fname’] . ‘ ‘ . $row[‘lname’] . ‘<br />’;
}

mysql_close($con);

if($search_results != ”) {
echo $search_results;
} else {
echo “No results found”;
}

?>
[/code]

The test query I’ve been using is searching for the value ‘roy'(I’ve also tried ‘Roy’ to eliminate any potential case-sensitive issues). My table contains only 1 user with the name ‘Roy Mustang’. If I only search for ‘roy’ the query always returns no results. If I search for ‘roy mustang’ I then of course recieve 1 result, being the correct row from my database.

Perhaps there was something I missed but as far as I can tell this just isn’t working like it should. I appreciate any help in advance.

Also in #PHP _↴