Click here to Skip to main content
Click here to Skip to main content
Go to top

Searching for words that sound like..

, 10 Jun 2009
Rate this:
Please Sign up or sign in to vote.
Combining methods to better search for words that sound like other words when searching a MySQL database.

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>" ;
    }
}

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Paul_O'Neil
Web Developer CodeLogic
United States United States
cheap, robust, and simple IT solutions

Comments and Discussions

 
GeneralGood Job Pinmemberkanu@adatapost10-Jun-09 20:30 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 10 Jun 2009
Article Copyright 2008 by Paul_O'Neil
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid