06-05-2020, 05:05 AM
PHP Pagination MySQL Database Example Script with Previous Next like Google
<div style="margin: 5px 5% 10px 5%;"><img src="https://www.sickgaming.net/blog/wp-content/uploads/2020/01/php-pagination-mysql-database-example-script-with-previous-next-like-google.png" width="940" height="788" title="" alt="" /></div><div><p>Last modified on December 5th, 2019 by Vincy.</p>
<p>Do you want to implement PHP pagination using core PHP with lightweight script? Jump in, this tutorial is all about it.</p>
<p>The pagination functionality is for stacking a list of items on many pages. It helps to fetch and display a huge volume of records in an effective way.</p>
<p>If you want to implement a <a href="https://phppot.com/php/php-pagination/">simple PHP pagination</a>, then it is very easy and straight forward.</p>
<p><img class="alignnone size-full wp-image-10837" src="https://phppot.com/wp-content/uploads/2019/12/php-pagination-script-with-previous-next.png" alt="PHP Pagination Script with Previous Next" width="940" height="788" srcset="https://phppot.com/wp-content/uploads/2019/12/php-pagination-script-with-previous-next.png 940w, https://phppot.com/wp-content/uploads/20...00x251.png 300w, https://phppot.com/wp-content/uploads/20...68x644.png 768w, https://phppot.com/wp-content/uploads/20...50x461.png 550w" sizes="(max-width: 940px) 100vw, 940px"></p>
<p>There is just one thing that you should keep in mind when you work on pagination. Do not pull the complete result set and filter it to display to the users.</p>
<p>SELECT only records using LIMIT from database. It can be done via AJAX or on page submit depending on your use case. We will see more about it in the example below. </p>
<p>There are scenarios to create pagination with AJAX. For example, an online quiz with a paginated question board. We have seen more examples to <a href="https://phppot.com/php/ajax-pagination-with-php/">create PHP pagination with AJAX</a>.</p>
<h2>What is inside?</h2>
<ol>
<li><a href="https://phppot.com/php/how-to-add-pagination-in-php-with-mysql/#about-this-example">About this example</a></li>
<li><a href="https://phppot.com/php/how-to-add-pagination-in-php-with-mysql/#plugins-to-enable-pagination-feature">Plugins to enable pagination feature</a></li>
<li><a href="https://phppot.com/php/how-to-add-pagination-in-php-with-mysql/#file-structure">File structure</a></li>
<li><a href="https://phppot.com/php/how-to-add-pagination-in-php-with-mysql/#database-script">Database script</a></li>
<li><a href="https://phppot.com/php/how-to-add-pagination-in-php-with-mysql/#design-landing-page-with-pagination-controls">Design landing page with pagination controls</a></li>
<li><a href="https://phppot.com/php/how-to-add-pagination-in-php-with-mysql/#php-code-to-get-paginated-results">Php code to get paginated results</a></li>
<li><a href="https://phppot.com/php/how-to-add-pagination-in-php-with-mysql/#php-ajax-pagination-output">PHP AJAX pagination Output</a></li>
</ol>
<h2 id="about-this-example">About this example</h2>
<p>In this example code, it has two types of control to navigate with the paginated results.</p>
<p>One is the pagination links to redirect us to the corresponding page. The other is an input to enter the page number. By submitting this page number we can jump into the target page from the current page.</p>
<p>I have implemented PHP pagination with a simple page refresh. We have already seen examples for achieving this with AJAX using jQuery.</p>
<p>I have enabled pagination for the list of database results displaying Animal names. The pagination links count varies based on the animal database record count. </p>
<p>A SELECT query with a specification of start and end limit fetch the page result. The per-page result count is configurable as a Config class constant. </p>
<p>There are many plugins available to create pagination easily. <a href="https://www.jqueryscript.net/other/Simple-Full-featured-jQuery-Pagination-System-Pagination-js.html" target="_blank" rel="noopener noreferrer">Pagination.js</a> and <a href="https://www.jqueryscript.net/other/Highly-Customizable-jQuery-Pagination-Component-jqPaginator.html" target="_blank" rel="noopener noreferrer">jqPaginator</a> are examples of 3-party plugins available in the market.</p>
<p>The Datatables is one of the popular library used for managing tabular data. It has an in-built search, pagination, sort, and more features. In an old article, we had seen <a href="https://phppot.com/php/datatables-server-side-processing-using-php-with-mysql/">how to integrate Datatables</a> to list records with pagination.</p>
<p>Having said all these, better to go with custom code. Because external plugins, libraries will slow down the application.</p>
<h2 id="file-structure">File structure</h2>
<p>The working example code contains the following files in the below hierarchical order.</p>
<p><img class="alignnone size-full wp-image-10818" src="https://phppot.com/wp-content/uploads/2019/12/ajax-pagination-file-structure.jpg" alt="AJAZ Pagination File Structure" width="287" height="292"></p>
<p>The <strong>index.php</strong> file is a landing page. It contains the HTML to display pagination links and the current page results.</p>
<p>Two CSS used in this example. The <strong>phppot-style.css</strong> has generic layout styles. The <strong>pagination.css</strong> has the exclusive styles created for this example.</p>
<p><strong>Model/Pagination.php</strong> includes functions to fetch the MySQL data. It receives parameters to set the limit for the SELECT query.</p>
<p>The file structure includes a SQL script to set up the database for running this example.</p>
<h2 id="database-script">Database script</h2>
<pre class="prettyprint lang-php">-- -- Database: `pagination` -- -- -------------------------------------------------------- -- -- Table structure for table `tbl_animal` -- CREATE TABLE `tbl_animal` ( `id` int(11) UNSIGNED NOT NULL, `common_name` varchar(255) NOT NULL DEFAULT '', `scientific_name` varchar(255) NOT NULL DEFAULT '' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- Dumping data for table `tbl_animal` -- INSERT INTO `tbl_animal` (`id`, `common_name`, `scientific_name`) VALUES (1, 'Bison', 'Bos gaurus\r\n'), (2, 'Black buck', 'Antelope cervicapra'), (3, 'Chinkara', 'Gazella bennettii'), (4, 'Nilgai', 'Boselaphus tragocamelus'), (5, 'Wolf', 'Canis lupus'), (6, 'Lion', 'Panthera leo'), (7, 'Elephant', 'Elephas maximus'), (8, 'Wild Ass', 'Equus africanus asinus'), (9, 'Panther', 'Panthera pardus'), (10, 'Kashmir stag', 'Cervus canadensis hanglu'), (11, 'Peacock', 'Pavo cristatus'), (12, 'Siberian crane', 'Grus leucogeranus'), (13, 'Fox', 'Vulpes vulpes'), (14, 'Rhinoceros', 'Rhinoceros unicornis'), (15, 'Tiger', 'Panthera Tigris'), (16, 'Crocodile', 'Crocodylus palustris'), (17, 'Gavial or Gharial', 'Gavialis gangeticus'), (18, 'Horse', 'Equus caballus'), (19, 'Zebra', 'Equus quagga'), (20, 'Buffalow', 'Babalus bubalis'), (21, 'Wild boar', 'Sus scrofa'), (22, 'Arabian camel', 'Camelus dromedaries'), (23, 'Giraffe', 'GiraffaÊcamelopardalis'), (24, 'House wall Lizard', 'Hemidactylus flaviviridis'), (25, 'Hippopotamus', 'Hippopotamus amphibius'), (26, 'Rhesus monkey', 'Macaca mulatta'), (27, 'Dog', 'Canis lupus familiaris'), (28, 'Cat', 'Felis domesticus'), (29, 'Cheetah', 'Acinonyx jubatus'), (30, 'Black rat', 'Rattus rattus'), (31, 'House mouse', 'Mus musculus'), (32, 'Rabbit', 'Oryctolagus cuniculus'), (33, 'Great horned owl', 'Bubo virginianus'), (34, 'House sparrow', 'Passer domesticus'), (35, 'House crow', 'Corvus splendens'), (36, 'Common myna', 'Acridotheres tristis'), (37, 'Indian parrot', 'Psittacula eupatria'), (38, 'Bulbul', 'Molpastes cafer'), (39, 'Koel', 'Eudynamis scolopaccus'), (40, 'Pigeon', 'Columba livia'), (41, 'Indian Cobra', 'Naja naja'), (42, 'King cobra', 'Ophiophagus hannah'), (43, 'Sea snake', 'Hydrophiinae'), (44, 'Indian python', 'Python molurus'), (45, 'Rat snake', 'Rat snake'); -- -- Indexes for dumped tables -- -- -- Indexes for table `tbl_animal` -- ALTER TABLE `tbl_animal` ADD PRIMARY KEY (`id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `tbl_animal` -- ALTER TABLE `tbl_animal` MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=46; </pre>
<h2 id="design-landing-page-with-pagination-controls">Designing landing page with pagination controls</h2>
<p>The landing page shows the <a href="https://phppot.com/php/jquery-ajax-pagination/">database results with a pagination option</a>. The following HTML has the embedded PHP script to display paginated results.</p>
<p>It contains table rows created in a loop on iterating the data array. Below this table, it shows pagination links with previous next navigation. This navigation allows moving back and forth from the current page.</p>
<p>This example also contains a HMTL form to enter the target page number. If you enter a number within the number of pages, then it will get the appropriate page result.</p>
<pre class="prettyprint lang-php"><!doctype html> <html lang="en"> <head> <title>PHP Pagination MySQL Database Example Script with Previous Next like Google</title> <link rel="stylesheet" type="text/css" href="assets/css/pagination.css"> <link rel="stylesheet" type="text/css" href="assets/css/phppot-style.css"> <script src="vendor/jquery/jquery-3.3.1.js"></script> </head> <body> <div class="phppot-container"> <div class="phppot-form"> <h1>Animal Names</h1> <table> <tr> <th>Id</th> <th>Common Name</th> <th>Scientific Name</th> </tr> <?php if (! empty($pageResult)) { foreach ($pageResult as $page) { ?> <tr> <td><?php echo $page['id'];?></td> <td><?php echo $page['common_name'];?></td> <td><?php echo $page['scientific_name'];?></td> </tr> <?php }} ?> </table> <div class="pagination"> <?php if (($page > 1) && ($pn > 1)) { ?> <a class="previous-page" id="prev-page" href="<?php echo $queryString;?>page=<?php echo (($pn-1));?>" title="Previous Page"><span>❮ Previous</span></a> <?php }?> <?php if (($pn - 1) > 1) { ?> <a href='index.php?page=1'><div class='page-a-link'> 1 </div></a> <div class='page-before-after'>...</div> <?php } for ($i = ($pn - 1); $i <= ($pn + 1); $i ++) { if ($i < 1) continue; if ($i > $totalPages) break; if ($i == $pn) { $class = "active"; } else { $class = "page-a-link"; } ?> <a href='index.php?page=<?php echo $i; ?>'> <div class='<?php echo $class; ?>'><?php echo $i; ?></div> </a> <?php } if (($totalPages - ($pn + 1)) >= 1) { ?> <div class='page-before-after'>...</div> <?php } if (($totalPages - ($pn + 1)) > 0) { if ($pn == $totalPages) { $class = "active"; } else { $class = "page-a-link"; } ?> <a href='index.php?page=<?php echo $totalPages; ?>'><div class='<?php echo $class; ?>'><?php echo $totalPages; ?></div></a> <?php } ?> <?php if (($page > 1) && ($pn < $totalPages)) { ?> <a class="next" id="next-page" href="<?php echo $queryString;?>page=<?php echo (($pn+1));?>" title="Next Page"><span>Next ❯</span></a> <?php } ?> </div> <div class="goto-page"> <form action="" method="GET" onsubmit="return pageValidation()"> <input type="submit" class="goto-button" value="Go to"> <input type="text" class="enter-page-no" name="page" min="1" id="page-no"> <input type="hidden" id="total-page" value="<?php echo $totalPages;?>"> </form> </div> </div> </div> </body> </html> </pre>
<p>The pageValidation() is a JavaScript method to confirm the entered page number. If the page number input contains invalid data, then this script will return false. Then it stops form submit and thereby preventing the page-jump action.</p>
<pre class="prettyprint lang-php"><script> function pageValidation() { var valid=true; var pageNo = $('#page-no').val(); var totalPage = $('#total-page').val(); if(pageNo == ""|| pageNo < 1 || !pageNo.match(/\d+/) || pageNo > parseInt(totalPage)){ $("#page-no").css("border-color","#ee0000").show(); valid=false; } return valid; } </script> </pre>
<p>The following styles are from the pagination.css. It defines the CSS for the table view and the pagination elements.</p>
<pre class="prettyprint lang-php">.pagination { display: inline-flex; } .pagination a { color: #505050; text-decoration: none; } .page-a-link { font-family: arial, verdana; font-size: 12px; border: 1px #afafaf solid; background-color: #fbfbfb; padding: 6px 12px 6px 12px; margin: 6px; text-decoration: none; border-radius: 3px; } .active { font-family: arial, verdana; font-size: 12px; padding: 8px 14px 6px 14px; margin: 3px; background-color: #404040; text-decoration: none; border-radius: 3px; margin: 6px; color: #FFF; } a.previous-page { margin: 10px 10px 10px 0px; } a.prev-next:hover { color: #03a9f4; } a.next { margin: 10px 0px 10px 10px; } input.enter-page-no { width: 42px !important; height: 28px !important; font-size: 12px; padding: 6px 12px 6px 12px !important; margin: 6px; border-radius: 3px !important; text-align: center !important; } input.goto-button { max-width: 80px; font-size: 12px; padding: 6px 12px 6px 12px !important; border: 1px solid #9a9a9a; border-radius: 3px !important; text-align: center !important; background: -webkit-gradient(linear, left top, left bottom, color-stop(0%, #dfdd99), color-stop(100%, #bcbd2b)); background: -webkit-linear-gradient(top, #dfdc99, #b8bd2b); border: 1px solid #97a031; box-shadow: inset 0px 1px 0px rgb(255, 255, 211), 0px 1px 4px rgba(199, 199, 199, 0.9); } .goto-page { float: right; } .page-before-after { font-weight: bold; padding-top: 12px; text-decoration: none; } </pre>
<h2 id="php-code-to-get-paginated-results">Php code to get paginated results</h2>
<p>This PHP script is for reading the database results and get the record count, pages count. The pages count varies based on the record count and the LIMIT_PER_PAGE configuration.</p>
<p>If there are more pages, this example will show only limited pages as like as Google pagination. This feature will help to make the pagination block to fit into the viewport.</p>
<p>On successive page navigation, you will get more pagination links to browse further.</p>
<pre class="prettyprint lang-php"><?php namespace Phppot; require_once __DIR__ . '/Model/Pagination.php'; $paginationModel = new Pagination(); $pageResult = $paginationModel->getPage(); $queryString = "?"; if (isset($_GET["page"])) { $pn = $_GET["page"]; } else { $pn = 1; } $limit = Config::LIMIT_PER_PAGE; $totalRecords = $paginationModel->getAllRecords(); $totalPages = ceil($totalRecords / $limit); ?> </pre>
<h3>PHP classes, libraries, configs used in this example</h3>
<p>This is a common DataSource library used in most examples. Here, I have shown the functions that are only used in this example. </p>
<p>You can get the full code by downloading the source added to this article.</p>
<p><strong>Datasource.php</strong></p>
<pre class="prettyprint lang-php"><?php /** * Copyright © 2019 Phppot * * Distributed under MIT license with an exception that, * you don’t have to include the full MIT License in your code. * In essense, you can use it on commercial software, modify and distribute free. * Though not mandatory, you are requested to attribute this URL in your code or website. */ namespace Phppot; /** * Generic datasource class for handling DB operations. * Uses MySqli and PreparedStatements. * * @version 2.5 - recordCount function added */ class DataSource { // PHP 7.1.0 visibility modifiers are allowed for class constants. // when using above 7.1.0, declare the below constants as private const HOST = 'localhost'; const USERNAME = 'root'; const PASSWORD = 'test'; const DATABASENAME = 'pagination'; private $conn; /** * PHP implicitly takes care of cleanup for default connection types. * So no need to worry about closing the connection. * * Singletons not required in PHP as there is no * concept of shared memory. * Every object lives only for a request. * * Keeping things simple and that works! */ function __construct() { $this->conn = $this->getConnection(); } /** * If connection object is needed use this method and get access to it. * Otherwise, use the below methods for insert / update / etc. * * @return \mysqli */ public function getConnection() { $conn = new \mysqli(self::HOST, self::USERNAME, self::PASSWORD, self::DATABASENAME); if (mysqli_connect_errno()) { trigger_error("Problem with connecting to database."); } $conn->set_charset("utf8"); return $conn; } /** * To get database results * * @param string $query * @param string $paramType * @param array $paramArray * @return array */ public function select($query, $paramType = "", $paramArray = array()) { $stmt = $this->conn->prepare($query); if (! empty($paramType) && ! empty($paramArray)) { $this->bindQueryParams($stmt, $paramType, $paramArray); } $stmt->execute(); $result = $stmt->get_result(); if ($result->num_rows > 0) { while ($row = $result->fetch_assoc()) { $resultset[] = $row; } } if (! empty($resultset)) { return $resultset; } } /** * To insert * * @param string $query * @param string $paramType * @param array $paramArray * @return int */ public function insert($query, $paramType, $paramArray) { //... } /** * To execute query * * @param string $query * @param string $paramType * @param array $paramArray */ public function execute($query, $paramType = "", $paramArray = array()) { //... } /** * 1. * Prepares parameter binding * 2. Bind prameters to the sql statement * * @param string $stmt * @param string $paramType * @param array $paramArray */ public function bindQueryParams($stmt, $paramType, $paramArray = array()) { $paramValueReference[] = & $paramType; for ($i = 0; $i < count($paramArray); $i ++) { $paramValueReference[] = & $paramArray[$i]; } call_user_func_array(array( $stmt, 'bind_param' ), $paramValueReference); } /** * To get database results * * @param string $query * @param string $paramType * @param array $paramArray * @return array */ public function getRecordCount($query, $paramType = "", $paramArray = array()) { $stmt = $this->conn->prepare($query); if (! empty($paramType) && ! empty($paramArray)) { $this->bindQueryParams($stmt, $paramType, $paramArray); } $stmt->execute(); $stmt->store_result(); $recordCount = $stmt->num_rows; return $recordCount; } } </pre>
<p><strong>Config.php</strong></p>
<p>This is the config file added for this example. I kept the per-page record limit as an application constant. I used this for setting the SELECT query limit.</p>
<p>You can add more constants and fine-tune the PHP pagination script to work based on them. For example, create new constant SHOW_ALL_LINKS. Set it on/off to enable/disable the Google-like feature to show limited links. </p>
<pre class="prettyprint lang-php"><?php namespace Phppot; class Config { const LIMIT_PER_PAGE = '5'; } </pre>
<p>This screenshot shows the output of the PHP pagination example code. It shows five records per page. </p>
<p>On the left, it shows usual pagination with previous next and separate page links. On the right side, it is a HMTL form to allow the user to enter the page number.</p>
<p><img class="alignnone size-full wp-image-10821" src="https://phppot.com/wp-content/uploads/2019/12/php-pagination-mysql-example-output.jpg" alt="PHP pagination MySQL Example Output" width="753" height="375" srcset="https://phppot.com/wp-content/uploads/2019/12/php-pagination-mysql-example-output.jpg 753w, https://phppot.com/wp-content/uploads/20...00x149.jpg 300w, https://phppot.com/wp-content/uploads/20...50x274.jpg 550w" sizes="(max-width: 753px) 100vw, 753px"></p>
<h2>See Also</h2>
<p>These links have a variety of pagination scripts.</p>
<ol>
<li><a href="https://phppot.com/php/php-search-and-pagination-using-pdo/">PHP CRUD with search and pagination</a></li>
<li><a href="https://phppot.com/php/ajax-pagination-with-tabular-records-using-php-and-jquery/">Pagination with Tabular records</a></li>
<li><a href="https://phppot.com/php/how-to-create-facebook-like-infinite-scroll-pagination-using-php-and-jquery/">How to create Facebook-link infinite scroll pagination</a></li>
</ol>
<h2 id>Conclusion</h2>
<p>Thus, we created PHP pagination with Google-like limited links and previous next links. We have seen the interlinking of older <a href="https://phppot.com/php/php-search-and-pagination-using-pdo/">PHP pagination script</a> along with this article.</p>
<p>We have discussed the third-party libraries that contain in-built pagination feature. It will be helpful to have a choice if are searching for such plugins.</p>
<p>In this custom PHP pagination example, we have provided an option to jump into a target page directly via a form. Sometimes, it itself is enough with previous next navigation.</p>
<p>Hope this article and the example help you to build a nice simple PHP pagination for your web application.</p>
<p><a class="download" href="https://phppot.com/downloads/php-pagination-mysql-database-example-script-with-previous-next-like-google.zip">Download</a></p>
<p> <!-- #comments --> </p>
<div class="related-articles">
<h2>Popular Articles</h2>
</p></div>
<p> <a href="https://phppot.com/php/how-to-add-pagination-in-php-with-mysql/#top" class="top">↑ Back to Top</a> </p>
</div>
https://www.sickgaming.net/blog/2019/12/...ke-google/
<div style="margin: 5px 5% 10px 5%;"><img src="https://www.sickgaming.net/blog/wp-content/uploads/2020/01/php-pagination-mysql-database-example-script-with-previous-next-like-google.png" width="940" height="788" title="" alt="" /></div><div><p>Last modified on December 5th, 2019 by Vincy.</p>
<p>Do you want to implement PHP pagination using core PHP with lightweight script? Jump in, this tutorial is all about it.</p>
<p>The pagination functionality is for stacking a list of items on many pages. It helps to fetch and display a huge volume of records in an effective way.</p>
<p>If you want to implement a <a href="https://phppot.com/php/php-pagination/">simple PHP pagination</a>, then it is very easy and straight forward.</p>
<p><img class="alignnone size-full wp-image-10837" src="https://phppot.com/wp-content/uploads/2019/12/php-pagination-script-with-previous-next.png" alt="PHP Pagination Script with Previous Next" width="940" height="788" srcset="https://phppot.com/wp-content/uploads/2019/12/php-pagination-script-with-previous-next.png 940w, https://phppot.com/wp-content/uploads/20...00x251.png 300w, https://phppot.com/wp-content/uploads/20...68x644.png 768w, https://phppot.com/wp-content/uploads/20...50x461.png 550w" sizes="(max-width: 940px) 100vw, 940px"></p>
<p>There is just one thing that you should keep in mind when you work on pagination. Do not pull the complete result set and filter it to display to the users.</p>
<p>SELECT only records using LIMIT from database. It can be done via AJAX or on page submit depending on your use case. We will see more about it in the example below. </p>
<p>There are scenarios to create pagination with AJAX. For example, an online quiz with a paginated question board. We have seen more examples to <a href="https://phppot.com/php/ajax-pagination-with-php/">create PHP pagination with AJAX</a>.</p>
<h2>What is inside?</h2>
<ol>
<li><a href="https://phppot.com/php/how-to-add-pagination-in-php-with-mysql/#about-this-example">About this example</a></li>
<li><a href="https://phppot.com/php/how-to-add-pagination-in-php-with-mysql/#plugins-to-enable-pagination-feature">Plugins to enable pagination feature</a></li>
<li><a href="https://phppot.com/php/how-to-add-pagination-in-php-with-mysql/#file-structure">File structure</a></li>
<li><a href="https://phppot.com/php/how-to-add-pagination-in-php-with-mysql/#database-script">Database script</a></li>
<li><a href="https://phppot.com/php/how-to-add-pagination-in-php-with-mysql/#design-landing-page-with-pagination-controls">Design landing page with pagination controls</a></li>
<li><a href="https://phppot.com/php/how-to-add-pagination-in-php-with-mysql/#php-code-to-get-paginated-results">Php code to get paginated results</a></li>
<li><a href="https://phppot.com/php/how-to-add-pagination-in-php-with-mysql/#php-ajax-pagination-output">PHP AJAX pagination Output</a></li>
</ol>
<h2 id="about-this-example">About this example</h2>
<p>In this example code, it has two types of control to navigate with the paginated results.</p>
<p>One is the pagination links to redirect us to the corresponding page. The other is an input to enter the page number. By submitting this page number we can jump into the target page from the current page.</p>
<p>I have implemented PHP pagination with a simple page refresh. We have already seen examples for achieving this with AJAX using jQuery.</p>
<p>I have enabled pagination for the list of database results displaying Animal names. The pagination links count varies based on the animal database record count. </p>
<p>A SELECT query with a specification of start and end limit fetch the page result. The per-page result count is configurable as a Config class constant. </p>
<p>There are many plugins available to create pagination easily. <a href="https://www.jqueryscript.net/other/Simple-Full-featured-jQuery-Pagination-System-Pagination-js.html" target="_blank" rel="noopener noreferrer">Pagination.js</a> and <a href="https://www.jqueryscript.net/other/Highly-Customizable-jQuery-Pagination-Component-jqPaginator.html" target="_blank" rel="noopener noreferrer">jqPaginator</a> are examples of 3-party plugins available in the market.</p>
<p>The Datatables is one of the popular library used for managing tabular data. It has an in-built search, pagination, sort, and more features. In an old article, we had seen <a href="https://phppot.com/php/datatables-server-side-processing-using-php-with-mysql/">how to integrate Datatables</a> to list records with pagination.</p>
<p>Having said all these, better to go with custom code. Because external plugins, libraries will slow down the application.</p>
<h2 id="file-structure">File structure</h2>
<p>The working example code contains the following files in the below hierarchical order.</p>
<p><img class="alignnone size-full wp-image-10818" src="https://phppot.com/wp-content/uploads/2019/12/ajax-pagination-file-structure.jpg" alt="AJAZ Pagination File Structure" width="287" height="292"></p>
<p>The <strong>index.php</strong> file is a landing page. It contains the HTML to display pagination links and the current page results.</p>
<p>Two CSS used in this example. The <strong>phppot-style.css</strong> has generic layout styles. The <strong>pagination.css</strong> has the exclusive styles created for this example.</p>
<p><strong>Model/Pagination.php</strong> includes functions to fetch the MySQL data. It receives parameters to set the limit for the SELECT query.</p>
<p>The file structure includes a SQL script to set up the database for running this example.</p>
<h2 id="database-script">Database script</h2>
<pre class="prettyprint lang-php">-- -- Database: `pagination` -- -- -------------------------------------------------------- -- -- Table structure for table `tbl_animal` -- CREATE TABLE `tbl_animal` ( `id` int(11) UNSIGNED NOT NULL, `common_name` varchar(255) NOT NULL DEFAULT '', `scientific_name` varchar(255) NOT NULL DEFAULT '' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- Dumping data for table `tbl_animal` -- INSERT INTO `tbl_animal` (`id`, `common_name`, `scientific_name`) VALUES (1, 'Bison', 'Bos gaurus\r\n'), (2, 'Black buck', 'Antelope cervicapra'), (3, 'Chinkara', 'Gazella bennettii'), (4, 'Nilgai', 'Boselaphus tragocamelus'), (5, 'Wolf', 'Canis lupus'), (6, 'Lion', 'Panthera leo'), (7, 'Elephant', 'Elephas maximus'), (8, 'Wild Ass', 'Equus africanus asinus'), (9, 'Panther', 'Panthera pardus'), (10, 'Kashmir stag', 'Cervus canadensis hanglu'), (11, 'Peacock', 'Pavo cristatus'), (12, 'Siberian crane', 'Grus leucogeranus'), (13, 'Fox', 'Vulpes vulpes'), (14, 'Rhinoceros', 'Rhinoceros unicornis'), (15, 'Tiger', 'Panthera Tigris'), (16, 'Crocodile', 'Crocodylus palustris'), (17, 'Gavial or Gharial', 'Gavialis gangeticus'), (18, 'Horse', 'Equus caballus'), (19, 'Zebra', 'Equus quagga'), (20, 'Buffalow', 'Babalus bubalis'), (21, 'Wild boar', 'Sus scrofa'), (22, 'Arabian camel', 'Camelus dromedaries'), (23, 'Giraffe', 'GiraffaÊcamelopardalis'), (24, 'House wall Lizard', 'Hemidactylus flaviviridis'), (25, 'Hippopotamus', 'Hippopotamus amphibius'), (26, 'Rhesus monkey', 'Macaca mulatta'), (27, 'Dog', 'Canis lupus familiaris'), (28, 'Cat', 'Felis domesticus'), (29, 'Cheetah', 'Acinonyx jubatus'), (30, 'Black rat', 'Rattus rattus'), (31, 'House mouse', 'Mus musculus'), (32, 'Rabbit', 'Oryctolagus cuniculus'), (33, 'Great horned owl', 'Bubo virginianus'), (34, 'House sparrow', 'Passer domesticus'), (35, 'House crow', 'Corvus splendens'), (36, 'Common myna', 'Acridotheres tristis'), (37, 'Indian parrot', 'Psittacula eupatria'), (38, 'Bulbul', 'Molpastes cafer'), (39, 'Koel', 'Eudynamis scolopaccus'), (40, 'Pigeon', 'Columba livia'), (41, 'Indian Cobra', 'Naja naja'), (42, 'King cobra', 'Ophiophagus hannah'), (43, 'Sea snake', 'Hydrophiinae'), (44, 'Indian python', 'Python molurus'), (45, 'Rat snake', 'Rat snake'); -- -- Indexes for dumped tables -- -- -- Indexes for table `tbl_animal` -- ALTER TABLE `tbl_animal` ADD PRIMARY KEY (`id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `tbl_animal` -- ALTER TABLE `tbl_animal` MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=46; </pre>
<h2 id="design-landing-page-with-pagination-controls">Designing landing page with pagination controls</h2>
<p>The landing page shows the <a href="https://phppot.com/php/jquery-ajax-pagination/">database results with a pagination option</a>. The following HTML has the embedded PHP script to display paginated results.</p>
<p>It contains table rows created in a loop on iterating the data array. Below this table, it shows pagination links with previous next navigation. This navigation allows moving back and forth from the current page.</p>
<p>This example also contains a HMTL form to enter the target page number. If you enter a number within the number of pages, then it will get the appropriate page result.</p>
<pre class="prettyprint lang-php"><!doctype html> <html lang="en"> <head> <title>PHP Pagination MySQL Database Example Script with Previous Next like Google</title> <link rel="stylesheet" type="text/css" href="assets/css/pagination.css"> <link rel="stylesheet" type="text/css" href="assets/css/phppot-style.css"> <script src="vendor/jquery/jquery-3.3.1.js"></script> </head> <body> <div class="phppot-container"> <div class="phppot-form"> <h1>Animal Names</h1> <table> <tr> <th>Id</th> <th>Common Name</th> <th>Scientific Name</th> </tr> <?php if (! empty($pageResult)) { foreach ($pageResult as $page) { ?> <tr> <td><?php echo $page['id'];?></td> <td><?php echo $page['common_name'];?></td> <td><?php echo $page['scientific_name'];?></td> </tr> <?php }} ?> </table> <div class="pagination"> <?php if (($page > 1) && ($pn > 1)) { ?> <a class="previous-page" id="prev-page" href="<?php echo $queryString;?>page=<?php echo (($pn-1));?>" title="Previous Page"><span>❮ Previous</span></a> <?php }?> <?php if (($pn - 1) > 1) { ?> <a href='index.php?page=1'><div class='page-a-link'> 1 </div></a> <div class='page-before-after'>...</div> <?php } for ($i = ($pn - 1); $i <= ($pn + 1); $i ++) { if ($i < 1) continue; if ($i > $totalPages) break; if ($i == $pn) { $class = "active"; } else { $class = "page-a-link"; } ?> <a href='index.php?page=<?php echo $i; ?>'> <div class='<?php echo $class; ?>'><?php echo $i; ?></div> </a> <?php } if (($totalPages - ($pn + 1)) >= 1) { ?> <div class='page-before-after'>...</div> <?php } if (($totalPages - ($pn + 1)) > 0) { if ($pn == $totalPages) { $class = "active"; } else { $class = "page-a-link"; } ?> <a href='index.php?page=<?php echo $totalPages; ?>'><div class='<?php echo $class; ?>'><?php echo $totalPages; ?></div></a> <?php } ?> <?php if (($page > 1) && ($pn < $totalPages)) { ?> <a class="next" id="next-page" href="<?php echo $queryString;?>page=<?php echo (($pn+1));?>" title="Next Page"><span>Next ❯</span></a> <?php } ?> </div> <div class="goto-page"> <form action="" method="GET" onsubmit="return pageValidation()"> <input type="submit" class="goto-button" value="Go to"> <input type="text" class="enter-page-no" name="page" min="1" id="page-no"> <input type="hidden" id="total-page" value="<?php echo $totalPages;?>"> </form> </div> </div> </div> </body> </html> </pre>
<p>The pageValidation() is a JavaScript method to confirm the entered page number. If the page number input contains invalid data, then this script will return false. Then it stops form submit and thereby preventing the page-jump action.</p>
<pre class="prettyprint lang-php"><script> function pageValidation() { var valid=true; var pageNo = $('#page-no').val(); var totalPage = $('#total-page').val(); if(pageNo == ""|| pageNo < 1 || !pageNo.match(/\d+/) || pageNo > parseInt(totalPage)){ $("#page-no").css("border-color","#ee0000").show(); valid=false; } return valid; } </script> </pre>
<p>The following styles are from the pagination.css. It defines the CSS for the table view and the pagination elements.</p>
<pre class="prettyprint lang-php">.pagination { display: inline-flex; } .pagination a { color: #505050; text-decoration: none; } .page-a-link { font-family: arial, verdana; font-size: 12px; border: 1px #afafaf solid; background-color: #fbfbfb; padding: 6px 12px 6px 12px; margin: 6px; text-decoration: none; border-radius: 3px; } .active { font-family: arial, verdana; font-size: 12px; padding: 8px 14px 6px 14px; margin: 3px; background-color: #404040; text-decoration: none; border-radius: 3px; margin: 6px; color: #FFF; } a.previous-page { margin: 10px 10px 10px 0px; } a.prev-next:hover { color: #03a9f4; } a.next { margin: 10px 0px 10px 10px; } input.enter-page-no { width: 42px !important; height: 28px !important; font-size: 12px; padding: 6px 12px 6px 12px !important; margin: 6px; border-radius: 3px !important; text-align: center !important; } input.goto-button { max-width: 80px; font-size: 12px; padding: 6px 12px 6px 12px !important; border: 1px solid #9a9a9a; border-radius: 3px !important; text-align: center !important; background: -webkit-gradient(linear, left top, left bottom, color-stop(0%, #dfdd99), color-stop(100%, #bcbd2b)); background: -webkit-linear-gradient(top, #dfdc99, #b8bd2b); border: 1px solid #97a031; box-shadow: inset 0px 1px 0px rgb(255, 255, 211), 0px 1px 4px rgba(199, 199, 199, 0.9); } .goto-page { float: right; } .page-before-after { font-weight: bold; padding-top: 12px; text-decoration: none; } </pre>
<h2 id="php-code-to-get-paginated-results">Php code to get paginated results</h2>
<p>This PHP script is for reading the database results and get the record count, pages count. The pages count varies based on the record count and the LIMIT_PER_PAGE configuration.</p>
<p>If there are more pages, this example will show only limited pages as like as Google pagination. This feature will help to make the pagination block to fit into the viewport.</p>
<p>On successive page navigation, you will get more pagination links to browse further.</p>
<pre class="prettyprint lang-php"><?php namespace Phppot; require_once __DIR__ . '/Model/Pagination.php'; $paginationModel = new Pagination(); $pageResult = $paginationModel->getPage(); $queryString = "?"; if (isset($_GET["page"])) { $pn = $_GET["page"]; } else { $pn = 1; } $limit = Config::LIMIT_PER_PAGE; $totalRecords = $paginationModel->getAllRecords(); $totalPages = ceil($totalRecords / $limit); ?> </pre>
<h3>PHP classes, libraries, configs used in this example</h3>
<p>This is a common DataSource library used in most examples. Here, I have shown the functions that are only used in this example. </p>
<p>You can get the full code by downloading the source added to this article.</p>
<p><strong>Datasource.php</strong></p>
<pre class="prettyprint lang-php"><?php /** * Copyright © 2019 Phppot * * Distributed under MIT license with an exception that, * you don’t have to include the full MIT License in your code. * In essense, you can use it on commercial software, modify and distribute free. * Though not mandatory, you are requested to attribute this URL in your code or website. */ namespace Phppot; /** * Generic datasource class for handling DB operations. * Uses MySqli and PreparedStatements. * * @version 2.5 - recordCount function added */ class DataSource { // PHP 7.1.0 visibility modifiers are allowed for class constants. // when using above 7.1.0, declare the below constants as private const HOST = 'localhost'; const USERNAME = 'root'; const PASSWORD = 'test'; const DATABASENAME = 'pagination'; private $conn; /** * PHP implicitly takes care of cleanup for default connection types. * So no need to worry about closing the connection. * * Singletons not required in PHP as there is no * concept of shared memory. * Every object lives only for a request. * * Keeping things simple and that works! */ function __construct() { $this->conn = $this->getConnection(); } /** * If connection object is needed use this method and get access to it. * Otherwise, use the below methods for insert / update / etc. * * @return \mysqli */ public function getConnection() { $conn = new \mysqli(self::HOST, self::USERNAME, self::PASSWORD, self::DATABASENAME); if (mysqli_connect_errno()) { trigger_error("Problem with connecting to database."); } $conn->set_charset("utf8"); return $conn; } /** * To get database results * * @param string $query * @param string $paramType * @param array $paramArray * @return array */ public function select($query, $paramType = "", $paramArray = array()) { $stmt = $this->conn->prepare($query); if (! empty($paramType) && ! empty($paramArray)) { $this->bindQueryParams($stmt, $paramType, $paramArray); } $stmt->execute(); $result = $stmt->get_result(); if ($result->num_rows > 0) { while ($row = $result->fetch_assoc()) { $resultset[] = $row; } } if (! empty($resultset)) { return $resultset; } } /** * To insert * * @param string $query * @param string $paramType * @param array $paramArray * @return int */ public function insert($query, $paramType, $paramArray) { //... } /** * To execute query * * @param string $query * @param string $paramType * @param array $paramArray */ public function execute($query, $paramType = "", $paramArray = array()) { //... } /** * 1. * Prepares parameter binding * 2. Bind prameters to the sql statement * * @param string $stmt * @param string $paramType * @param array $paramArray */ public function bindQueryParams($stmt, $paramType, $paramArray = array()) { $paramValueReference[] = & $paramType; for ($i = 0; $i < count($paramArray); $i ++) { $paramValueReference[] = & $paramArray[$i]; } call_user_func_array(array( $stmt, 'bind_param' ), $paramValueReference); } /** * To get database results * * @param string $query * @param string $paramType * @param array $paramArray * @return array */ public function getRecordCount($query, $paramType = "", $paramArray = array()) { $stmt = $this->conn->prepare($query); if (! empty($paramType) && ! empty($paramArray)) { $this->bindQueryParams($stmt, $paramType, $paramArray); } $stmt->execute(); $stmt->store_result(); $recordCount = $stmt->num_rows; return $recordCount; } } </pre>
<p><strong>Config.php</strong></p>
<p>This is the config file added for this example. I kept the per-page record limit as an application constant. I used this for setting the SELECT query limit.</p>
<p>You can add more constants and fine-tune the PHP pagination script to work based on them. For example, create new constant SHOW_ALL_LINKS. Set it on/off to enable/disable the Google-like feature to show limited links. </p>
<pre class="prettyprint lang-php"><?php namespace Phppot; class Config { const LIMIT_PER_PAGE = '5'; } </pre>
<p>This screenshot shows the output of the PHP pagination example code. It shows five records per page. </p>
<p>On the left, it shows usual pagination with previous next and separate page links. On the right side, it is a HMTL form to allow the user to enter the page number.</p>
<p><img class="alignnone size-full wp-image-10821" src="https://phppot.com/wp-content/uploads/2019/12/php-pagination-mysql-example-output.jpg" alt="PHP pagination MySQL Example Output" width="753" height="375" srcset="https://phppot.com/wp-content/uploads/2019/12/php-pagination-mysql-example-output.jpg 753w, https://phppot.com/wp-content/uploads/20...00x149.jpg 300w, https://phppot.com/wp-content/uploads/20...50x274.jpg 550w" sizes="(max-width: 753px) 100vw, 753px"></p>
<h2>See Also</h2>
<p>These links have a variety of pagination scripts.</p>
<ol>
<li><a href="https://phppot.com/php/php-search-and-pagination-using-pdo/">PHP CRUD with search and pagination</a></li>
<li><a href="https://phppot.com/php/ajax-pagination-with-tabular-records-using-php-and-jquery/">Pagination with Tabular records</a></li>
<li><a href="https://phppot.com/php/how-to-create-facebook-like-infinite-scroll-pagination-using-php-and-jquery/">How to create Facebook-link infinite scroll pagination</a></li>
</ol>
<h2 id>Conclusion</h2>
<p>Thus, we created PHP pagination with Google-like limited links and previous next links. We have seen the interlinking of older <a href="https://phppot.com/php/php-search-and-pagination-using-pdo/">PHP pagination script</a> along with this article.</p>
<p>We have discussed the third-party libraries that contain in-built pagination feature. It will be helpful to have a choice if are searching for such plugins.</p>
<p>In this custom PHP pagination example, we have provided an option to jump into a target page directly via a form. Sometimes, it itself is enough with previous next navigation.</p>
<p>Hope this article and the example help you to build a nice simple PHP pagination for your web application.</p>
<p><a class="download" href="https://phppot.com/downloads/php-pagination-mysql-database-example-script-with-previous-next-like-google.zip">Download</a></p>
<p> <!-- #comments --> </p>
<div class="related-articles">
<h2>Popular Articles</h2>
</p></div>
<p> <a href="https://phppot.com/php/how-to-add-pagination-in-php-with-mysql/#top" class="top">↑ Back to Top</a> </p>
</div>
https://www.sickgaming.net/blog/2019/12/...ke-google/