Title:Searching in MySQL using fulltext (php)
Author:Mark Kiehl
Category:MySQL
Searching in MySQL using fulltext

Modify your MySQL InnoDB table to include a fulltext index by running this SQL:
ALTER TABLE `content` ADD FULLTEXT(`title`,`author`);

// mysql_connect( server, username, password )
$DbLink = mysql_connect(db_host,dbuser,dbpswd) OR die("Unable to open the database, error: ".mysql_error());
// Select the database.
$DbResult = mysql_select_db(db, $DbLink) OR die("Db connect successful, but unable to select the database ".db.", error: ".mysql_error());

$search_terms = "php mark vba excel";
echo "Starting with: ".$search_terms."<br />";
// Filter out some characters (str_replace is easier to understand than regular expressions).
$search_terms = str_replace(","," ",$search_terms);
$search_terms = str_replace("<"," ",$search_terms);
$search_terms = str_replace(">"," ",$search_terms);
$search_terms = str_replace("("," ",$search_terms);
$search_terms = str_replace(")"," ",$search_terms);
$search_terms = str_replace("~"," ",$search_terms);
$search_terms = str_replace(" * "," ",$search_terms);
$search_terms = str_replace(" *"," ",$search_terms);
// Add double quotes around key phrases with less than 4 characters
$search_terms = str_replace('php','"php"',$search_terms);
$search_terms = str_replace('vba','"vba"',$search_terms);
$search_terms = str_replace('css','"css"',$search_terms);
$search_terms = StripExtraSpace($search_terms);
$against = $search_terms." IN BOOLEAN MODE";

$sql="SELECT COUNT(*) as `CountOfRecs` FROM `content` WHERE MATCH(`title`,`html`,`author`,`cat1`) AGAINST ('".$against."') ";
$result = mysql_query($sql,$DbLink) or die("db query error, ".mysql_error()."<br />".$sql);
$rec = mysql_fetch_array($result);
echo $rec['CountOfRecs'].' records found & sorted by relevance for <em>\''.$search_terms.'\'</em>:<br />';

$sql="SELECT *,MATCH(`title`,`html`,`author`,`cat1`) AGAINST ('".$against."') AS `score` FROM `content` WHERE MATCH(`title`,`html`,`author`,`cat1`) AGAINST ('".$against."') ORDER BY `score` DESC ";
$result = mysql_query($sql,$DbLink) or die("db query error, ".mysql_error()."<br />".$sql);
echo mysql_num_rows($result)." records found matching criteria ".$search_terms."<br/>";
echo '<table>';
while ($rec = mysql_fetch_array($result)) {
echo '<tr><td><strong>'.$rec['title'].'</strong><br /><br />';
$html = limit_text($rec['html'],20);
$html = htmlspecialchars($html,ENT_QUOTES,'UTF-8',false);
$html = nl2br($html);
echo '<p class="small">'.$html.'</p></td></tr>';
}
echo '</table>';