Highlight Keyword in Search Results with PHP and MySQL

Search functionality is very useful for the data list. It helps to find the relevant records quickly from the database. Search term highlighting feature makes the search functionality user-friendly. Highlight keyword helps the user to easily identify the more relevant records in search results.

In the keywords highlighting feature, the search terms are highlighted with the different background color or text color in the search results. In this tutorial, we will show you how to search in MySQL database and highlight keyword in search results with PHP.

Highlight Words in Text

The highlightWords() is a custom function that helps to highlight the words in the text.

• The PHP preg_replace() function is used to perform a regular expression search and add a element to each matched string.
• A class attribute is attached with the element which can be used to specify the highlight color with CSS.
• The highlightWords() function accepts two parameters,
  • $text – The content to search and replace.
  • $word – The string to search for.

// Highlight words in text
function highlightWords($text, $word){
    $text = preg_replace('#'. preg_quote($word) .'#i', '<span class="hlw">\\0</span>', $text);
    return $text;
}

In the example code, we will fetch the posts data from the database and listed on the web page. Based on the search, the records will be filtered and the search term will be highlighted in the search results.

Create Database Table

For this example, we will create a posts table with some basic fields in the MySQL database. The posts table holds the records which will be searched and highlighted.

CREATE TABLE `posts` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `content` text COLLATE utf8_unicode_ci NOT NULL,
 `created` datetime NOT NULL,
 `modified` datetime NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Database Configuration (dbConfig.php)

The following code is used to connect the database using PHP and MySQL. Specify the database host ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName) as per your database credentials.

<?php
// Database configuration
$dbHost     = "localhost";
$dbUsername = "root";
$dbPassword = "root";
$dbName     = "codexworld";

// Create database connection
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);

// Check connection
if ($db->connect_error) {
    die("Connection failed: " . $db->connect_error);
}

Highlight Keywords in Search Results

Initially, all the records are fetched from the database and listed on the web page. Based on the search keyword, the words are highlighted in the search list.

• An HTML form is provided to filter the records based on the keywords.
• When the user search records by the keyword,
  • The WHERE clause is used with LIKE operator to filter the records by search term.
  • Before displaying the search results, the highlightWords() function is used to add the highlighting class in the matched words.
• Once the keywords are wrapped with the highlighting element in the search result, the attached class is used to highlight the string with CSS.

<?php
// Include the database config file
require_once 'dbConfig.php';

// If the search form is submitted
$searchKeyword = $whrSQL = '';
if(isset($_POST['searchSubmit'])){
    $searchKeyword = $_POST['keyword'];
    if(!empty($searchKeyword)){
        // SQL query to filter records based on the search term
        $whrSQL = "WHERE (title LIKE '%".$searchKeyword."%' OR content LIKE '%".$searchKeyword."%')";
    }
}

// Get matched records from the database
$result = $db->query("SELECT * FROM posts $whrSQL ORDER BY id DESC");

// Highlight words in text
function highlightWords($text, $word){
    $text = preg_replace('#'. preg_quote($word) .'#i', '<span class="hlw">\\0</span>', $text);
    return $text;
}
?>

<!-- Search form -->
<form method="post">
    <div class="input-group">
        <input type="text" name="keyword" value="<?php echo $searchKeyword; ?>" placeholder="Search by keyword..." >
        <input type="submit" name="searchSubmit" value="Search">
    </div>
</form>

<!-- Search results -->
<?php
if($result->num_rows > 0){
    while($row = $result->fetch_assoc()){
        $title = !empty($searchKeyword)?highlightWords($row['title'], $searchKeyword):$row['title'];
        $contnet = !empty($searchKeyword)?highlightWords($row['content'], $searchKeyword):$row['content'];
?>
<div class="list-item">
    <h4><?php echo $title; ?></h4>
    <p><?php echo $contnet; ?></p>
</div>
<?php } }else{ ?>
<p>No post(s) found...</p>
<?php } ?>

Highlight Keywords without CSS

You can use inline CSS to highlight the words in the text without using a CSS class. In this case, the highlightWords() will be modified like the following.

function highlightWords($text, $word){
    $text = preg_replace('#'. preg_quote($word) .'#i', '<span style="background-color: #F9F902;">\\0</span>', $text);
    return $text;
}

PHP CRUD Operations with Search and Pagination

Conclusion

Our highlightWords() class will work the both text and HTML content. Using the example code, you can highlight keywords in the search results with HTML content. This search highlight functionality will be very useful for the list of the data management section. Also, you can easily enhance our highlight search keyword functionality using PHP and MySQL.