Live search in PHP and MySQL with AJAX

Live search is a handy feature for filtering data that enriches the user experience. It helps to shortlist the result based on the criteria selected by the end-user.

Search feature implementation effort is based on the complexity of the search requirement of the application. Also, it varies depends on the application need and structure.

The below list of items shows the types of search mechanisms commonly used by the applications.

1. Single common filter to search multiple columns.
2. Dedicated column-wise search filters.
3. Range filters with a range of dates, performance, estimates and more.
4. Google-like advanced search filter.

In this modern web/mobile application era, applications’ enriched interface made the search easy for the end-user. The design enrichment increases the usability of the search with the features like auto-complete, keyword-highlight and more.

What is inside?

1. Popular live search plugins and libraries
2. About this example
3. Database script
4. Application configuration
5. Rendering data list from database using MySQL
6. jQuery AJAX script to request live search
7. Code mechanism for live search in PHP
8. Output: Live search in PHP

Popular live search plugins and libraries

There are popular plugins available to integrate a search feature for an application.

DataTables – jQuery Javascript library has a seamless search feature. Only thing is that this plugin not only provides the search but also many like sort, pagination, inline-editing.

In a previous article, I have created code to enable Datatables column search with server-side processing.

Another alternative solution to enable search is the AJAX-enabled live search library.

Having more choices online, my preference is to go with a custom code for most of the application functionalities. Since the libraries may turn to be overload, minimalism will save effort during the maintenance.

About this example

This is to create a live search in PHP to filter database results. It lets the users enter the keyword, based on which the search is going to happen.

This example will display a single input field above the list of data from the database. The keyword search is applied to all the database columns to find a match.

I thought it would be better to highlight the search keyword along with the filtered result. But, it’s a configurable feature only.

This live search example also displays paginated results. The search criteria will be kept alive during the pagination. The results count per page is from the application config.

It uses PDO to connect the MySQL database to fetch results during the search and the pagination.

The following screenshot shows the file structure of this example.

Database script

This database script includes the CREATE statement, PRIMARY KEY, AUTO-INCREMENT specification with the data dump.

Import this script before running this example. It helps to try the live search on the dynamic data.

CREATE TABLE IF NOT EXISTS `posts` (
`id` int(8) NOT NULL,
  `post_title` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `post_at` date DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `posts`
--

INSERT INTO `posts` (`id`, `post_title`, `description`, `post_at`) VALUES
(1, 'Plugin integration', 'Documentation to help to integrate plugins.', '2021-03-02'),
(2, 'Learning languages', 'Guide to get started learning new languages.', '2021-03-25'),
(3, 'Editor tools', 'Number of tools and utilities available in the editor.', '2021-03-25');

--
-- Indexes for dumped tables

ALTER TABLE `posts`
 ADD PRIMARY KEY (`id`);

ALTER TABLE `posts`
MODIFY `id` int(8) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=18;

Application configuration

This is a basic configuration created for this code. It has the constants to define the units per page and a directive to enable or disable an optional feature of the live search functionality.

This code sets the ENABLE_HIGHLIGHT value to boolean true. And so, it highlights the keywords among the filtered result.

lib/Config.php

<?php
namespace Phppot;

class Config
{

    const ENABLE_HIGHLIGHT = true;

    const LIMIT_PER_PAGE = 2;
}

Rendering data list from database using MySQL

This is a landing page that displays the dynamic data with a live search option.

It includes the PHP code to query the database and retrieve the results to be listed in a tabular format.

On submitting the search form, it calls AJAX to get the filtered result from the server.

It also includes a pagination option to move back and forth. The pagination request is also sent via the AJAX call to show a seamless data display.

index.php

<?php
namespace Phppot;

use Phppot\SearchModel;
require_once __DIR__ . "/Model/SearchModel.php";
require_once __DIR__ . '/lib/Config.php';

/* Pagination Code starts */
$per_page_html = '';
$page = 1;
$start = 0;
if (! empty($_POST["page"])) {
    $page = $_POST["page"];
    $start = ($page - 1) * Config::LIMIT_PER_PAGE;
}

$searchModel = new SearchModel();

$row_count = $searchModel->getCount();

$limit = " limit " . $start . "," . Config::LIMIT_PER_PAGE;

if (! empty($row_count)) {
    $per_page_html .= "<div style='text-align:center;margin:20px 0px;'>";
    $page_count = ceil($row_count / Config::LIMIT_PER_PAGE);

    if ($page_count > 1) {
        for ($i = 1; $i <= $page_count; $i ++) {
            if ($i == $page) {
                $per_page_html .= '<input type="button"  name="page" onclick="getResult(' . $i . ')" value="' . $i . '" class="btn-page current" />';
            } else {
                $per_page_html .= '<input type="button"  name="page" onclick="getResult(' . $i . ')" value="' . $i . '" class="btn-page" />';
            }
        }
    }
    $per_page_html .= "</div>";
}

$result = $searchModel->getAllPosts($start, Config::LIMIT_PER_PAGE);
?>
<html>
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link href="./assets/css/phppot-style.css" type="text/css"
	rel="stylesheet" />
<script src="./vendor/jquery/jquery-3.2.1.min.js"></script>
</head>
<style>
.img-url {
	background: url("demo-search-icon.png") no-repeat center right 7px;
}
</style>
<body>
	<form name='frmSearch' action='' method='post'
		onSubmit="submitSearch(event)">
		<div class="serach">
			<label>Search:</label> <input type='text' name='search'
				class="img-url" id='keyword' maxlength='25'>
		</div>
		<table class='tbl-qa' id="tutorial-body">
			<thead>
				<tr>
					<th class='table-header' width='20%'>Title</th>
					<th class='table-header' width='40%'>Description</th>
					<th class='table-header' width='20%'>Date</th>
				</tr>
			</thead>
			<tbody id='table-body'>
	<?php
if (! empty($result)) {
    foreach ($result as $row) {
        ?>
	  <tr class='table-row'>
					<td><?php echo $row['post_title']; ?></td>
					<td><?php echo $row['description']; ?></td>
					<td><?php echo $row['post_at'];?></td>
				</tr>
    <?php
    }
}
?>
  </tbody>
		</table>
<?php echo  $per_page_html;  ?>
<script src="./assets/js/search.js"></script>
	</form>
</body>
</html>

jQuery AJAX script to request live search

This section creates the AJAX request to get the search result by the entered keyword.

With this AJAX call, it passes the current page number and the search keyword.

In the success block, it receives the output response in a HTML format and updates the UI.

The getResult() function is called on the click event of the pagination links. The submitSearch() function is called on submit after entering the search keyword.

assets/js/search.js

function getResult(pageNumber) {
	var searchKey = $("#keyword").val();
	$.ajax({
		type : "POST",
		url : "ajax-endpoint/get-search-result.php",
		data : {
			page : pageNumber,
			search : searchKey
		},
		cache : false,
		success : function(response) {
			$("#table-body").html("");
			$("#table-body").html(response);
		}
	});
}

function submitSearch(event) {
	event.preventDefault();
	getResult(1);
}

Code mechanism for live search in PHP

These PHP code snippets will display the live search PHP AJAX endpoint, the model class and the DAO.

The get-search-result.php is the PHP-AJAX endpoint that receives the live search request param and processes it.

It invokes the SearchModel class method getAllPosts() to get the search result. It sends the search keyword and the pagination param along with the method call.

It includes a function called highlightKeywords() to highlight the searched keyword. This invocation happens based on the configuration status to enable or disable this feature.

ajax-endpoint/get-search-result.php

<?php
namespace Phppot;

use Phppot\SearchModel;
require_once __DIR__ . "/../Model/SearchModel.php";
require_once __DIR__ . '/../lib/Config.php';

$search_keyword = '';

if (! empty($_POST['search'])) {
    $search_keyword = $_POST['search'];
}

/* Pagination Code starts */
$page = 1;
$start = 0;
if (! empty($_POST["page"])) {
    $page = $_POST["page"];
    $start = ($page - 1) * Config::LIMIT_PER_PAGE;
}

$searchModel = new SearchModel();

$result = $searchModel->getAllPosts($start, Config::LIMIT_PER_PAGE, $search_keyword);

function highlightKeywords($text, $keyword)
{
    $wordsAry = explode(" ", $keyword);
    $wordsCount = count($wordsAry);

    for ($i = 0; $i < $wordsCount; $i ++) {
        $highlighted_text = "<span style='font-weight:bold;'>$wordsAry[$i]</span>";
        $text = str_ireplace($wordsAry[$i], $highlighted_text, $text);
    }

    return $text;
}
if (! empty($_POST["page"])) {
    if (! empty($result)) {
        foreach ($result as $row) {
            if (Config::ENABLE_HIGHLIGHT == true) {
                $post_title = highlightKeywords($row["post_title"], $_POST["search"]);
                $new_description = highlightKeywords($row["description"], $_POST["search"]);
                $post_at = highlightKeywords($row["post_at"], $_POST["search"]);
            } else {
                print $post_title = $row['post_title'];
                $new_description = $row['description'];
                $post_at = $row['post_at'];
            }
            ?>
<tr class='table-row'>
	<td><?php echo $post_title ?></td>
	<td><?php echo $new_description ?></td>
	<td><?php echo $post_at ?></td>
</tr>
<?php
        }
    }
}
?>

This PHP class includes DataSource to get the connection object. It prepares the query param to append conditions to get the search result.

It has two functions to get the count and the data results separately.

The getCount() function returns the total number of records in the database about to the search. It will help to create the pagination HTML links.

The getAllPosts() function receives the per-page limit and the live search keyword to query the database.

Model/SearchModel.php

<?php
namespace Phppot;

class SearchModel
{

    private $ds;

    private $perPage;

    function __construct()
    {
        require_once __DIR__ . './../lib/DataSource.php';
        $con = new DataSource();
        $this->ds = $con->getConnection();
    }

    function getCount($search_keyword = "")
    {
        if(!empty($search_keyword)) {
            $sql = 'SELECT * FROM posts WHERE post_title LIKE :keyword OR description LIKE :keyword OR post_at LIKE :keyword';
        } else {
            $sql = 'SELECT * FROM posts';
        }
        $pdo_statement = $this->ds->prepare($sql);
        if(!empty($search_keyword)) {
        $pdo_statement->bindValue(':keyword', '%' . $search_keyword . '%', \PDO::PARAM_STR);
        }
        $pdo_statement->execute();
        $row_count = $pdo_statement->rowCount();
        return $row_count;
    }

    function getAllPosts($start, $perPage, $search_keyword = "")
    {
        if(!empty($search_keyword)) {
            $sql = 'SELECT * FROM posts WHERE post_title LIKE :keyword OR description LIKE :keyword OR post_at LIKE :keyword ORDER BY id ASC LIMIT ' . $start . ',' . $perPage;
        } else {
            $sql = 'SELECT * FROM posts ORDER BY id ASC LIMIT ' . $start . ',' . $perPage;
        }
        $pdo_statement = $this->ds->prepare($sql);
        if(!empty($search_keyword)) {
            $pdo_statement->bindValue(':keyword', '%' . $search_keyword . '%', \PDO::PARAM_STR);
        }
        $pdo_statement->execute();
        $result = $pdo_statement->fetchAll();
        return $result;
    }
}

lib/DataSource.php

<?php
namespace Phppot;
class DataSource
{

    function getConnection()
    {
        $database_username = 'root';
        $database_password = 'test';

        $pdo_conn = new \PDO('mysql:host=localhost;dbname=blog_example', $database_username, $database_password);
        return $pdo_conn;
    }
}
?>

Output: Live search in PHP

The output screenshot shows the live search result with highlighted keywords.

It populates the searched keyword in the search input fields above the list table view.

Below the tabular data, it displays the clickable pagination links.

Conclusion

With the live search PHP code, we have seen how to create and enable the search feature in an application. It has a common search field to search multiple columns to find the match.

It configures the keyword highlighting feature to display the searched keyword in a bold font.

We have seen several code references for different types of search implementations from previous articles.

I hope you have a very good start to create your own code to implement the live search feature in PHP.

Download

Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.

↑ Back to Top

• PHP
  • Learn PHP
  • PHP Introduction
  • PHP Basics
  • PHP Shopping Cart
  • Payment Gateway
  • Contact Form
  • PHP Login Registration
  • Comments System
  • Types, Variables & Operators
  • PHP Strings
  • PHP Arrays
  • PHP Functions
  • PHP OOPS
  • Event Management System
  • PHP Mail
  • PHP Forms
  • Advanced
  • PHP AJAX
  • RESTful API
  • PHP Databases
  • PHP Sessions and Cookies
  • Error and Exception Handling
  • File Upload
  • File Import Export
  • Files and Directories
  • PHP Date Time
  • PHP XML
  • PHP CSV
  • PHP JSON
  • PHP Code Samples
    • URI Shortener PHP Script
    • Create Shortener to Convert Long URL to Short in JavaScript
    • How to Add CSRF Token to AJAX Request
    • PHP MySQL Date Range Search with jQuery DatePicker
    • Advanced Search using PHP
    • PHP CRUD with Search and Pagination
    • Highlighting Keywords in Search Results with PHP
    • PHP Pagination MySQL Database Example Script with Previous Next like Google
    • Command Line PHP
    • How to Create Custom Image File Explorer in PHP
    • Web Scraping with PHP - Tutorial to Scrape Web Pages
    • User Login Session Timeout Logout in PHP
    • PHP Change Password Script
    • Object-Oriented Poll System using PHP
    • PHP cURL
    • Extract Content using PHP and Preview like Facebook
    • Show Image Thumbnail by YouTube Video URL using PHP
    • Star Rating with PHP and jQuery AJAX
    • Simple HTML 5 star rating code
    • How to Crop Image using jQuery and PHP
    • How to Generate Initial Avatar Image from Username using PHP Dynamically
    • Live search in PHP and MySQL with AJAX
  • Library
  • PHP Freelancer
• jQuery
• JavaScript
• WordPress
• MySQL
• CSS

• Share
• Tweet
• Share

1. Simple PHP Shopping Cart
2. Stripe Payment Gateway Integration using PHP
3. User Registration in PHP with Login: Form with MySQL and Code Download
4. PHP Contact Form
5. How to Create Dynamic Stacked Bar, Doughnut and Pie charts in PHP with Chart.js