{"id":404,"date":"2024-02-11T05:01:39","date_gmt":"2024-02-11T05:01:39","guid":{"rendered":"http:\/\/www.phpgang.com\/?p=404---b037e405-4b95-46ec-b998-0cda502f3406"},"modified":"2024-02-11T05:01:39","modified_gmt":"2024-02-11T05:01:39","slug":"how-to-create-advance-pagination-in-php-mysql-with-jquery","status":"publish","type":"post","link":"https:\/\/www.phpgang.com\/how-to-create-advance-pagination-in-php-mysql-with-jquery_404.html","title":{"rendered":"How to create Advanced Pagination in PHP & MySQL with jQuery"},"content":{"rendered":"

We have already created 2 pagination[1<\/a>][2<\/a>]\u00a0articles for PHP<\/a> Developers<\/strong> and this one is the advanced version of pagination with “…” many readers requested<\/a> me to make it with these dots, so today I am going to write this article on advance pagination with jQuery<\/a> and show you how to show small list of numbers with dots in between.<\/p>\n

\"ajax-pagination\"<\/p>\n

[wpdm_file id=52]DEMO<\/a><\/div>\n

Database design and table:<\/strong>
\ndatabase name => phpgang
\ntable name => pagination
\ncolumn names => id, post, postlink
\ndb.sql<\/strong>
\nDatabase file run in your mysql to create database and add data in table.<\/p>\n

create database `phpgang`;\r\nuse `phpgang`;\r\nCREATE TABLE `pagination` (\r\n  `id` int(11) NOT NULL auto_increment,\r\n  `post` varchar(250) NOT NULL,\r\n  `postlink` varchar(250) NOT NULL,\r\n  PRIMARY KEY  (`id`)\r\n) ENGINE=MyISAM AUTO_INCREMENT=48 DEFAULT CHARSET=latin1 AUTO_INCREMENT=48 ;\r\n\r\nINSERT INTO `pagination` VALUES (1, 'How to Resize text using jQuery', 'https:\/\/www.phpgang.com\/?p=312');\r\nINSERT INTO `pagination` VALUES (2, 'How to Integrate live search in PHP and MySQL with jQuery', 'https:\/\/www.phpgang.com\/?p=309');\r\nINSERT INTO `pagination` VALUES (3, 'How to implement jquery Timeago with php', 'https:\/\/www.phpgang.com\/?p=290');\r\nINSERT INTO `pagination` VALUES (4, 'How to Mask an input box in jQuery', 'https:\/\/www.phpgang.com\/?p=304');\r\nINSERT INTO `pagination` VALUES (5, 'How to block Inappropriate content with javascript validation', 'https:\/\/www.phpgang.com\/?p=301');\r\nINSERT INTO `pagination` VALUES (6, 'How to Crop Image with jQuery and PHP', 'https:\/\/www.phpgang.com\/?p=298');\r\nINSERT INTO `pagination` VALUES (7, 'How to Integrate jQuery Scroll Paging with PHP', 'https:\/\/www.phpgang.com\/?p=294');\r\nINSERT INTO `pagination` VALUES (8, 'Bug Reporting with Windows Program Steps Recorder (PSR)', 'https:\/\/www.phpgang.com\/?p=291');\r\nINSERT INTO `pagination` VALUES (9, 'How to Configure Google Cloud API in PHP', 'https:\/\/www.phpgang.com\/?p=288');\r\nINSERT INTO `pagination` VALUES (10, 'How to convert text to MP3 Voice', 'https:\/\/www.phpgang.com\/?p=284');<\/pre>\n
db.php<\/strong>
\nDatabase configuration file edit database name, user and password as per your configuration.<\/p>\n
<?php\r\n$connection = mysqli_connect('localhost','phpgang_usr','******','phpgang') or die(mysqli_error($connection));\r\n?><\/pre>\n
style.css<\/strong>
\nContain styles for your pagination numbers and next and previous.<\/p>\n
<style>\r\ndiv.pagination {\r\npadding: 3px;\r\nmargin: 3px;\r\ntext-align:center;\r\n}\r\n\r\ndiv.pagination a {\r\npadding: 2px 5px 2px 5px;\r\nmargin: 2px;\r\nborder: 1px solid #AAAADD;\r\n\r\ntext-decoration: none; \/* no underline *\/\r\ncolor: #000099;\r\n}\r\ndiv.pagination a:hover, div.digg a:active {\r\nborder: 1px solid #000099;\r\n\r\ncolor: #000;\r\n}\r\ndiv.pagination span.current {\r\npadding: 2px 5px 2px 5px;\r\nmargin: 2px;\r\nborder: 1px solid #000099;\r\n\r\nfont-weight: bold;\r\nbackground-color: #000099;\r\ncolor: #FFF;\r\n}\r\ndiv.pagination span.disabled {\r\npadding: 2px 5px 2px 5px;\r\nmargin: 2px;\r\nborder: 1px solid #EEE;\r\n\r\ncolor: #DDD;\r\n}\r\n\r\n<\/style><\/pre>\n
JQuery Required to handle AJAX functions:<\/p>\n
<script type=\"text\/javascript\" src=\"jquery-1.8.0.min.js\"><\/script>\r\n<script type=\"text\/javascript\">\r\nfunction changePagination(pageId){\r\n     $(\".flash\").show();\r\n     $(\".flash\").fadeIn(400).html\r\n                ('Loading <img src=\"ajax-loader.gif\" \/>');\r\n     var dataString = 'pageId='+ pageId;\r\n     $.ajax({\r\n           type: \"POST\",\r\n           url: \"loadData.php\",\r\n           data: dataString,\r\n           cache: false,\r\n           success: function(result){\r\n           $(\".flash\").hide();\r\n                 $(\"#pageData\").html(result);\r\n           }\r\n      });\r\n}\r\n<\/script><\/pre>\n
loadData.php<\/strong><\/p>\n
This file contain complete Logic of the pagination and in index file we gather all js and css together:<\/p>\n
<?php\r\ninclude_once('db.php');\r\n\r\n$query=\"select id from pagination order by id asc\";\r\n$res    = mysqli_query($connection,$query);\r\n$count  = mysqli_num_rows($res);\r\n$page = (int) (!isset($_REQUEST['pageId']) ? 1 :$_REQUEST['pageId']);\r\n$page = ($page == 0 ? 1 : $page);\r\n$recordsPerPage = 5;\r\n$start = ($page-1) * $recordsPerPage;\r\n$adjacents = \"2\";\r\n\r\n$prev = $page - 1;\r\n$next = $page + 1;\r\n$lastpage = ceil($count\/$recordsPerPage);\r\n$lpm1 = $lastpage - 1;   \r\n$pagination = \"\";\r\nif($lastpage > 1)\r\n    {   \r\n        $pagination .= \"<div class='pagination'>\";\r\n        if ($page > 1)\r\n            $pagination.= \"<a href=\\\"#Page=\".($prev).\"\\\" onClick='changePagination(\".($prev).\");'>&laquo; Previous&nbsp;&nbsp;<\/a>\";\r\n        else\r\n            $pagination.= \"<span class='disabled'>&laquo; Previous&nbsp;&nbsp;<\/span>\";   \r\n        if ($lastpage < 7 + ($adjacents * 2))\r\n        {   \r\n            for ($counter = 1; $counter <= $lastpage; $counter++)\r\n            {\r\n                if ($counter == $page)\r\n                    $pagination.= \"<span class='current'>$counter<\/span>\";\r\n                else\r\n                    $pagination.= \"<a href=\\\"#Page=\".($counter).\"\\\" onClick='changePagination(\".($counter).\");'>$counter<\/a>\";     \r\n\r\n            }\r\n        }   \r\n\r\n        elseif($lastpage > 5 + ($adjacents * 2))\r\n        {\r\n            if($page < 1 + ($adjacents * 2))\r\n            {\r\n                for($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)\r\n                {\r\n                    if($counter == $page)\r\n                        $pagination.= \"<span class='current'>$counter<\/span>\";\r\n                    else\r\n                        $pagination.= \"<a href=\\\"#Page=\".($counter).\"\\\" onClick='changePagination(\".($counter).\");'>$counter<\/a>\";     \r\n                }\r\n                $pagination.= \"...\";\r\n                $pagination.= \"<a href=\\\"#Page=\".($lpm1).\"\\\" onClick='changePagination(\".($lpm1).\");'>$lpm1<\/a>\";\r\n                $pagination.= \"<a href=\\\"#Page=\".($lastpage).\"\\\" onClick='changePagination(\".($lastpage).\");'>$lastpage<\/a>\";   \r\n\r\n           }\r\n           elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))\r\n           {\r\n               $pagination.= \"<a href=\\\"#Page=\\\"1\\\"\\\" onClick='changePagination(1);'>1<\/a>\";\r\n               $pagination.= \"<a href=\\\"#Page=\\\"2\\\"\\\" onClick='changePagination(2);'>2<\/a>\";\r\n               $pagination.= \"...\";\r\n               for($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)\r\n               {\r\n                   if($counter == $page)\r\n                       $pagination.= \"<span class='current'>$counter<\/span>\";\r\n                   else\r\n                       $pagination.= \"<a href=\\\"#Page=\".($counter).\"\\\" onClick='changePagination(\".($counter).\");'>$counter<\/a>\";     \r\n               }\r\n               $pagination.= \"..\";\r\n               $pagination.= \"<a href=\\\"#Page=\".($lpm1).\"\\\" onClick='changePagination(\".($lpm1).\");'>$lpm1<\/a>\";\r\n               $pagination.= \"<a href=\\\"#Page=\".($lastpage).\"\\\" onClick='changePagination(\".($lastpage).\");'>$lastpage<\/a>\";   \r\n           }\r\n           else\r\n           {\r\n               $pagination.= \"<a href=\\\"#Page=\\\"1\\\"\\\" onClick='changePagination(1);'>1<\/a>\";\r\n               $pagination.= \"<a href=\\\"#Page=\\\"2\\\"\\\" onClick='changePagination(2);'>2<\/a>\";\r\n               $pagination.= \"..\";\r\n               for($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)\r\n               {\r\n                   if($counter == $page)\r\n                        $pagination.= \"<span class='current'>$counter<\/span>\";\r\n                   else\r\n                        $pagination.= \"<a href=\\\"#Page=\".($counter).\"\\\" onClick='changePagination(\".($counter).\");'>$counter<\/a>\";     \r\n               }\r\n           }\r\n        }\r\n        if($page < $counter - 1)\r\n            $pagination.= \"<a href=\\\"#Page=\".($next).\"\\\" onClick='changePagination(\".($next).\");'>Next &raquo;<\/a>\";\r\n        else\r\n            $pagination.= \"<span class='disabled'>Next &raquo;<\/span>\";\r\n\r\n        $pagination.= \"<\/div>\";       \r\n    }\r\n\r\nif(isset($_POST['pageId']) && !empty($_POST['pageId']))\r\n{\r\n    $id=$_POST['pageId'];\r\n}\r\nelse\r\n{\r\n    $id='0';\r\n}\r\n$query=\"select post,postlink from pagination order by id asc\r\nlimit \".mysqli_real_escape_string($connection,$start).\",$recordsPerPage\";\r\n\/\/echo $query;\r\n$res    =   mysqli_query($connection,$query);\r\n$count  =   mysqli_num_rows($res);\r\n$HTML='';\r\nif($count > 0)\r\n{\r\n    while($row=mysqli_fetch_array($res))\r\n    {\r\n        $post=$row['post'];\r\n        $link=$row['postlink'];\r\n        $HTML.='<div>';\r\n        $HTML.='<a href=\"'.$link.'\" target=\"_blank\">'.$post.'<\/a>';\r\n        $HTML.='<\/div><br\/>';\r\n    }\r\n}\r\nelse\r\n{\r\n    $HTML='No Data Found';\r\n}\r\necho $HTML;\r\necho $pagination;\r\n?><\/pre>\n
Your request via AJAX function sent to that php file and it will process the request and return you paginated data.<\/p>\n
This line of code return all numbers in the table and show count.<\/p>\n
$query=\"select id from pagination order by id asc\";\r\n$res=mysql_query($query);\r\n$count=mysql_num_rows($res);<\/pre>\n
It will show you 5 records on each page you can increase records and decrease by changing this value\u00a0$start = ($page-1) * 5;<\/em> to any number you want.<\/p>\n
Index file complete code of css and jquery together:<\/p>\n
<!DOCTYPE html PUBLIC \"-\/\/W3C\/\/DTD XHTML 1.0 Strict\/\/EN\"\r\n  \"http:\/\/www.w3.org\/TR\/xhtml1\/DTD\/xhtml1-strict.dtd\">\r\n<html xmlns=\"http:\/\/www.w3.org\/1999\/xhtml\" xml:lang=\"en\" lang=\"en\">\r\n  <head>\r\n    <title>How to create pagination in PHP and MySQL with AJAX | PGPGang.com<\/title>\r\n    <meta http-equiv=\"Content-Type\" content=\"text\/html;charset=utf-8\" \/>\r\n    <script type=\"text\/javascript\" src=\"jquery-1.8.0.min.js\"><\/script>\r\n<style>\r\ndiv.pagination {\r\npadding: 3px;\r\nmargin: 3px;\r\ntext-align:center;\r\n}\r\n\r\ndiv.pagination a {\r\npadding: 2px 5px 2px 5px;\r\nmargin: 2px;\r\nborder: 1px solid #AAAADD;\r\n\r\ntext-decoration: none; \/* no underline *\/\r\ncolor: #000099;\r\n}\r\ndiv.pagination a:hover, div.digg a:active {\r\nborder: 1px solid #000099;\r\n\r\ncolor: #000;\r\n}\r\ndiv.pagination span.current {\r\npadding: 2px 5px 2px 5px;\r\nmargin: 2px;\r\nborder: 1px solid #000099;\r\n\r\nfont-weight: bold;\r\nbackground-color: #000099;\r\ncolor: #FFF;\r\n}\r\ndiv.pagination span.disabled {\r\npadding: 2px 5px 2px 5px;\r\nmargin: 2px;\r\nborder: 1px solid #EEE;\r\n\r\ncolor: #DDD;\r\n}\r\n\r\n<\/style>\r\n\r\n<script type=\"text\/javascript\">\r\n$(document).ready(function(){\r\nchangePagination('0');\t\r\n});\r\nfunction changePagination(pageId){\r\n     $(\".flash\").show();\r\n     $(\".flash\").fadeIn(400).html\r\n                ('Loading <img src=\"ajax-loader.gif\" \/>');\r\n     var dataString = 'pageId='+ pageId;\r\n     $.ajax({\r\n           type: \"POST\",\r\n           url: \"loadData.php\",\r\n           data: dataString,\r\n           cache: false,\r\n           success: function(result){\r\n           $(\".flash\").hide();\r\n                 $(\"#pageData\").html(result);\r\n           }\r\n      });\r\n}\r\n<\/script>\r\n  <\/head>\r\n  <body>\r\n    <h2>How to create pagination in PHP and MySQL with AJAX example.&nbsp;&nbsp;&nbsp;=> <a href=\"https:\/\/www.phpgang.com\/\">Home<\/a> | <a href=\"http:\/\/demo.phpgang.com\/\">More Demos<\/a><\/h2>\r\n\r\n<div id=\"pageData\"><\/div>\r\n<span class=\"flash\"><\/span>\r\n\r\n<\/body>\r\n<\/html><\/pre>\n
Feel free to comment your suggestions and problems you are facing in regard this tutorial.<\/p>\n
Facebook<\/a><\/blockquote><\/div><\/div>
Tutorial Categories:<\/strong>
\n        \"jQuery\"<\/a><\/div>
\n        \"MySQL\"<\/a><\/div>
\n        \"PHP\"<\/a><\/div><\/div>","protected":false},"excerpt":{"rendered":"
We have already created 2 pagination[1<\/a>][2<\/a>]\u00a0articles for PHP<\/a> Developers<\/strong> and this one is the advanced version of pagination with “…” many readers requested<\/a> me to make it with these dots, so today I am going to write this article on advance pagination with jQuery<\/a> and show you how to show small list of numbers with dots in between.<\/p>\n
\"ajax-pagination\"<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"twitterCardType":"","cardImageID":0,"cardImage":"","cardTitle":"","cardDesc":"","cardImageAlt":"","cardPlayer":"","cardPlayerWidth":0,"cardPlayerHeight":0,"cardPlayerStream":"","cardPlayerCodec":"","footnotes":""},"categories":[164,38,4,3],"tags":[166,248,251,249,250,623,22,23,25,21,20,24],"_links":{"self":[{"href":"https:\/\/www.phpgang.com\/wp-json\/wp\/v2\/posts\/404"}],"collection":[{"href":"https:\/\/www.phpgang.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.phpgang.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.phpgang.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.phpgang.com\/wp-json\/wp\/v2\/comments?post=404"}],"version-history":[{"count":0,"href":"https:\/\/www.phpgang.com\/wp-json\/wp\/v2\/posts\/404\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.phpgang.com\/wp-json\/wp\/v2\/media?parent=404"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.phpgang.com\/wp-json\/wp\/v2\/categories?post=404"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.phpgang.com\/wp-json\/wp\/v2\/tags?post=404"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}