Can't find FULLTEXT index matching the column list

andersstudio

Hi,

Even after reading the appropriate documentation, I still don't understand what creating a fulltext index does, why i'm supposed to do it, and how it affects my table...

My table is setup with the following columns: auto_id, name, parent, text

I wish to have a search box that will search my text column in the database for the keywords that were input. However, I'm getting errors (see below) with the following query:

$sql = "SELECT * FROM nav_and_content
		WHERE MATCH (name, text) AGAINST ('$keywords');";

the .php page:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/andersst/public_html/yumyumbakeshops/search.php on line 13

the query window in phpmyadmin:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$sql = "SELECT * FROM nav_and_content
WHERE MATCH (name,text) AGAINST ('donut' at line 1

What do I have to do in order to initiate the fulltext in my db? I'm afraid that if I run the command below the table will only contain the data that existed in the database at that time and will not be updated when new records are added. Is this true?:

ALTER TABLE nav_and_content ADD INDEX name_text (name, text)

In other words, if I add more records to the database, will the indexed table automatically update with new information?

Your help is much appreciated.

Thanks,

NogDog

The table must use the MyISAM engine, and the column being matched must have a FULLTEXT index defined for it:

ALTER TABLE nav_and_content ADD FULLTEXT INDEX name_text (name, text)

See http://dev.mysql.com/doc/refman/5.0/en/fulltext-restrictions.html for details on the restrictions for using fulltext searches.

andersstudio

Thanks for the help. I see that this did not add a new table. Rather, it modified the existing table type. So my other questions are answered. 🙂

Moving forward, I'm having difficulty with the string position of the keywords found in each record.

As my query loops through each record, my objective is to display a chunk of text containing the keyword, but with a certain amount of characters on either side of the keyword. I think I'm in good shape with how to code padding and pull substrings of the result.

However, my holdup is when multiple keywords are found in the same record. the $pos is therefore not declared and its throwing everything else off.

$pos = strpos($text, $keywords);

is there a way to STOP this function once the 1st keyword is found?

cbcjobsDOTcom

maybe try explode with the keyword as separator?

http://us3.php.net/explode

andersstudio

It looks like multiple keywords is not the problem because the error is inconsistent. It works for some records that have multiple keywords and fails for others that also have multiple keywords.

Thanks for mentioning explode. After modifying my code, I got the same errors until I tried to convert the $keywords and $text to lowercase. My code worked once I did this so I guess explode is case sensitive. The problem is that I am now forced to display all lowercase. Or, is there a way around this problem?

The code is below:


$keywords = strtolower($keywords);
$keyword_str_length = strlen($keywords); // get the length of the keyword	
// SET THE PADDING FOR THE NUMBER OF CHARACTERS TO SURROUND THE KEYWORD
$padding = 50;

 $text = strtolower($text);
// strip the html tags
$text = strip_tags($text);	

// SPLIT AT THE KEYWORD
$new_text = explode($keywords,$text, 2);

if(strlen($new_text[0]) < $padding){
	$start_string = $new_text[0]; // NOT ENOUGH PADDING IN FRONT OF THE KEYWORD, SO TAKE THE WHOLE THING
}
else{	
	// STARTING AT THE END OF THE STRING, KEEP ONLY X NUMBER OF CHRACTERS
	$new_front_string = substr($new_text[0], -$padding, $padding);

// SPLITTING MAY OCCUR IN THE MIDDLE OF A WORD, SO MOVE TO WHITE SPACE AND SPLIT AGAIN
$clean_start_string = explode(" ",$new_front_string, 2);
$start_string = "..." . $clean_start_string[1]; // GRAB THE 2ND ELEMENT WITH ALL THE TEXT	
}


// STARTING AT THE END OF THE KEYWORD, KEEP ONLY X NUMBER MORE OF CHRACTERS
$new_end_string = substr($new_text[1], 0, $padding);


// SPLITTING MAY OCCUR IN THE MIDDLE OF A WORD, SO MOVE TO WHITE SPACE AND SPLIT AGAIN
//$clean_end_string = explode(" ",$new_end_string, 2);
//$end_string = $clean_end_string[1]; // GRAB THE 2ND ELEMENT WITH ALL THE TEXT	
$end_string = $new_end_string;

$result_string = "$start_string $keywords $end_string ..."; // PUT IT BACK TOGETHER, BUT ADD KEYWORDS BACK IN SINCE THEY WERE REMOVED AT EXPLODE

$bolded_text = highlight($keywords, $result_string);

print("<p>$bolded_text</p>");

Also, the explode requires me to start at the beginning of the string to start searching for the delimiter. I really need to start at the back to look for the 2nd to last empty space to make sure that I didn't explode in the middle of a word. Is there a way to do this?

Thanks a lot for your help!