3 letter fulltext search

[deansatch]

I need to use a fulltext search but there are a couple of columns that will contain three letter words. My host won't lower the minimum letter length so I had to come up with a different solution.

 

Here it is:

 

$separate_words = explode(" ", $_POST['search']);//create an array of words

//run through the array and extract the 3 letter words
foreach($separate_words as $three_letters){
if(strlen($three_letters)==3){

//check the existence of the 3 letter words in the relevant columns then get their id's
$query = mysql_query("SELECT id FROM table_acts WHERE actname='$three_letters' OR acttype='$three_letters' OR musictype='$three_letters'");
while($row=mysql_fetch_array($query)){
$id .= $row['id'].',';
}
}
}
//fulltext search BUT also include results from matching id's too
$query = mysql_query("SELECT *, MATCH(actname, country, county, musictype, acttype, music) AGAINST('".$_POST['search']."') AS score FROM table_acts WHERE MATCH(actname, country, county, musictype, acttype, music) AGAINST('".$_POST['search']."') or id IN ($id 0) ORDER BY SCORE DESC")or die(mysql_error());


while($row = mysql_fetch_assoc($query)){

$actname = $row['actname'];
$country = $row['country'];
$county = $row['county'];
$musictype = $row['musictype'];
$acttype = $row['acttype'];
$music = $row['music'];

echo "<p>$actname - $country - $county - $musictype - $acttype - $music</p>";
}

 

It works fine, BUT, the relevance order is all messed up and seems to put the 3 letter candidates last.

 

Is this going to be too much load on the server as my member database grows? If not, how can I get the relevance order to work a bit better?

[deansatch]

anyone?

 

Also, the search returns nothing if I search for a word with an apostrophe in it(even though it is in the db) e.g. "let's" = no results but $actname = let's

 

I have added mysql_real_escape_string() but it made no difference.