Searching for words that sound like..

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"; // we only search for one word

$dblink = mysql_connect("localhost", "%user%", "%password%")  
 or die("connection was unsuccessful");
mysql_select_db("db_name", $dblink) 
 or die("MYSQL Database Error: " . mysql_error());

//We're only selecting one field 
$query = mysql_query("SELECT field_name FROM TABLE");

while($r = mysql_fetch_array($query))
{
    //parse the multi-word string returned
    $tok = strtok($r["field_name"], ", ");
    while($tok !== false)
    {
        //needed to strip a quote character that enclosed the string inside the field 
        similar_text(strtoupper($trimmed), trim(strtoupper($tok),'"'), $percentage);
        $percentage = number_format($percentage, 0);
        if($percentage >= 80) //define a threshold, in this case an 80 percent match
        {
            //prints the results to clearly indicated which terms meet the threshold
            $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);
    //echo key pair values
    foreach($results_record as $result => $percentage)
    {
        echo $result . " - " . $percentage."%<BR>" ;
    }
}