Live search in PHP and MySQL with AJAX

by Vincy. Last modified on March 9th, 2021.

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.

Live Search in PHP and MySQLl

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

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.

Live Search Files

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>

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 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.

Live Search by Keyword Output

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

Leave a Reply

Your email address will not be published. Required fields are marked *

↑ Back to Top