mysql & php search highlighting

Question

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']); 

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

highlighting search results in php/mysql

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

Thanks in advance

Answer 1

In this block of code:

//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>';
    }
}

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