article

Wednesday, August 30, 2023

PHP Mysql Jquery Ajax Pagination

PHP Mysql Jquery Ajax Pagination

Create Database And Table
CREATE TABLE `country` (
  `id` int(6) NOT NULL,
  `country` varchar(250) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

https://gist.github.com/adhipg/1600028#file-countries-sql

Create a Database Connection File db.php

?
1
2
3
4
5
6
7
8
9
10
11
//db.php
<?php
    $servername='localhost';
    $username='root';
    $password='';
    $dbname = "devdb";
    $conn=mysqli_connect($servername,$username,$password,"$dbname");
      if(!$conn){
          die('Could not Connect MySql Server:' .mysql_error());
        }
?>
Download simple-bootstrap-paginator Plugin https://github.com/jorgefernando1/simple-bootstrap-paginator

Create List File ajax-pagination.php
?
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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
//ajax-pagination.php
<!DOCTYPE html>
<html>
<head>
    <title>PHP Mysql Jquery Ajax Pagination</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css">
    <script src="https://cdn.jsdelivr.net/npm/jquery@3.7.0/dist/jquery.min.js"></script>
    <script src="simple-bootstrap-paginator.js"></script>
</head>
<body>
<div class="container">  
    <?php
        include_once("db.php");
        $perPage = 5;
        $sqlQuery = "SELECT * FROM country";
        $result = mysqli_query($conn, $sqlQuery);
        $totalRecords = mysqli_num_rows($result);
        $totalPages = ceil($totalRecords/$perPage);
    ?>
    <h2 class='w-100 d-flex justify-content-center p-3'>PHP Mysql Jquery Ajax Pagination</h2>
    <div class="row">
        <table class="table table-hover table-bordered">
            <thead>
                <tr>
                    <th>Id</th>
                    <th>Name</th>
                </tr>
            </thead>
            <tbody id="content">    
            </tbody>
        </table>  
        <div id="pagination"></div>   
        <input type="hidden" id="totalPages" value="<?php echo $totalPages; ?>">   
    </div>   
</div>
<script>
    $(document).ready(function(){
    var totalPage = parseInt($('#totalPages').val());  
    var pag = $('#pagination').simplePaginator({
        totalPages: totalPage,
        maxButtonsVisible: 5,
        currentPage: 1,
        nextLabel: 'Next',
        prevLabel: 'Prev',
        firstLabel: 'First',
        lastLabel: 'Last',
        clickCurrentPage: true,
        pageChange: function(page) {        
            $("#content").html('<tr><td colspan="6"><strong>loading...</strong></td></tr>');
            $.ajax({
                url:"get_data.php",
                method:"POST",
                dataType: "json",      
                data:{page: page},
                success:function(responseData){
                    console.log(responseData);
                    $('#content').html(responseData.html);
                }
            });
        }
    });
});
</script>
</body>
</html>
Bootstrap 5
https://getbootstrap.com/docs/5.0/getting-started/introduction/
https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css

Jquery
https://jquery.com/download/
CDN : jsDelivr CDN
https://www.jsdelivr.com/package/npm/jquery
https://cdn.jsdelivr.net/npm/jquery@3.7.0/dist/jquery.min.js

Create Get Data PHP File get_data.php
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
// get_data.php
<?php
include_once("db.php");
$perPage = 10;
if (isset($_POST["page"])) {
    $page  = $_POST["page"];
} else {
    $page=1;
}; 
$startFrom = ($page-1) * $perPage; 
$sqlQuery = "SELECT * FROM country ORDER BY id ASC LIMIT $startFrom, $perPage"; 
$result = mysqli_query($conn, $sqlQuery);
$paginationHtml = '';
while ($row = mysqli_fetch_assoc($result)) { 
    $paginationHtml.='<tr>'; 
    $paginationHtml.='<td>'.$row["id"].'</td>';
    $paginationHtml.='<td>'.$row["country"].'</td>';
    $paginationHtml.='</tr>'; 
}
$jsonData = array(
    "html"  => $paginationHtml, 
);
echo json_encode($jsonData);
?>
Posted by ednalan at 11:12 PM . Under , .

Related Post