Full Text search problem

[alco19357]

I have attached an image, please view it as it shows the visuals.

 

Basically, I have a search form that goes to a page which executes the commands.

 

I'm using mysql full text search in php and have set up the database with indexes (see jpeg for the visuals, it's more clear than me explaining)

 

When someone enters a keyword in the keyword field, marked by the name: $_POST[keywords]

and enters a city,state or zip and proximity, it calculates all the zipcodes in the proximity of the requested and forces them into an array. It then posts the keywords and array of zipcodes to a function. The function and its content is listed below:

 

<?php
function search_results($search_terms='', $city_state_zip=array()) {
$zipcode_arr = $city_state_zip;
$wherelocation = '(';
$number = 0;
foreach($zipcode_arr as $zc_k => $zc_v){
	if($number > 0) $wherelocation .= ' || ';
	$wherelocation .= "zip='".mysql_real_escape_string($zc_k)."'";
	$number++;
}
$wherelocation .= ')';

   $row2=0;
   $db = new mysql_database;
   $replace = array('', '', '', '');
   $search = array(',', ' ,', ', ', ' , ');
   $result = $db->select("SELECT *, MATCH (contact_name, business_name, phone_number) AGAINST ('".mysql_real_escape_string(str_replace($search, $replace, $search_terms))."') AS resultsInTurn FROM " . TABLE_CONTRACTOR_DETAILS . " WHERE MATCH (contact_name, business_name, phone_number) AGAINST('".mysql_real_escape_string(str_replace($search, $replace, $search_terms))."') ORDER BY resultsInTurn DESC");
   
   if($db->num_rows($result) > 0){ # results from deep search?>
			  <tr><td height="20"></td></tr>
			  <tr><td><hr />1 listing<hr /></td></tr>
			  <tr><td height="20"></td></tr>
  <? }else{
   	#no results
   }
   print_r($wherelocation);
   echo '<br>search term: ' . mysql_real_escape_string(str_replace($search, $replace, $search_terms));

}

 

The problem:

When i search something that's listed once (like joe smith2, joe smith1, or joe smith3), it returns 1 row, like it would be expected to do. when i search company2 (notice in the db in the picture you see 3 rows with company2, it doesn't return anything... you'd think it'd return 3 rows (what's the matter here!!!?? please help i'm puzzled)

 

anyone who can help will be my hero

 

Thank you,

alex

 

[attachment deleted by admin]

[fenway]

FTS acts funny on smaller datasts... how big is your table?

[alco19357]

FTS acts funny on smaller datasts... how big is your table?

 

what you see in the picture is the whole table lol. its currently in test mode. what is the number of rows needed before you start getting accurate results

[fenway]

There is no "fixed" number -- it depends on the distribution of the data.  The refman has more details.