go top


  • Colection of 65 PHP scripts for $4.29 each

Get 65 PHP scripts in a bundle for $4.29 each!

View Offer

MySQL search and highlight results with PHP

Wednesday, 13th July, 2016  /  PHP Tutorials  / MySQL Tutorials  /  3 Comments
You've probably seen these fancy websites with search functionality that highlight the text string you are searching for. In this tutorial, you will learn how to search your MySQL database for a particular string, print all the results and highlight all strings within the text.

You can try this demo and search for a string found in any of the listed news. Upon search you will see the string highlighted within the results.

We will first start with setting MySQL connection details for your database.

$servername = "localhost";
$username = "REPLACE";
$password = "REPLACE";
$dbname = "REPLACE";
$datatable = "search_and_highlight";

Our MySQL table is named 'search_and_highlight' and has two columns only - ID and content.

CREATE TABLE `search_and_highlight` (
`id` int(11) NOT NULL,
`content` text NOT NULL

The text that we will search for is located in the 'content' field. Next, we will create a function which will highlight the string we search for.

function highlight_word( $content, $word) {
$replace = '<span style="background-color: #FF0;">' . $word . '</span>'; // create replacement
$content = str_replace( $word, $replace, $content ); // replace content
return $content; // return highlighted data

Later, when we print the results after SQL SELECT query we will use this function, which will put all found strings within a span with an yellow background color. If you want to change the color, just replace #FF0 color code.

We will now create the search form:

<form action="search-and-highlight.php" method="get">
Search: <input type="text" name="findme" value="<?php echo $_GET["findme"]; ?>" /><input type="submit" value="Search" /><br />
<input name="show" type="radio" value="1"<?php if ($_GET["show"]=='1' or !isset($_GET["show"])) echo ' checked="checked"'; ?> />Show all news
<input name="show" type="radio" value="2"<?php if ($_GET["show"]=='2') echo ' checked="checked"'; ?> />Show news that match search criteria

We added an option allowing you to select if you want all results to be shown or only these that match the search criteria. In any case, strings found will be highlighted within the results.

And finally, we will create the actual MySQL search and will display the results:

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);

if ($_GET["show"]=='2') {
$sql = "SELECT * FROM ".$datatable." WHERE content LIKE '%".$conn->real_escape_string($_GET["findme"])."%'";
} else {
$sql = "SELECT * FROM ".$datatable;
$result = $conn->query($sql);

if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
if ($_GET["findme"]<>'') {
echo highlight_word($row["content"], $_GET["findme"]);
} else {
echo $row["content"];
echo "<hr>";
} else {
echo "0 results";

You can see above that if we search for a string instead of just printing the result, we use the highlight_word() function defined above. It will put all the instances of the string we search for in a highlighted span container.

You can download above code along with a sample database here. Demo is available here.

Have questions or comments? Please, use the form below.
Share on:

3 Comments to "MySQL search and highlight results with PHP"


Ibrahim / November 11, 2018 at 22:21 pm

This scripting actually solved my problem to certain extent. Could you, please, show me how to count the highlighted text in percentage % against the full text. Just as done in plagiarism web application


Cengiz / September 20, 2018 at 23:59 pm

How can we apply this application to multiple columns?

Mohamed Azarudheen

Mohamed Azarudheen / December 14, 2017 at 11:51 am

good application

Add your comment


    Please, be polite and helpful and do not spam or offend others! We promise you will be treated the same way!

    Log in to your account to post your comments. If you still haven't joined our community yet, you can create your FREE account now!

    Posting tip:
    If you use code in your comments, please put it in these tags [php], [sql], [css], [js] PHP code example: [php] echo date("Y-m-d"); [/php]

    Thank you,
    PHPJabbers Team

    Free Scripts

    Add great new functionalities to your website with our Free Scripts collection.

    Free scripts

    PHP Scripts

    Check our extensive collection of top-notch PHP Scripts that will enhance your website!

    Commercial PHP scripts