PHP / MySQL: highlight the results of the query "SOUNDS LIKE"

Question

Quick MYSQL / PHP question. I use a "not so strict" search query as a reserve, if the search results are not found with a regular search query:

foreach($find_array as $word) { clauses[] = "(firstname SOUNDS LIKE '$word%' OR lastname SOUNDS LIKE '$word%')"; } if (!empty($clauses)) $filter='('.implode(' AND ', $clauses).')'; $query = "SELECT * FROM table WHERE $filter"; 

Now I use PHP to highlight the results, for example:

 foreach ($find_array as $term_to_highlight){ foreach ($result as $key => $result_string){ $result[$key]=highlight_stuff($result_string, $term_to_highlight); } } 

But this method falls on his ass when I do not know what to highlight. Is there any way to find out what the "sound similarity" is when running this mysql query?

That is, if someone is looking for “Joan,” I want him to highlight “John” instead.

Answer 1

The SOUND LIKE clause simply compares the SOUNDEX key of both words, and you can use the PHP soundex () function to generate the same key.

So, if you find a suitable line and you need to find out which word to highlight, you can get both the first name and the last name, and then use PHP to find which one matches and highlight that particular word.

I made this code to try this. (To test my xD theory)

 <?php // A space seperated string of keywords, presumably from a search box somewhere. $search_string = 'John Doe'; // Create a data array to contain the keywords and their matches. // Keywords are grouped by their soundex keys. $data = array(); foreach(explode(' ', $search_string) as $_word) { $data[soundex($_word)]['keywords'][] = $_word; } // Execute a query to find all rows matching the soundex keys for the words. $soundex_list = "'". implode("','", array_keys($data)) ."'"; $sql = "SELECT id, firstname, lastname FROM sounds_like WHERE SOUNDEX(firstname) IN({$soundex_list}) OR SOUNDEX(lastname) IN({$soundex_list})"; $sql_result = $dbLink->query($sql); // Add the matches to their respective soundex key in the data array. // This checks which word matched, the first or last name, and tags // that word as the match so it can be highlighted later. if($sql_result) { while($_row = $sql_result->fetch_assoc()) { foreach($data as $_soundex => &$_elem) { if(soundex($_row['firstname']) == $_soundex) { $_row['matches'] = 'firstname'; $_elem['matches'][] = $_row; } else if(soundex($_row['lastname']) == $_soundex) { $_row['matches'] = 'lastname'; $_elem['matches'][] = $_row; } } } } // Print the results as a simple text list. header('content-type: text/plain'); echo "-- Possible results --\n"; foreach($data as $_group) { // Print the keywords for this group soundex key. $keyword_list = "'". implode("', '", $_group['keywords']) ."'"; echo "For keywords: {$keyword_list}\n"; // Print all the matches for this group, if any. if(isset($_group['matches']) && count($_group['matches']) > 0) { foreach($_group['matches'] as $_match) { // Highlight the matching word by encapsulatin it in dashes. if($_match['matches'] == 'firstname') { $_match['firstname'] = "-{$_match['firstname']}-"; } else { $_match['lastname'] = "-{$_match['lastname']}-"; } echo " #{$_match['id']}: {$_match['firstname']} {$_match['lastname']}\n"; } } else { echo " No matches.\n"; } } ?> 

A more generalized function, to pull the corresponding sound word from strings, might look like this:

 <?php /** * Attempts to find the first word in the $heystack that is a soundex * match for the $needle. */ function find_soundex_match($heystack, $needle) { $words = explode(' ', $heystack); $needle_soundex = soundex($needle); foreach($words as $_word) { if(soundex($_word) == $needle_soundex) { return $_word; } } return false; } ?> 

What, if I understand him correctly, can be used in your previously published code as:

 foreach ($find_array as $term_to_highlight){ foreach ($result as $key => $result_string){ $match_to_highlight = find_soundex_match($result_string, $term_to_highlight); $result[$key]=highlight_stuff($result_string, $match_to_highlight); } } 

This will not be as effective as the more focused code in the first fragment.

Answer 2

Please note that SOUNDS LIKE does not work as you think. It is not equivalent to LIKE in MySQL since it does not support the % pattern.

This means that your query will not find “John David” when searching for “John”. This may be acceptable if it is just your reserve, but it is not ideal.

So, here is another suggestion (which may require improvement); first use the PHPs soundex() function to find the soundex of the keyword you are looking for.

 $soundex = soundex($word); $soundexPrefix = substr($soundex, 0, 2); // first two characters of soundex $sql = "SELECT lastname, firstname ". "FROM table WHERE SOUNDEX(lastname) LIKE '$soundexPrefix%' ". "OR SOUNDEX(firstname) LIKE '$soundexPrefix%'"; 

Now you will have a list of firstnames and lastnames that have a vague similarity in sound (these can be many records, and you can increase the length of the soundex prefix that you use for your search). You can then calculate the Levenshtein distance between the soundex of each word and your search term and sort it.

Secondly, you should look at parameterized queries in MySQL to avoid SQL injection errors.