Multiple Searches - FullText Error

[Sops21]

I have a site that will have both and simple simple search and an advanced search feature. The simple search uses the following code:

 	$query="SELECT *, MATCH (description,specialties,name,street,city,zip,phone,dress,other) AGAINST ('$searchstring') AS 'score' FROM store WHERE MATCH(description,specialties,name,street,city,zip,phone,dress,other) AGAINST('$searchstring' IN BOOLEAN MODE)ORDER by score DESC ";

I made a fulltext index in phpmyadmin that includes those columns.

Now I want to make an advanced search. I used the following code to allow for this:

<?
$name=$_POST['name'];
$speciality=$_POST['speciality'];
$zip=$_POST['zip'];
$city=$_POST['city'];
$string = name; 
							$searchstring = $name;

						}
						else{
						}

						if ($speciality != null ){

						$string = $string . "," . specialties;
						$searchstring = $searchstring . " " . $speciality;

						}
						else {
						}


						if ($city != null ) {

						$string = $string . "," . city ;
						$searchstring = $searchstring . " " . $city ;

						}
						else {
						}

						if ($zip != null ) {
						$string = $string . "," . zip ;
						$searchstring = $searchstring . " " . $zip ;
						}

						else {
						}

						echo "$string <br>"; 
						echo "$searchstring";


						?>


						<? mysql_connect(localhost,$username,$password);
									@mysql_select_db($database) or die("Unable to Connect to database");

									$query="SELECT *, MATCH ($string) AGAINST ('$searchstring') AS 'score' FROM store WHERE MATCH($string) AGAINST('$searchstring' IN BOOLEAN MODE)ORDER by score DESC ";

Basically this just allows the search to know what columns to search in and what characters to search for.

The problem is that when I run this i get a "Can't find FULLTEXT index matching the column list" error because the fulltext index that i made in phpmyadmin for the simple search does not match the one that will be used in this advanced search.

Does what I'm trying to do make sense? Am i going about this the wrong way? Any suggestions?

[paulnaj]

Hi Sops,

There are quite a few potential errors in the code you posted (strings without quotes being the most common). I'm surprised that the program only coughs on the SQL!!

Have you got error_reporting turned on. If you haven't put the following line at the top of every script you want to debug:

<?php
error_reporting(E_ALL);
?>

Anyway, I think I understand what you're after ... something like ...

<?php
// These are the names of the fields that can be
// included in the search.
$searchable_fields = array('name', 'speciality', 'zip', 'city');

// Just some temp arrays ... converted to strings later
$search_fields = array();
$search_strings = array();

foreach($searchable_fields as $fn){
	if(isset($_POST[$fn]) && $_POST[$fn] != ''){
		// IF the fieldname (eg, speciality) has been sent and is not empty
		// THEN append the fieldname to the $search_fields array ...
		$search_fields[] = '`'.$fn.'`'; // Enclosed in back-ticks, not single-quotes
		// ... and append the value to the $search_strings array ...
		$search_strings[] = $_POST[$fn];
	}
}

if(count($search_fields) == 0){
	die('No search info sent!');
}

// I'm assuming all the connection stuff is good
mysql_connect('localhost', $username, $password);
@mysql_select_db($database) or die("Unable to Connect to database");

// Join up the arrays to use in the query
$search_fields = join(',', $search_fields);
$search_strings = join(' ', $search_strings);

// Note the use of back-ticks for table- and field-names in MySQL
$query = "
SELECT 	*
, 	MATCH (".$search_fields.") AGAINST ('".$search_strings."') AS `score` 
FROM `store` 
WHERE MATCH(".$search_fields.") AGAINST ('".$search_strings."' IN BOOLEAN MODE)
ORDER BY `score` DESC 
";

echo '<pre>'; var_dump($query); echo '</pre>';
?>

P.