Introduction
The PHP function similar_text
and other variants are useful when looking for similar words. When used in conjunction with MySQL, additional programming is necessary. The MySQL function soundex
is not sufficient.
Background
Recently, I needed to query a database and compare the returned results to a search term. If the fields in the table contained one word, this would be easy, but the fields being searched included many words as part of a name usually separated by spaces but sometimes with a comma character.
Using the code
This brief code snippet includes the main part that tokenized the returned results from the database, and compares each term using the similar_text
function. The code needs to be expanded to search for several words at a time.
$trimmed = "word_to_search";
$dblink = mysql_connect("localhost", "%user%", "%password%")
or die("connection was unsuccessful");
mysql_select_db("db_name", $dblink)
or die("MYSQL Database Error: " . mysql_error());
$query = mysql_query("SELECT field_name FROM TABLE");
while($r = mysql_fetch_array($query))
{
$tok = strtok($r["field_name"], ", ");
while($tok !== false)
{
similar_text(strtoupper($trimmed), trim(strtoupper($tok),'"'), $percentage);
$percentage = number_format($percentage, 0);
if($percentage >= 80)
{
$results_record["matched on:<font color=\"#ff0000\"> " .
trim(strtoupper($tok),'"') .
"</font> in string: <font color=\"#0000CC\"> " .
$r[field_name] . "</font>"] = $percentage;
}
$tok = strtok(", ");
}
}
if (!$results_record)
{
echo "nothing found";
}
else
{
asort($results_record, SORT_NUMERIC);
foreach($results_record as $result => $percentage)
{
echo $result . " - " . $percentage."%<BR>" ;
}
}