deansatch Posted July 16, 2009 Share Posted July 16, 2009 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? Link to comment https://forums.phpfreaks.com/topic/166246-3-letter-fulltext-search/ Share on other sites More sharing options...
deansatch Posted July 17, 2009 Author Share Posted July 17, 2009 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. Link to comment https://forums.phpfreaks.com/topic/166246-3-letter-fulltext-search/#findComment-876922 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.