[Resolved] full text search problem

jadeddog

hey there all.... ive been banging my head against the wall for about two hours on this one.... basically what im trying to do is grab certain records from my main table, then im inserting them into a temporary table which is exactly the same as the main table..... then im doing a full text search on this temp table, based on user input, sorting the results

now the original filter is working fine (the first part where i grab any records from the main table that match the $abuse_variable)... the insert into the temp table works fine as well, and i can see the entry(s) into the temp table if i dont bother trying to do a full text search on it

the problem lies with the full text search.... it never finds anything, even if i make sure that what im looking for exists in the temp table... it comes back with my "nothing found" error message... now the full text search works just fine if i use it on the main table, but as soon as i try and run it on the temp table, it comes back with nothing found

i hope that wasnt too confusing, here is my code, and i hope somebody can help me out on this one

<? 
include("other.inc");

$connection = mysql_connect($host,$user,$password)or die ("couldn’t connect to server");
$db = mysql_select_db($database,$connection)or die ("Couldn’t select database");


$userinput = "{$_POST['criteria']}";


if ($userinput == "") 
{
	include("ssm_banner.php");
	include("ssm_nav.php");

echo "<style type='text/css'>
<!--
.style3 {font-size: 12px}
-->
</style>";

echo "<table width ='75%' border='0' cellpadding='10'><tr><td width='5%'></td><td width='90%'>";
echo "<style type='text/css'>

.style1 {color: #FF0000}

</style>";	

echo "<b><span class='style1'>You did not enter anything to search for, please try again.<br>Click <a href='faq.php?page=directory'>here</a> for help with searches.</span></b>";
echo "<form name='search' action='abuse_search_redirect.php?page=directory' method='post'>";
	echo "Enter your search criteria:<br>";
	echo "<input type='text' name='criteria'><br>";
	echo "<input type='submit' name='search' value='Search'>";
echo "</form>";
echo '<p><a href="adv_search.php?page=directory">Advanced Search</a></p>';

echo "<p align='center'>&nbsp;</p><br>";
echo "<a href='admin.php?page=directory'>Admin Control</a>&nbsp;&nbsp;&nbsp;
		<a href='faq.php?page=directory'>FAQ/Help</a>&nbsp;&nbsp;&nbsp;<a href='contact_us.php?page=directory'>Contact Us</a>";
echo "<p>";

echo "</td><td width='5%'></td></tr></table>";	
exit;
}	
else
{
	$delete_query = "delete from houses_temp";
	$result_delete = mysql_query($delete_query) or die (mysql_error($connection));

$abuse_variable = "Abuse Manual";
$query_abuse_filter = "SELECT * from houses where grouping like '%$abuse_variable%'";
$query_abuse_filter .= " and altGrouping like '%$abuse_variable%'";

//below is the table layout for results 
$result_abuse_filter = mysql_query($query_abuse_filter) or die (mysql_error($connection));
while ($row = mysql_fetch_array($result_abuse_filter,MYSQL_ASSOC)) 
{
	extract($row);
	$name2 = addslashes($name);
	$address2 = addslashes($address);
	$postalCode2 = addslashes($postalCode);
	$phoneNum2 = addslashes($phoneNum);
	$altPhoneNum2 = addslashes($altPhoneNum);
	$description2 = addslashes($description);

	$add_record = "insert into houses_temp (name, address, city, province, postalCode, phoneNum, 
	altPhoneNum, description, grouping, latitude, longitude, altGrouping, website) values ('$name2', 
	'$address2', '$city', '$province', '$postalCode2', '$phoneNum2', '$altPhoneNum2', '$description2', 
	'$grouping', '$latitude', '$longitude', '$altGrouping', '$website')";
	$result_add = mysql_query($add_record) or die (mysql_error($connection));
}	

//search for the matching records from the abuse manual that match the user input 
$query = "SELECT *, MATCH(name, city, description, website, grouping, altGrouping) AGAINST('$userinput') AS score FROM houses_temp
WHERE MATCH(name, city, description, website, grouping, altGrouping) AGAINST('$userinput') ORDER BY score DESC"; 		

$delete_query = "delete from houses_temp";
$result_delete = mysql_query($delete_query) or die (mysql_error($connection));

//below is the table layout for results 
$result = mysql_query($query) or die (mysql_error($connection));
while ($row = mysql_fetch_array($result,MYSQL_ASSOC)) 
{
	extract($row);
	$name2 = addslashes($name);
	$address2 = addslashes($address);
	$postalCode2 = addslashes($postalCode);
	$phoneNum2 = addslashes($phoneNum);
	$altPhoneNum2 = addslashes($altPhoneNum);
	$description2 = addslashes($description);

	$add_record = "insert into houses_temp (name, address, city, province, postalCode, phoneNum, 
	altPhoneNum, description, grouping, latitude, longitude, altGrouping, website) values ('$name2', 
	'$address2', '$city', '$province', '$postalCode2', '$phoneNum2', '$altPhoneNum2', '$description2', 
	'$grouping', '$latitude', '$longitude', '$altGrouping', '$website')";
	$result_add = mysql_query($add_record) or die (mysql_error($connection));
}
header("Location: abuse_show_search.php?page=directory"); 
}


?>

jadeddog

still having problems with this... and i found out what the source of the problem is... its the full text search

i put in some echo statements to make sure things were going as they should... i echoed the $name2 variable that was getting written to houses_temp, and it was "abuse test"..... then i echoed the num_rows getting written to houses_temp, which came back as "1".... this is all before the full text search.... i then echoed the full text search itself and it was:
"SELECT , MATCH(name, city, description, website, grouping, altGrouping) AGAINST('test') AS score FROM houses_temp WHERE MATCH(name, city, description, website, grouping, altGrouping) AGAINST('test') ORDER BY score DESC"
so the full text search query looks fine, its searching the correct fields for the user input of "test".... it should* find a match since the $name2 variable that was written to houses_temp was "abuse test".... but when i echo the num_rows for the full text query i get "0".... so whats happening is that the full text is not finding any matches in the houses_temp table, even though there is one that would be an obvious match

further to this full test problem.... if i use the exact same query but change the table from "houses_temp" to my main table of "houses", it then finds the "abuse test" record just fine.... this leads me to believe that my houses_temp table has some problem with it.... but it looks exactly the same as my houses and i have rebuilt it a couple times now, as well as adding the "ALTER TABLE houses_temp ADD FULLTEXT(name, city, description, website, grouping, altGrouping);".... so the houses_temp table should be fine

i would really really appreciate any help on this one as it just doesnt seem to make sense

Roger_Ramjet

If I follow what you are saying then you have checked that there are records in the houses_temp table. Yes? I mean, if you just ran a select all it would return rows?

jadeddog

yeah there is a record in the houses_temp table (basically i only have one test record that meets the criteria to be put into the hosues_temp table)..... when i do a num_rows for the result_abuse_filter i get a return of 1 row (so the one test record is in the houses_temp, as it should be).... but then when it gets to the full text search, there is nothing found, even though the record "abuse test" obviously matches the search query

the only thing that i can see being wrong is the houses_temp table not being set up correctly for full text searching ..... but the table is exactly the same as the houses table (which full text searching works on), and i modified the hosues_temp table by entering "ALTER TABLE houses_temp ADD FULLTEXT(name, city, description, website, grouping, altGrouping);" so that name, city etc can be searched.... maybe im doing something wrong with the houses_temp table setup?

tsinka

Hi,

I think in this case the result set is empty because MySQL treats words that are present in at least 50% of the rows in a table as stop words and ignores them (in fulltext search queries). In your case the search word is present in all rows (if I'm right).

Thomas

jadeddog

hmm yeah i forgot about that... you might be right, im gonna do some testing and let you know... if it turns out to be the case, is there any way to set that number to a much higher number or none at all (ie. allow for the possibility that a word is in every record)

tsinka

I think you have to modify the sources and recompile MySQL in order to change the 50% threshold.

Alternatively, use a boolean full-text search.

Thomas

jadeddog

i added a bunch more (8 to be precise) records that meet the abuse_filter criteria... so the hosue_temp table now has 8 records in it when teh full search query runs, but the full text search still finds 0 matches ...... it must haev something to do withthe houses table, but what?

tsinka

If you have 9 records that meet the criteria then make sure that you have at least 11 records in the table that don't meet the criteria or you'll get an empty result set.

Thomas

jadeddog

no i mean i have xx records in the main table... from this main table, i have 9 records that im "filtering out"... i then copy these 9 records into the houses_temp table..... so the houses_temp table has 9 records in it at this point.... then i run my full text query searching for $userinput (of the 9 records in the hosues_temp table, 3 of them will match this $userinput, so less than 50%).... at this point i delete the current contents of the houses_temp table, and replace it with the results from teh full text query (should be 3).... and here is the problem, nothing gets added to the houses_temp table, because the full text search doesnt return any results, even though 3 of 9 records in the houses_temp table shoudl match

jadeddog

ok so i figured it out.... the problem was with the hosues_temp table, but not it the way i thought

i was copying the filtered results (the 9 records) to the houses_temp table, and then doing the full search on the houses_temp table, then finally writing to the same hosues_temp table, and that was the problem.... i just created another temp table hosues_temp_abuse and copied the filtered results to that one... then i did the full search on the new houes_temp_abuse table, and copied these full search results to the original hosues_temp table... and viola!

so there was a problem with doing everything in one table... once i had a different temp file to copy to i was ok.... thanks for all the help