Pagination can be easily integrated into a data list using jQuery, Ajax, PHP, and MySQL without page refresh. Ajax pagination is the most elegant option to make the data list user-friendly. In the first tutorial, we have shown how to integrate Ajax pagination with PHP in a web application. Now, we will increase Ajax Paging Script functionality with search and filter features.
Search is the most beneficial feature of the data management section. It helps to filter data from a large number of records quickly.
The user can find the same data and sort the results set by using the search functionality. In this tutorial, we will explain to you how to unify Ajax pagination with search and filter using PHP, jQuery, and MySQL.
In the example Ajax search script, we will add the pagination and search feature to the posts data list.
- Attach dynamic records to the database and list them on the webpage.
- Generate links and add pagination to the list of posts.
- Add search and filter options to the posts list.
- Permit the user to paginate and search records without page reload.
Before proceeding to build Ajax pagination and search in PHP, have a look at the file structure.
ajax_pagination_with_search_filter/ ├── dbConfig.php ├── index.php ├── getData.php ├── Pagination.class.php ├── js/ │ └── jquery.min.js └── css/ └── style.css
Pagination Library
Our custom pagination library helps you integrate Ajax pagination with search functionality using PHP. Ajax Pagination creates links to control the paging of the data list. The following minimum configuration is required to associate Ajax pagination with a search in the data list.
- baseURL – URL where the Ajax request will send to fetch the records from the database.
- totalRows – Total number of records.
- perPage – Record numbers to display on each page.
- currentPage – Current page number.
- contentDiv – HTML element ID where the Ajax response data will appear.
- link_func – Function name that handles search functionality.
<?php /** * CodexWorld is a programming blog. Our mission is to provide the best online resources on programming and web development. * * This Pagination class helps to integrate ajax pagination in PHP. * * @class Pagination * @author Allsweb * @link http://www.Allsweb.com * @contact http://www.Allsweb.com/contact-us * @version 1.0 */ class Pagination{ var $baseURL = ''; var $totalRows = ''; var $perPage = 10; var $numLinks = 3; var $currentPage = 0; var $firstLink = '‹ First'; var $nextLink = '>'; var $prevLink = '<'; var $lastLink = 'Last ›'; var $fullTagOpen = '<div class="pagination">'; var $fullTagClose = '</div>'; var $firstTagOpen = ''; var $firstTagClose = ' '; var $lastTagOpen = ' '; var $lastTagClose = ''; var $curTagOpen = ' <b>'; var $curTagClose = '</b>'; var $nextTagOpen = ' '; var $nextTagClose = ' '; var $prevTagOpen = ' '; var $prevTagClose = ''; var $numTagOpen = ' '; var $numTagClose = ''; var $anchorClass = ''; var $showCount = true; var $currentOffset = 0; var $contentDiv = ''; var $additionalParam= ''; var $link_func = ''; function __construct($params = array()){ if (count($params) > 0){ $this->initialize($params); } if ($this->anchorClass != ''){ $this->anchorClass = 'class="'.$this->anchorClass.'" '; } } function initialize($params = array()){ if (count($params) > 0){ foreach ($params as $key => $val){ if (isset($this->$key)){ $this->$key = $val; } } } } /** * Generate the pagination links */ function createLinks(){ // If total number of rows is zero, do not need to continue if ($this->totalRows == 0 OR $this->perPage == 0){ return ''; } // Calculate the total number of pages $numPages = ceil($this->totalRows / $this->perPage); // Is there only one page? will not need to continue if ($numPages == 1){ if ($this->showCount){ $info = '<p>Showing : ' . $this->totalRows.'</p>'; return $info; }else{ return ''; } } // Determine the current page if ( ! is_numeric($this->currentPage)){ $this->currentPage = 0; } // Links content string variable $output = ''; // Showing links notification if ($this->showCount){ $currentOffset = $this->currentPage; $info = 'Showing ' . ( $currentOffset + 1 ) . ' to ' ; if( ($currentOffset + $this->perPage) < $this->totalRows) $info .= $currentOffset + $this->perPage; else $info .= $this->totalRows; $info .= ' of ' . $this->totalRows . ' | '; $output .= $info; } $this->numLinks = (int)$this->numLinks; // Is the page number beyond the result range? the last page will show if ($this->currentPage > $this->totalRows){ $this->currentPage = ($numPages - 1) * $this->perPage; } $uriPageNum = $this->currentPage; $this->currentPage = floor(($this->currentPage/$this->perPage) + 1); // Calculate the start and end numbers. $start = (($this->currentPage - $this->numLinks) > 0) ? $this->currentPage - ($this->numLinks - 1) : 1; $end = (($this->currentPage + $this->numLinks) < $numPages) ? $this->currentPage + $this->numLinks : $numPages; // Render the "First" link if ($this->currentPage > $this->numLinks){ $output .= $this->firstTagOpen . $this->getAJAXlink( '' , $this->firstLink) . $this->firstTagClose; } // Render the "previous" link if ($this->currentPage != 1){ $i = $uriPageNum - $this->perPage; if ($i == 0) $i = ''; $output .= $this->prevTagOpen . $this->getAJAXlink( $i, $this->prevLink ) . $this->prevTagClose; } // Write the digit links for ($loop = $start -1; $loop <= $end; $loop++){ $i = ($loop * $this->perPage) - $this->perPage; if ($i >= 0){ if ($this->currentPage == $loop){ $output .= $this->curTagOpen.$loop.$this->curTagClose; }else{ $n = ($i == 0) ? '' : $i; $output .= $this->numTagOpen . $this->getAJAXlink( $n, $loop ) . $this->numTagClose; } } } // Render the "next" link if ($this->currentPage < $numPages){ $output .= $this->nextTagOpen . $this->getAJAXlink( $this->currentPage * $this->perPage , $this->nextLink ) . $this->nextTagClose; } // Render the "Last" link if (($this->currentPage + $this->numLinks) < $numPages){ $i = (($numPages * $this->perPage) - $this->perPage); $output .= $this->lastTagOpen . $this->getAJAXlink( $i, $this->lastLink ) . $this->lastTagClose; } // Remove double slashes $output = preg_replace("#([^:])//+#", "\\1/", $output); // Add the wrapper HTML if exists $output = $this->fullTagOpen.$output.$this->fullTagClose; return $output; } function getAJAXlink( $count, $text) { if($this->link_func == '' && $this->contentDiv == '') return '<a href="'.$this->baseURL.'?'.$count.'"'.$this->anchorClass.'>'.$text.'</a>'; $pageCount = $count?$count:0; if(!empty($this->link_func)){ $linkClick = 'onclick="'.$this->link_func.'('.$pageCount.')"'; }else{ $this->additionalParam = "{'page' : $pageCount}"; $linkClick = "onclick=\"$.post('". $this->baseURL."', ". $this->additionalParam .", function(data){ $('#". $this->contentDiv . "').html(data); }); return false;\""; } return "<a href=\"javascript:void(0);\" " . $this->anchorClass . " ". $linkClick .">". $text .'</a>'; } } ?>
Make a Database Table
A table is required to store dynamic data in a database. The following SQL creates a POST table with some primary fields in the MySQL database.
CREATE TABLE `posts` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `created` datetime NOT NULL, `modified` datetime NOT NULL, `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1=Active | 0=Inactive', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Database Configuration (dbConfig.php)
There is a need to use the dbConfig.php file to add and select databases. Specify the database username ($ dbUsername), host ($ dbHost), password ($ dbPassword), and name ($ dbName) according to MySQL database server credentials.
<?php // Database configuration $dbHost = "localhost"; $dbUsername = "root"; $dbPassword = "root"; $dbName = "allsweb"; // Create database connection $db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName); // Check connection if ($db->connect_error) { die("Connection failed: " . $db->connect_error); }
Data List with Ajax Pagination and Search (index.php)
In this tutorial, There is a list of the initial posts data with pagination links, search input, and filter dropdown. Using pagination links, the user can quickly get the posts data from the MySQL database without page refresh using jQuery and Ajax. Therefore, the user can filter the post data using the search box and filter dropdown.
jQuery Library:
Include the jQuery library for the Ajax pagination.
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
Loading Image:
It is used to show/hide loading overlay when ajax request starts/completed.
<script> // Show loading overlay when ajax request starts $( document ).ajaxStart(function() { $('.loading-overlay').show(); }); // Hide loading overlay when ajax request completes $( document ).ajaxStop(function() { $('.loading-overlay').hide(); }); </script>
Search Function:
SearchFilter () is a custom JavaScript function that handles search and filter functionality using jQuery
- Firstly, get the search input and filter dropdown value.
- Secondly, send the search and filter request to the server-side script (getData.php) via Ajax.
- Link the filtered records to the HTML content of the posts list.
<script> function searchFilter(page_num) { page_num = page_num?page_num:0; var keywords = $('#keywords').val(); var sortBy = $('#sortBy').val(); $.ajax({ type: 'POST', url: 'getData.php', data:'page='+page_num+'&keywords='+keywords+'&sortBy='+sortBy, beforeSend: function () { $('.loading-overlay').show(); }, success: function (html) { $('#postContent').html(html); $('.loading-overlay').fadeOut("slow"); } }); } </script>
Search Form:
Describe the elements of HTML for the search input and sort by filter select box.
- Set the searchFilter() method with onkeyup/onchange event to trigger search option.
<div class="post-search-panel"> <input type="text" id="keywords" placeholder="Type keywords..." onkeyup="searchFilter();"/> <select id="sortBy" onchange="searchFilter();"> <option value="">Sort by Title</option> <option value="asc">Ascending</option> <option value="desc">Descending</option> </select> </div>
Add Pagination:
At first, retrieve a specific number of records from the database and listed with an Ajax pagination link. When you click a paging link and initiate Ajax request and sent to a server-side script.
- To display pagination links use createLinks() function of the Pagination class
<div class="post-wrapper"> <!-- Loading overlay --> <div class="loading-overlay"><div class="overlay-content">Loading...</div></div> <!-- Post list container --> <div id="postContent"> <?php // Include pagination library file include_once 'Pagination.class.php'; // Include database configuration file require_once 'dbConfig.php'; // Set some useful configuration $baseURL = 'getData.php'; $limit = 5; // Count of all records $query = $db->query("SELECT COUNT(*) as rowNum FROM posts"); $result = $query->fetch_assoc(); $rowCount= $result['rowNum']; // Initialize pagination class $pagConfig = array( 'baseURL' => $baseURL, 'totalRows' => $rowCount, 'perPage' => $limit, 'contentDiv' => 'postContent', 'link_func' => 'searchFilter' ); $pagination = new Pagination($pagConfig); // Fetch records based on the limit $query = $db->query("SELECT * FROM posts ORDER BY id DESC LIMIT $limit"); if($query->num_rows > 0){ ?> <!-- Display posts list --> <div class="post-list"> <?php while($row = $query->fetch_assoc()){ ?> <div class="list-item"><a href="#"><?php echo $row["title"]; ?></a></div> <?php } ?> </div> <!-- Display pagination links --> <?php echo $pagination->createLinks(); ?> <?php }else{ echo '<p>Post(s) not found...</p>'; } ?> </div> </div>
Search and Get Pagination Data (getData.php)
You can load the getData.php file by Ajax request (called from searchFilter() function) to retrieve the records from the database.
- On the search query and paging, limit attach the data based
- Render filtered the data list with pagination links.
- Return the HTML view of the posts list.
<?php if(isset($_POST['page'])){ // Include pagination library file include_once 'Pagination.class.php'; // Include database configuration file require_once 'dbConfig.php'; // Set some useful configuration $baseURL = 'getData.php'; $offset = !empty($_POST['page'])?$_POST['page']:0; $limit = 5; // Set conditions for search $whereSQL = $orderSQL = ''; if(!empty($_POST['keywords'])){ $whereSQL = "WHERE title LIKE '%".$_POST['keywords']."%'"; } if(!empty($_POST['sortBy'])){ $orderSQL = " ORDER BY title ".$_POST['sortBy']; }else{ $orderSQL = " ORDER BY title DESC "; } // Count of all records $query = $db->query("SELECT COUNT(*) as rowNum FROM posts ".$whereSQL.$orderSQL); $result = $query->fetch_assoc(); $rowCount= $result['rowNum']; // Initialize pagination class $pagConfig = array( 'baseURL' => $baseURL, 'totalRows' => $rowCount, 'perPage' => $limit, 'currentPage' => $offset, 'contentDiv' => 'postContent', 'link_func' => 'searchFilter' ); $pagination = new Pagination($pagConfig); // Fetch records based on the offset and limit $query = $db->query("SELECT * FROM posts $whereSQL $orderSQL LIMIT $offset,$limit"); if($query->num_rows > 0){ ?> <!-- Display posts list --> <div class="post-list"> <?php while($row = $query->fetch_assoc()){ ?> <div class="list-item"><a href="#"><?php echo $row["title"]; ?></a></div> <?php } ?> </div> <!-- Display pagination links --> <?php echo $pagination->createLinks(); ?> <?php }else{ echo '<p>Post(s) not found...</p>'; } } ?>
Conclusion
Firstly, we have tried to simplify the Ajax pagination integration process by the pagination library. Secondly, our pagination class gives you an easy way to implement Ajax pagination with search and filter in PHP using MySQL. Moreover, you can create paging links and add paging functionality without refreshing the page using jQuery, Ajax, and PHP. Besides, You can easily increase the functionality of Ajax Pagination Library to suit your needs.
Also, read our previous blog- Integrate new Google reCAPTCHA Checkbox with PHP