MySQL and Ajax Pagination with PHP

PHP MySQL jQuery Ajax Pagination

PHP mysql jquery ajax pagination. how to create jQuery ajax pagination with PHP + MySQL without reload or refresh the whole web page.

Sometimes, your MySQL database contains thousands of records; And you are showing a list of them from MySQL database in the PHP app and you want to see only a few records and data on one page. So for this, you can show the data by using Pagination in Php Mysql with Jquery Ajax.

Ajax Pagination with PHP and MySQL

Follow the below steps and implement advanced jQuery ajax pagination + bootstrap with PHP and MySQL;

  • Step 1 – Create Database And Table
  • Step 2 – Create a Database Connection File
  • Step 3 – Download simple-bootstrap-paginator Plugin
  • Step 4 – Create List File
  • Step 5 – Create Get Data PHP File

Step 1 – Create Database And Table

First of all, navigate to your phpmyadmin panel and create database and table using the following sql queries:

CREATE DATABASE my_db;
 
CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `skills` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `gender` varchar(255) NOT NULL,
  `designation` varchar(255) NOT NULL,
  `age` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Then insert few records into users table to display records with pagination; so use the following SQL query for that:

INSERT INTO `users` (`id`, `name`, `skills`, `address`, `gender`, `designation`, `age`) VALUES
(1, 'John', 'C', 'Newyork', 'Male', 'Software Engineer', 25),
(2, 'David', 'PHP', 'London', 'Male', 'Web Developer', 20),
(3, 'Rhodes', 'jQuery', 'New Jersy', 'Male', 'Web Developer', 30),
(4, 'Meera', 'JavaScript', 'Delhi', 'Female', 'Web Developer', 38),
(5, 'Shyrlin', 'NodeJS', 'Tokiyo', 'Female', 'Programmer', 35),
(6, 'Steve', 'Angular', 'London', 'Male', 'Web Developer', 28),
(7, 'Kelvin', 'MySQL', 'Paris', 'Male', 'Web Developer', 23),
(8, 'Root', 'HTML', 'Paris', 'Male', 'Web Developer', 20),
(9, 'William', 'jQuery', 'Sydney', 'Male', 'Web Developer', 23),
(10, 'Nathan', 'PHP', 'London', 'Male', 'Web Developer', 35),
(11, 'Shri', 'PHP', 'Delhi', 'Male', 'Web Developer', 38),
(12, 'Jay', 'PHP', 'Delhi, India', 'Male', 'Web Developer', 32);

Step 2 – Create a Database Connection File

In this step, you will create a file name db.php and add the following code into your file.

The below code is used to create a MySQL database connection in PHP. When you insert form data into MySQL database, there you will include this file:

<?php
    $servername='localhost';
    $username='root';
    $password='';
    $dbname = "my_db";
    $conn=mysqli_connect($servername,$username,$password,"$dbname");
      if(!$conn){
          die('Could not Connect MySql Server:' .mysql_error());
        }
?>

Step 3 – Download simple-bootstrap-paginator Plugin

In this step, download the jQuery simple bootstrap pagination plugin and place it into your PHP project directory. Then include Bootstrap, jQuery and pagination plugin files into your list.php file.

Step 4 – Create List File

In this step, you need to create a file which names list.php and add the following code into it:

<!DOCTYPE html>
<html>
<head>
    <title>php mysql jquery ajax pagination - CodeOne</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap-theme.min.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
    <script src="plugin/simple-bootstrap-paginator.js"></script>
    <script src="js/pagination.js"></script>
</head>
<body>
<div class="container">   
    <?php
        include_once("db.php");
        $perPage = 5;
        $sqlQuery = "SELECT * FROM users";
        $result = mysqli_query($conn, $sqlQuery);
        $totalRecords = mysqli_num_rows($result);
        $totalPages = ceil($totalRecords/$perPage);
    ?>
    <div class="row">
        <table class="table table-hover table-bordered">
            <thead>
                <tr>
                    <th>Id</th>
                    <th>Name</th>
                    <th>Age</th>
                    <th>Address</th>
                    <th>Skills</th>
                    <th>Designation</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){
                    $('#content').html(responseData.html);
                }
            });
        }
    });
});
</script>
</body>
</html>

Step 5 – Create Get Data PHP File

In this step, you need to create a new file name get_data.php and add the below code into your get_data.php file.

<?php
include_once("db.php");
$perPage = 5;
if (isset($_GET["page"])) { 
    $page  = $_GET["page"]; 
} else { 
    $page=1; 
};  
$startFrom = ($page-1) * $perPage;  
$sqlQuery = "SELECT id, name, age, address, skills, designation
    FROM users 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["name"].'</td>';
    $paginationHtml.='<td>'.$row["age"].'</td>'; 
    $paginationHtml.='<td>'.$row["address"].'</td>';
    $paginationHtml.='<td>'.$row["skills"].'</td>';
    $paginationHtml.='<td>'.$row["designation"].'</td>'; 
    $paginationHtml.='</tr>';  
} 
$jsonData = array(
    "html"  => $paginationHtml,  
);
echo json_encode($jsonData); 
?>

The above below code is used to fetch data from MySQL database using PHP server. And return data to HTML page using jQuery ajax pagination + bootstrap library.

Conclusion

PHP MySQL jquery ajax pagination. Through this tutorial, you have learned how to create jQuery ajax pagination with PHP + MySQL without reload or refresh the whole web page.

2 thoughts on “MySQL and Ajax Pagination with PHP

  • April 23, 2022 at 1:55 am
    Permalink

    What a stuff of un-ambiguity and preserveness of valuable know-how
    regarding unexpected emotions.

    Reply

Leave a Reply

Your email address will not be published.