mysql &php搜索突出显示 [英] mysql & php search highlighting

问题描述

想知道是否有人可以帮助我朝正确的方向推动，我正在构建一个搜索功能(php 和 mysql)，它将显示搜索结果并突出显示用户搜索过的关键字.目前，我获取用户输入的搜索条件，并针对数据库查询该条件，该条件可以正常工作以获得所需的结果.我的问题是

Wondering if someone could help give me a push in the right direction, I am building a search function (php and mysql) which will display search results and highlights keywords that the user has searched for. at the moment I grab the search criteria that the user has entered and query that against the database which works fine to get the desired results. the problem I have is

$highlight = preg_replace("/".$_GET['criteria']."/", "<span class='highlight'>".$_GET['criteria']."</span>", $_row['name']); 

这只会突出显示一个词组，而不是单个关键字.因此，例如，如果文档名为Hello world"并且用户准确输入了此内容，则它会突出显示没有问题，但是如果用户输入world hello"，则不会突出显示任何内容.我认为采用搜索条件并使用爆炸并单独检查每个单词是个好主意，但这似乎也失败了.这是我的查询以及我如何显示结果

This will only highlight a phrase and not individual keywords. so for example if the document was called "Hello world" and the user typed this exactly it would highlight no problem however if the user typed "world hello" it will not highlight anything. I thought it would be a good idea to take the search criteria and use explode and check each word individually but this seems to fail as well. here is my query and how I am displaying results

    $sql = "SELECT *
                FROM uploaded_documents
                WHERE dept_cat = 'procedures'
                AND cat =:cat 
                AND keywords REGEXP :term ";
    $result->execute(array(':cat' => $_GET['category'],':term' => $_GET['criteria']));

 //display results
 while($row = $stmt->fetch()){
    $explode_criteria = explode(" ",$_GET['criteria']);             
    foreach($explode_criteria as $key){                             
        $highlight = preg_replace("/".$key."/", "<span class='highlight'>".$key."</span>", $row['name']); 

            echo '<td><a target="_blank" href="'.$row['url'].'">'.$highlight.'</a></td>';   
                    echo '<td>'.$row['version'].'</td>';
                    echo '<td>'.$row['cat'].'</td>';
                    echo '<td>'.$row['author'].'</td>'; 

                    echo '<td>'.$row['added'].'</td>';  
                    echo '<td>'.$row['auth_dept'].'</td>';  

                    echo '<td>';
    } 
}

为了篇幅，我在这里省略了代码并尽量保持最少，我一直试图将我的工作建立在以下帖子上

For the sake of length I have omitted code here and tried to keep it minimal, I have been trying to base my work on the following post

在 php/mysql 中突出显示搜索结果

我认为我的第一个问题是 while 循环中的 foreach 循环复制结果，但我想不出解决方法.

I think my first problem is the foreach loop in the while loop duplicating results but I cant think of a way around it.

提前致谢

推荐答案

在这段代码中:

//display results
while ($row = $stmt->fetch())
{
    $explode_criteria = explode(" ", $_GET['criteria']);
    foreach ($explode_criteria as $key)
    {
        $highlight = preg_replace("/" . $key . "/", "<span class='highlight'>" . $key . "</span>", $row['name']);

        echo '<td><a target="_blank" href="' . $row['url'] . '">' . $highlight . '</a></td>';
        echo '<td>' . $row['version'] . '</td>';
        echo '<td>' . $row['cat'] . '</td>';
        echo '<td>' . $row['author'] . '</td>';

        echo '<td>' . $row['added'] . '</td>';
        echo '<td>' . $row['auth_dept'] . '</td>';

        echo '<td>';
    }
}

循环不断地引用$row['name']，所以替换完成，但下一次循环发生时，它将替换原始未修改的上的下一个单词$row['name']

The loop is constantly referring to $row['name'], so the replacement is done, but the next time the loop happens it is replacing the next word on the original unmodified $row['name']

我认为这应该对您有所帮助:

I think this should help you:

//display results
while ($row = $stmt->fetch())
{
    $explode_criteria = explode(" ", $_GET['criteria']);
    $highlight = $row['name']; // capture $row['name'] here
    foreach ($explode_criteria as $key)
    {
        // escape the user input
        $key2 = preg_quote($key, '/');
        // keep affecting $highlight
        $highlight = preg_replace("/" . $key2 . "/", "<span class='highlight'>" . $key . "</span>", $highlight);

        echo '<td><a target="_blank" href="' . $row['url'] . '">' . $highlight . '</a></td>';
        echo '<td>' . $row['version'] . '</td>';
        echo '<td>' . $row['cat'] . '</td>';
        echo '<td>' . $row['author'] . '</td>';

        echo '<td>' . $row['added'] . '</td>';
        echo '<td>' . $row['auth_dept'] . '</td>';

        echo '<td>';
    }
}

这篇关于mysql &php搜索突出显示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！