Create an account


Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
[Tut] PHP Pagination Code with Search using AJAX

#1
PHP Pagination Code with Search using AJAX

<div style="margin: 5px 5% 10px 5%;"><img src="https://www.sickgaming.net/blog/wp-content/uploads/2022/03/php-pagination-code-with-search-using-ajax.jpg" width="180" height="228" title="" alt="" /></div><div><div class="modified-on" readability="7.0909090909091"> by <a href="https://phppot.com/about/">Vincy</a>. Last modified on March 17th, 2022.</div>
<p>Fetching and displaying a bulk of records on a single fetch is not a good practice. It will increase the server load and has more disadvantages.</p>
<p>Pagination will resolve this problem.</p>
<p>Most of the website shows links of numbers for pagination. There are different ways to add pagination. Some of them are listed below.</p>
<ol>
<li>Previous-next navigation controls.</li>
<li>Number links 1, 2, 3 … up to … total number of pages.</li>
<li>A restricted number of page links that are expandable on navigation.</li>
</ol>
<p>In this tutorial, we are going to see an <strong>example of a PHP pagination code with search sorting</strong>.</p>
<p>This example adds pagination to a list of records displayed in a tabular view. It uses jQuery AJAX to fetch records for each page from the database.</p>
<h2>What is pagination</h2>
<ul>
<li>The <strong>Pagination</strong> is a simple method to split voluminous data into pages. It prevents the disadvantages of displaying all records on a page.</li>
<li>It shows a limited number of records per page which lets a quick glance at the page result.</li>
<li>Paginated results require the start offset and per-page count to set the LIMIT to fetch.</li>
</ul>
<h2>Merits and demerits of pagination</h2>
<p>There are more merits of using PHP pagination. Some of them are listed below.</p>
<ul>
<li>It increases efficiency by loading the page content quickly.</li>
<li>It reduces server overload.</li>
<li>The pagination links will reveal the approximate data stuff of the application.</li>
<li>It will greatly improve site responses on displaying the media library with pagination.</li>
<li>It increases user experience in browsing the paginated results.</li>
</ul>
<p>Almost PHP pagination has no demerits, but it is not suitable in some scenarios. For example,</p>
<ul>
<li>It is skippable for applications displaying records that are countable by fingers.</li>
<li>Without relational attributes (like prev, next URL), it gives negative impacts on SEO’s point of view.</li>
</ul>
<h2>About this example</h2>
<p>This example has the code to group the pagination, <a href="https://phppot.com/php/sorting-mysql-column-using-php/">sorting</a>, search for a listing page.</p>
<p>The pagination UI will show limited pagination links. It extends the link stack on moving forward page by page.</p>
<p>In this example, we have three options based on which pagination format will be changed.</p>
<p>The pagination request carries over the search and sorting parameters if exist.</p>
<p>It calls an AJAX script to send the pagination, search and sort request data to the PHP.</p>
<p>It allows to search and <a href="https://phppot.com/jquery/jquery-ajax-column-sort/">sort by name via AJAX</a>. The default sorting order is ascending (ASC) to sort the name column in alphabetical order.</p>
<p>The on-click event of the column header switches the sorting orders between ‘ASC’ and ‘DESC’</p>
<h3>File structure</h3>
<p>This is the file structure of the PHP pagination example. The has the paginated results to be rendered into the home page.</p>
<p>The list.php is to display the results queried with a per-page limit. The perpage.php file contains the handlers to prepare the pagination HTML to be shown into the UI.</p>
<p><img loading="lazy" class="alignnone size-full wp-image-16039" src="https://phppot.com/wp-content/uploads/2022/01/php-pagination-files.jpg" alt="php pagination files" width="180" height="228"></p>
<h2>Home page HTML to render pagination UI</h2>
<p>The following code shows the <a href="https://phppot.com/php/how-to-create-php-crud-using-oops-with-mysqli-in-mvc/">HTML to display the member</a> database results.</p>
<p>It displays a name-wise search panel above the table header. Also, the first column displays the member names with a sorting feature.</p>
<p>The PHP pagination, search and sorting actions give results without page refresh. It uses <a href="https://phppot.com/php/ajax-programming-with-php/">jQuery AJAX to request PHP</a> to perform these actions without reloading the page.</p>
<p>The “listViaAJAX()” JavaScript function request PHP pagination result via AJAX. The “sortList()” function, sets the ordering request parameters to get the sorted results.</p>
<p>The search form submit-button invokes the listViaAJAX() code to send the keyword. In the callback, it gets the response HTML from the server and updates the UI.</p>
<p class="code-heading">index.php</p>
<pre class="prettyprint"><code class="language-html">
&lt;html&gt;
&lt;head&gt; &lt;link href="./assets/style.css" type="text/css" rel="stylesheet" /&gt;
&lt;/head&gt;
&lt;body&gt; &lt;h2 class="page-title"&gt;PHP Pagination with Search&lt;/h2&gt; &lt;div id="grid"&gt; &lt;form name="frmSearch" id="frmSearch" method="post" action="index.php"&gt; &lt;div class="search-box"&gt; &lt;label&gt;Search :&lt;/label&gt; &lt;input type="text" id="search" placeholder="name" name="filter_value" class="demoInputBox" /&gt; &lt;input type="button" name="go" class="search-form-btn" value="Search" onClick="listViaAJAX();"&gt; &lt;input type="reset" class="search-form-btn" value="Reset" onclick="window.location='index.php'"&gt; &lt;/div&gt; &lt;table id=mytable cellpadding="10" cellspacing="1"&gt; &lt;thead&gt; &lt;tr&gt; &lt;th class="sortable"&gt;&lt;span onClick="sortList();"&gt;&lt;input type="hidden" name="order_type" id="order-type" value="asc" /&gt;Name&lt;/span&gt; &lt;img src='images/sort-down.svg' /&gt;&lt;/th&gt; &lt;th&gt;Registered Date&lt;/th&gt; &lt;th class="align-right"&gt;Subscription Amount&lt;/th&gt; &lt;/tr&gt; &lt;/thead&gt; &lt;tbody id="table-body"&gt; &lt;?php require_once __DIR__ . '/list.php'; ?&gt; &lt;tbody&gt; &lt;/table&gt; &lt;/form&gt; &lt;/div&gt;
&lt;/body&gt;
&lt;script src="https://code.jquery.com/jquery-3.6.0.min.js" integrity="sha256-/xUj+3OJU5yExlq6GSYGSHk7tPXikynS7ogEvDej/m4=" crossorigin="anonymous"&gt;&lt;/script&gt;
&lt;script src='./assets/pagination-ajax.js'&gt;&lt;/script&gt;
&lt;/html&gt; </code></pre>
<h2>Paginated database results in a tabular view</h2>
<p>This list.php file is to fetch and output the results to the UI. This PHP pagination request is called via AJAX by clicking the page links.</p>
<p>The below code prepares the <a href="https://phppot.com/mysql/mysql-fetch-using-php/">MySQL query to fetch the data</a> from the database. This query uses LIMIT to display the data with the per-page limit.</p>
<p>This code gets the total records’ count and per-page limit. It passes these two parameters the showPerpage() function to generate the PHP pagination HTML.</p>
<p>Added to the pagination parameters, this PHP code receives the search, sort request.</p>
<p>It prepares the WHERE and ORDER BY clause based on the search and sort request. These clauses are attached to the query to get the filtered result.</p>
<p class="code-heading">list.php</p>
<pre class="prettyprint"><code class="language-html">
&lt;?php
use Phppot\DataSource; require_once "perpage.php";
require_once __DIR__ . "/lib/DataSource.php";
$dataSource = new DataSource(); $name = "";
$queryCondition = "";
$paramType = "";
$paramValue = array();
if (! empty($_POST["filter_value"])) { $name = $_POST["filter_value"]; $queryCondition .= " WHERE name LIKE ?"; $paramType .= 's'; $paramValue[] = $_POST["filter_value"] . '%';
} $orderBy = "name";
$order = "asc";
if (! empty($_POST["order_type"])) { $order = $_POST["order_type"];
} $nextOrder = "asc";
if ($order == "asc") { $nextOrder = "desc";
}
$query = "SELECT * from tbl_member " . $queryCondition . " ORDER BY " . $orderBy . " " . $order . " ";
$href = 'index.php';
$perPage = 5;
$page = 1;
if (isset($_POST['pagination_offset'])) { $page = $_POST['pagination_offset'];
}
$start = ($page - 1) * $perPage; if ($start &lt; 0) $start = 0; $queryWithLimit = $query . " limit " . $start . "," . $perPage; $resultpage = $dataSource-&gt;select($queryWithLimit, $paramType, $paramValue); if (! empty($resultpage)) { $count = $dataSource-&gt;getRecordCount($query, $paramType, $paramValue); $resultpage["perpage"] = showperpage($count, $perPage, $href);
}
?&gt;
&lt;?php if (! empty($resultpage)) { foreach ($resultpage as $k =&gt; $v) { if (is_numeric($k)) { ?&gt; &lt;?php $date = $resultpage[$k]["date"]; $newDate = date("d-m-Y", strtotime($date)); ?&gt;
&lt;tr&gt; &lt;td class="column1"&gt;&lt;?php echo $resultpage[$k]["name"]; ?&gt;&lt;/td&gt; &lt;td class="column2"&gt;&lt;?php echo $newDate ?&gt;&lt;/td&gt; &lt;td class="column3 align-right"&gt;$ &lt;?php echo $resultpage[$k]["subscription_amount"]; ?&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;?php } }
}
if (isset($resultpage["perpage"])) { ?&gt;
&lt;tr&gt; &lt;td colspan="5" class="align-center"&gt; &lt;?php echo $resultpage["perpage"]; ?&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;?php } ?&gt; </code></pre>
<h2>External handlers to prepare PHP pagination controls</h2>
<p>It uses a constant to split the database results into pages. This code uses the following two functions.</p>
<ul>
<li>perpage() – prepares the HTML for the pagination UI links.</li>
<li>showperpage() – returns pagination HTML response.</li>
</ul>
<p>The perpage() function adds the onClick attribute to the PHP pagination links. It calls the AJAX code by sending the pagination query offset.</p>
<p>It uses the offset, per-page count and the total number of records to calculate the number of pages.&nbsp; It runs a loop through the number of pages to display the pagination links.</p>
<p>It adds HTML attributes to <a href="https://phppot.com/jquery/jquery-active-menu-highlight/">highlight the current page</a> among the page links.</p>
<p class="code-heading">perpage.php</p>
<pre class="prettyprint"><code class="language-html">
&lt;?php
function perpage($count, $per_page = '5',$href) { $output = ''; $paging_id = "link_perpage_box"; if(!isset($_POST["pagination_offset"])) $_POST["pagination_offset"] = 1; if($per_page != 0) $pages = ceil($count/$per_page); if($pages&gt;1) { if(($_POST["pagination_offset"]-3)&gt;0) { if($_POST["pagination_offset"] == 1) $output = $output . '&lt;span id=1 class="current-page"&gt;1&lt;/span&gt;'; else $output = $output . '&lt;input type="hidden" name="pagination_offset" value="1" /&gt;&lt;input type="button" class="perpage-link" value="1" onClick="listViaAJAX()" /&gt;'; } if(($_POST["pagination_offset"]-3)&gt;1) { $output = $output . '...'; } for($i=($_POST["pagination_offset"]-2); $i&lt;=($_POST["pagination_offset"]+2); $i++) { if($i&lt;1) continue; if($i&gt;$pages) break; if($_POST["pagination_offset"] == $i) $output = $output . '&lt;span id='.$i.' class="current-page" &gt;'.$i.'&lt;/span&gt;'; else $output = $output . '&lt;input type="hidden" name="pagination_offset" value="' . $i . '" /&gt;&lt;input type="button" class="perpage-link" value="' . $i . '" onClick="listViaAJAX()" /&gt;'; } if(($pages-($_POST["pagination_offset"]+2))&gt;1) { $output = $output . '...'; } if(($pages-($_POST["pagination_offset"]+2))&gt;0) { if($_POST["pagination_offset"] == $pages) $output = $output . '&lt;span id=' . ($pages) .' class="current-page"&gt;' . ($pages) .'&lt;/span&gt;'; else $output = $output . '&lt;input type="hidden" name="pagination_offset" value="' . $pages . '" /&gt;&lt;input type="button" class="perpage-link" value="' . $pages . '" onClick="listViaAJAX()" /&gt;'; } } return $output;
} function showperpage($count, $per_page = 5, $href)
{ $perpage = perpage($count, $per_page,$href); return $perpage;
}
?&gt; </code></pre>
<h2>jQuery AJAX script to call paginated list from PHP</h2>
<p>This jQuery AJAX script calls the PHP pagination code on the server-side.</p>
<p>It calls the PHP endpoint index.php by sending the form data. It serializes and posts the following data to the PHP.</p>
<ul>
<li>Page offset. Default is 0.</li>
<li>Search keyword.</li>
<li>Sorting order. Default is ascending order(ASC).</li>
</ul>
<p>The sortlist() function toggles the sorting order by clicking the corresponding column name.</p>
<p class="code-heading">pagination-ajax.js</p>
<pre class="prettyprint"><code class="language-javaScript">
function listViaAJAX() { $.ajax({ method: "POST", url: "list.php", data: $("#frmSearch").serialize(), }).done(function( response ) { $("#table-body").html(response); });
} function sortList() { if($("#order-type").val() == 'asc') { $("#order-type").val('desc'); $(".sortable img").attr("src", "images/sort-up.svg"); } else { $("#order-type").val('asc'); $(".sortable img").attr("src", "images/sort-down.svg"); } listViaAJAX();
} </code></pre>
<h2>Database script</h2>
<p>Download the below database script into your environment. It shows the database schema with the table structure.</p>
<p>It also has the sample data in the target database table. It supports to start seeing the <a href="https://phppot.com/php/php-search-and-pagination-using-pdo/">PHP pagination code result with search</a>.</p>
<p class="code-heading">sql/database.sql</p>
<pre class="prettyprint"><code class="language-html"> --
-- Database: `blog_eg`
-- -- -------------------------------------------------------- --
-- Table structure for table `tbl_member`
-- CREATE TABLE `tbl_member` ( `id` int(255) NOT NULL, `name` varchar(256) NOT NULL, `date` date NOT NULL, `subscription_amount` varchar(256) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; --
-- Dumping data for table `tbl_member`
-- INSERT INTO `tbl_member` (`id`, `name`, `date`, `subscription_amount`) VALUES
(1, 'Jennifer d\' Sousa ', '2022-01-20', '10'),
(2, 'Tom Adomian', '2021-12-25', '10'),
(3, 'Vincent Thomas', '2021-12-24', '100'),
(4, 'Lara Ricky', '2021-12-25', '100'),
(5, 'Kevin Fernandes', '2021-12-17', '100'),
(6, 'Leena Christy', '2021-12-16', '40'),
(7, 'Christian Benchamin', '2021-12-19', '60'),
(8, 'Abraham Maslow', '2021-12-17', '175'),
(9, 'Helan Immaculate', '2021-12-16', '190'),
(10, 'Jane Jancy', '2021-12-19', '30'); --
-- Indexes for dumped tables
-- --
-- Indexes for table `tbl_member`
--
ALTER TABLE `tbl_member` ADD PRIMARY KEY (`id`); --
-- AUTO_INCREMENT for dumped tables
-- --
-- AUTO_INCREMENT for table `tbl_member`
--
ALTER TABLE `tbl_member` MODIFY `id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=24; </code></pre>
<h2>PHP pagination output</h2>
<p>The following screenshot displays the PHP pagination code. It shows the tabular results with pagination links and a search panel.</p>
<p><img loading="lazy" class="alignnone size-large wp-image-16038" src="https://phppot.com/wp-content/uploads/2022/01/php-pagination-search-output-550x192.jpg" alt="php pagination search output" width="550" height="192" srcset="https://phppot.com/wp-content/uploads/2022/01/php-pagination-search-output-550x192.jpg 550w, https://phppot.com/wp-content/uploads/20...00x105.jpg 300w, https://phppot.com/wp-content/uploads/20...68x269.jpg 768w, https://phppot.com/wp-content/uploads/20...output.jpg 1098w" sizes="(max-width: 550px) 100vw, 550px"></p>
<p><a class="download" href="https://phppot.com/downloads/php-pagination-search.zip">Download</a></p>
<p> <!-- #comments --> </p>
<div class="related-articles">
<h2>Popular Articles</h2>
</p></div>
<p> <a href="https://phppot.com/php/php-pagination-search/#top" class="top">↑ Back to Top</a> </p>
</div>


https://www.sickgaming.net/blog/2022/03/...sing-ajax/
Reply



Forum Jump:


Users browsing this thread:
1 Guest(s)

Forum software by © MyBB Theme © iAndrew 2016