MySQL full-text searching and partial words

[[m] at]

I'm building a site which is supposed to have a pretty robust search built in. I'm having trouble getting the full-text search to recognize partial word matches. It's a music site, so the way I've coded the search is kinda strange.

This sets up the temporary table with the search results...

//create the temporary table
$sql = mysql_query("CREATE TEMPORARY TABLE SearchResult (id INT(6), artist varchar(255), title varchar(255), FULLTEXT (artist,title))");


This pulls up any items that might match the search terms, and inserts their title, as well as the artist's name, into the temporary search result table. There is an identical bunch of code that does the same thing for any artists that might match the search terms.

//inset data from item search
$sql = mysql_query("SELECT i.title, i.id, i.artist_id, a.artist, a.id as artist_id, MATCH (title) AGAINST ('%%$term%%') as relevance FROM items i, artists a WHERE MATCH (title) AGAINST ('%%$term%%') AND a.id = i.artist_id LIMIT 0 , 150");
print mysql_error();
while ($row = mysql_fetch_array($sql)) {
$id = $row['id'];
$artist = $row['artist'];
$title = $row['title'];
$tempsql = mysql_query("INSERT INTO SearchResult (id, artist, title) VALUES ('$id', '$artist', '$title')");
}


Finally, this searches the temporary table in order to get relevance rankings.

$sql = mysql_query("SELECT *, MATCH (artist, title) AGAINST ('%%$term%%') as relevance FROM SearchResult");


In searching, a search for "pavement" pulls up two items, but a search for "paveme" pulls up nothing. Also, interestingly enough, when I do run a successful search, every item returns a relevance of 0.

Is there something I can do to get wildcard matches with full-text searches? And if anyone happens to know why my relevance rankings aren't working that'd be cool as well.

Thanks guys. :thumbsup:

[raf]

why are you using a full-text search? looks to me like you need a standard keyword-matching ...