This post helps you to make Pagination with Jquery, PHP , Ajax and MySQL.
Database
MySQL messages table contains two columns msg_id and message
1 2 3 4 5 | CREATE TABLE messages ( msg_id INT PRIMARY KEY AUTO_INCREMENT, message VARCHAR(150) ); |
JavaScript Code
This script works like a data controller.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | <script type="text/javascript" src="http://ajax.googleapis.com/ ajax/libs/jquery/1.4.2/jquery.min.js"></script> <script type="text/javascript"> $(document).ready(function() { function loading_show() { $('#loading').html("<img src='images/loading.gif'/>").fadeIn('fast'); } function loading_hide() { $('#loading').fadeOut(); } function loadData(page) { loading_show(); $.ajax ({ type: "POST", url: "load_data.php", data: "page="+page, success: function(msg) { $("#container").ajaxComplete(function(event, request, settings) { loading_hide(); $("#container").html(msg); }); } }); } loadData(1); // For first time page load default results $('#container .pagination li.active').live('click',function(){ var page = $(this).attr('p'); loadData(page); }); }); </script> |
load_data.php
Contains PHP coding. Displaying data from the messages table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | <?php include('db.php'); if($_POST['page']) { $page = $_POST['page']; $cur_page = $page; $page -= 1; $per_page = 15; // Per page records $previous_btn = true; $next_btn = true; $first_btn = true; $last_btn = true; $start = $page * $per_page; include"db.php"; $query_pag_data = "SELECT msg_id,message from messages LIMIT $start, $per_page"; $result_pag_data = mysqli_query($query_pag_data); $msg = ""; while ($row = mysqli_fetch_array($result_pag_data,MYSQLI_ASSOC)) { $htmlmsg=htmlentities($row['message']); //HTML entries filter $msg .= "<li><b>" . $row['msg_id'] . "</b> " . $htmlmsg . "</li>"; } $msg = "<div class='data'><ul>" . $msg . "</ul></div>"; // Content for Data /* -----Total count--- */ $query_pag_num = "SELECT COUNT(*) AS count FROM messages"; // Total records $result_pag_num = mysqli_query($query_pag_num); $row = mysqli_fetch_array($result_pag_num,MYSQLI_ASSOC); $count = $row['count']; $no_of_paginations = ceil($count / $per_page); /* -----Calculating the starting and endign values for the loop----- */ //Some Code. Available in download script } ?> |
db.php
Database configuration file, modify username, password and database values.
1 2 3 4 5 6 7 | <?php define('DB_SERVER', 'localhost'); define('DB_USERNAME', 'username'); define('DB_PASSWORD', 'password'); define('DB_DATABASE', 'database'); $db = mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE); ?> |