Ajax PHP MySQL Search Example

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?

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.

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.

 $searchModel = new SearchModel(); $row_count = $searchModel->getCount(); $limit = " limit " . $start . "," . Config::LIMIT_PER_PAGE; if (! empty($row_count)) < $per_page_html .= "
"; $page_count = ceil($row_count / Config::LIMIT_PER_PAGE); if ($page_count > 1) < for ($i = 1; $i '; > else < $per_page_html .= ''; > > > $per_page_html .= "
"; > $result = $searchModel->getAllPosts($start, Config::LIMIT_PER_PAGE); ?>       .img-url  
 
Search:    > ?>
 
 
Title
 
Description
 
Date
 
 
 
  
 
     

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.

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)

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.

 /* 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 = "$wordsAry[$i]"; $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']; >?>    > > ?> 

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.

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

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.

Comments to “Live search in PHP and MySQL with AJAX”

Hi, Thank you for sharing such a very quality article and more such amazing tutorials. Thank you Vincy.

Источник

Быстрый поиск с выводом результатов

Виджет на JQuery и AJAX, который покажет результаты поиска непосредственно при вводе в текстовое поле.

Форма

В обычную форму поиска добавляется пустой .

JQuery скрипт получает введенный в поле текст и отправляет его POST-запросом на обработчик /ajax_search.php , ответ выводится в всплывающей плашке.

$(document).ready(function() < var $result = $('#search_box-result'); $('#search').on('keyup', function()< var search = $(this).val(); if ((search != '') && (search.length >1))< $.ajax(< type: "POST", url: "/ajax_search.php", data: , success: function(msg) < $result.html(msg); if(msg != '')< $result.fadeIn(); >else < $result.fadeOut(100); >> >); > else < $result.html(''); $result.fadeOut(100); >>); $(document).on('click', function(e) < if (!$(e.target).closest('.search_box').length)< $result.html(''); $result.fadeOut(100); >>); $(document).on('click', '.search_result-name a', function()< $('#search').val($(this).text()); $result.fadeOut(100); return false; >); $(document).on('click', function(e) < if (!$(e.target).closest('.search_box').length)< $result.html(''); $result.fadeOut(100); >>); >);

Стили формы поиска и результатов:

.search_box < position: relative; >.search_box input[type="text"] < display: block; width: 100%; height: 35px; line-height: 35px; padding: 0; margin: 0; border: 1px solid #fd4836; outline: none; overflow: hidden; border-radius: 4px; background-color: rgb(255, 255, 255); text-indent: 15px; font-size: 14px; color: #222; >.search_box input[type="submit"] < display: inline-block; width: 17px; height: 17px; padding: 0; margin: 0; border: 0; outline: 0; overflow: hidden; text-indent: -999px; background: url(https://snipp.ru/demo/127/search.png) 0 0 no-repeat; position: absolute; top: 9px; right: 16px; >/* Стили для плашки с результатами */ .search_result

Источник

PHP: Динамический поиск с использованием PHP, MySQL и AJAX

Иногда нам нужно искать данные, не загружая всю страницу. Из этого туториала Вы узнаете, как реализовать поиск данных в реальном времени с помощью ajax, используя базу данных PHP mysql. Вы можете использовать бесплатный исходный код для поиска данных в реальном времени ajax с помощью jquery php mysql.

Создание динамического поиска с использованием PHP, MySQL и AJAX

Выполните следующие несколько шагов и легко реализуйте динамический поиск из базы данных в реальном времени на PHP, MySQL и jQuery:

1. Создайте файл подключения к базе данных.

На этом этапе вы создадите файл с именем db.php и обновите приведенный ниже код в своем файле.

Приведенный ниже код используется для создания подключения к базе данных MySQL в PHP. Когда мы вставляем данные формы в базу данных MySQL, мы подключаем туда этот файл:

2. Создайте поисковую форму ajax.

На этом этапе вам нужно создать форму поиска ajax и обновить приведенный ниже код в форме поиска.

       
 
 
Search for users
 
 
   

3. Создайте скрипт PHP для поиска в БД.

На этом этапе вам нужно создать одно имя файла ajax-db-search.php и обновить приведенный ниже код в своем файле.

Приведенный ниже код предназначен для поиска в таблице базы данных MySQL с использованием скрипта Ajax PHP:

%' LIMIT 100"; $result = mysqli_query($conn, $query); if (mysqli_num_rows($result) > 0) < while ($user = mysqli_fetch_array($result)) < echo $user['name']."
"; > > else < echo "
User not found.
 "; > > ?>

В этом руководстве вы узнали, как реализовать динамический поиск в реальном времени на PHP с таблицей базы данных MySQL с использованием кода PHP. Это очень простой и легкий пример динамического поиска ajax в базе данных MySQL с использованием PHP.

Источник