MySQL Search And Highlight Results With PHP

Reading Time: 2 minutes

Highlighting keywords in search results will help a user to identify proper results easily. You have probably seen some websites with search functionality that highlight the text what you are searching for.

In this article, we learn about how to search and highlighting result keywords with php.

first we will start with setting MySQL connection details for database.

<?php
error_reporting(0);
$servername = "localhost";
$username = "REPLACE";
$password = "REPLACE";
$dbname = "REPLACE";
$datatable = "search_and_highlight";

MySQL table is named as ‘search_and_highlight’ and it has only two columns – ID and content

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

In the ‘content’ field, the text which we want to search is located. Next step is to create a function which will highlight in text.

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
}

After, when we print the results after SQL SELECT query, we will use this function, which will put yellow background color. If you want to change the color, replace #FF0 color code.

<form action="search-and-highlight.php" method="get">
Search: <input type="text" name="findme" value="<?php echo
$_GET["findme"]; ?>" /><input type="submit" value="Search" />
<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>

Next, we will add an option which allow you to select all results to be shown or only these that match the search criteria. And at last, we will create the actual MySQL search and 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();
?>

Note: Form above you can see that if we search for a string , we use the highlight_word() function defined above

We wish this tutorial is helpful to you. Like it. Share it.

Leave a Reply