MySQL search and highlight results with PHP

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.

<?php

error_reporting(0);

$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

</form>

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:

<?php

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

}

$conn->close();

?>

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.