PHP/MySQL:高亮显示"SOUNDS LIKE"查询结果 [英] PHP/MySQL: Highlight "SOUNDS LIKE" query results

问题描述

MYSQL/PHP快速问题.如果没有使用普通搜索查询找到任何结果，我将使用不太严格"搜索查询作为备用，

Quick MYSQL/PHP question. I'm using a "not-so-strict" search query as a fallback if no results are found with a normal search query, to the tune of:

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

现在，我正在使用PHP突出显示结果，例如:

Now, I'm using PHP to highlight the results, like:

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

但是，当我不知道要突出显示什么时，此方法就会陷入困境.运行该mysql查询时，有什么方法可以找出声音相似"的匹配项吗?

But this method falls on its ass when I don't know what to highlight. Is there any way to find out what the "sound-alike" match is when running that mysql query?

也就是说，如果有人搜索琼"，我希望它突出显示约翰".

That is to say, if someone searches for "Joan" I want it to highlight "John" instead.

推荐答案

SOUND LIKE条件仅比较两个单词的SOUNDEX键，您可以使用PHP soundex()函数生成相同的键.

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

因此，如果找到匹配的行并需要找出要突出显示的单词，则可以获取名字和姓氏，然后使用PHP查找匹配的单词并仅突出显示该单词.

So, if you found a matching row and needed to find out which word to highlight, you can fetch both the firstname and lastname, and then use PHP to find which one matches and highlight just that word.

我编写此代码只是为了进行尝试. (必须测试我的理论xD)

I made this code just to try this out. (Had to test my theory xD)

<?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's 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";
    }
}
?>

更通用的功能是从字符串中提取匹配的soundex单词，如下所示:

A more generalized function, to pull out the matching soundex word from a strings could look like:

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

如果我正确理解的话，可以在您以前发布的代码中使用:

Which, if I am understanding it correctly, could be used in your previously posted 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 wouldn't be as efficient tho, as the more targeted code in the first snippet.

这篇关于PHP/MySQL:高亮显示"SOUNDS LIKE"查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！