Thursday 18 August 2016

Make Pagination using Ajax with Jquery, PHP and MySQL



In this post we are going to learn how to create ajax pagination without page refresh by using Ajax with Jquery, PHP and Mysql. Ajax Pagination is most helpful to display set of table data with no any page reload event. Inside ajax pagination ajax call will deliver asynchronous manner for that web ajax call will do work coordinate with another ajax call.

In most of the web based application, pagination or pager is a most necessary part where large numbers of data are fetched from the database table. In those type of case, Pagination with Ajax is a excellent style this is because it will relief to develop the User Interface of your web based application. In This post, I will display how can you apply the Ajax pagination in PHP using jQuery and MySQL. I have created the very simple but powerful pagination script to make pagination with jQuery, Ajax, PHP, and MySQL. I have using php and I will fetch the student data from the MySQL database with the pagination links and this pagination links, you can pull the database table data except the visibled data. jQuery and Ajax will be helpful to fetch the data from the database table as per on pagination links without refreshing whole the page. I have describe complete in this video tutorial, you can see the video and learn from this video tutorial how to create pagination by using Ajax with Jquery php and mysql. I have also provide source code here also.


Source Code

Database


 --  
 -- Table structure for table `tbl_student`  
 --  
 CREATE TABLE IF NOT EXISTS `tbl_student` (  
  `student_id` int(11) NOT NULL AUTO_INCREMENT,  
  `student_name` varchar(250) NOT NULL,  
  `student_phone` varchar(20) NOT NULL,  
  PRIMARY KEY (`student_id`)  
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=17 ;  
 --  
 -- Dumping data for table `tbl_student`  
 --  
 INSERT INTO `tbl_student` (`student_id`, `student_name`, `student_phone`) VALUES  
 (1, 'Pauline S. Rich', '412-735-0224'),  
 (2, 'Sarah C. White', '320-552-9961'),  
 (3, 'Samuel L. Leslie', '201-324-8264'),  
 (4, 'Norma R. Manly', '478-322-4715'),  
 (5, 'Kimberly R. Castro', '479-966-6788'),  
 (6, 'Elaine R. Davis', '701-685-8912'),  
 (7, 'Concepcion S. Gardner', '607-829-8758'),  
 (8, 'Patricia J. White', '803-789-0429'),  
 (9, 'Michael M. Bothwell', '214-585-0737'),  
 (10, 'Ronald C. Vansickle', '630-571-4107'),  
 (11, 'Clarence A. Rich', '904-459-3747'),  
 (12, 'Elizabeth W. Peterson', '404-380-9481'),  
 (13, 'Renee R. Hewitt', '323-350-4973'),  
 (14, 'John K. Love', '337-229-1983'),  
 (15, 'Teresa J. Rincon', '216-394-6894'),  
 (16, 'Erin S. Huckaby', '503-284-8652');  

index.php


 <!DOCTYPE html>  
 <html>  
      <head>  
           <title>Webslesson Tutorial | Make Pagination using Jquery, PHP, Ajax and MySQL</title>  
           <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />  
           <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>  
           <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>  
      </head>  
      <body>  
           <br /><br />  
           <div class="container">  
                <h3 align="center">Make Pagination using Jquery, PHP, Ajax and MySQL</h3><br />  
                <div class="table-responsive" id="pagination_data">  
                </div>  
           </div>  
      </body>  
 </html>  
 <script>  
 $(document).ready(function(){  
      load_data();  
      function load_data(page)  
      {  
           $.ajax({  
                url:"pagination.php",  
                method:"POST",  
                data:{page:page},  
                success:function(data){  
                     $('#pagination_data').html(data);  
                }  
           })  
      }  
      $(document).on('click', '.pagination_link', function(){  
           var page = $(this).attr("id");  
           load_data(page);  
      });  
 });  
 </script>  

pagination.php


 <?php  
 //pagination.php  
 $connect = mysqli_connect("localhost", "root", "", "testing");  
 $record_per_page = 5;  
 $page = '';  
 $output = '';  
 if(isset($_POST["page"]))  
 {  
      $page = $_POST["page"];  
 }  
 else  
 {  
      $page = 1;  
 }  
 $start_from = ($page - 1)*$record_per_page;  
 $query = "SELECT * FROM tbl_student ORDER BY student_id DESC LIMIT $start_from, $record_per_page";  
 $result = mysqli_query($connect, $query);  
 $output .= "  
      <table class='table table-bordered'>  
           <tr>  
                <th width='50%'>Name</th>  
                <th width='50%'>Phone</th>  
           </tr>  
 ";  
 while($row = mysqli_fetch_array($result))  
 {  
      $output .= '  
           <tr>  
                <td>'.$row["student_name"].'</td>  
                <td>'.$row["student_phone"].'</td>  
           </tr>  
      ';  
 }  
 $output .= '</table><br /><div align="center">';  
 $page_query = "SELECT * FROM tbl_student ORDER BY student_id DESC";  
 $page_result = mysqli_query($connect, $page_query);  
 $total_records = mysqli_num_rows($page_result);  
 $total_pages = ceil($total_records/$record_per_page);  
 for($i=1; $i<=$total_pages; $i++)  
 {  
      $output .= "<span class='pagination_link' style='cursor:pointer; padding:6px; border:1px solid #ccc;' id='".$i."'>".$i."</span>";  
 }  
 $output .= '</div><br /><br />';  
 echo $output;  
 ?>  

17 comments:

  1. Thank you so much for your post. But I want to do something different. I want pagination comes like - 1 2 3 4 5 ...... 10 20 30 40 50 [next] . because i have lots more data. So can you please give me an idea how can I do this ??

    Thanks again

    ReplyDelete
  2. This is tremendously helpful. Thank you so much!

    ReplyDelete
  3. Click Not Working, Pls help

    ReplyDelete
  4. how to add active class on a span here

    ReplyDelete
  5. Ty works form me i only need to adapt, and do the pagination with boostrap just missed :V

    ReplyDelete
  6. bro can you please teach how to add active class

    ReplyDelete
  7. Thank you so much for your post. But I want to do something different. I want pagination comes like - 1 2 3 4 5 ...... 10 20 30 40 50 [next] . because i have lots more data. So can you please give me an idea how can I do this ??

    Thanks again

    ReplyDelete
  8. I just want to say thanks for all your helpful scripts. Good work!
    Eranda Jayamaha

    ReplyDelete
  9. Awesome Content as always

    ReplyDelete
  10. hi thanks for sharing is its possible to make auto slide of the button to next like
    a carollsell or slider

    ReplyDelete
  11. the best article that i read till today thanks for sharing for this useful information

    ReplyDelete