Ajax Pagination with PHP and MySQL

Pagination is very useful for a data list where a large number of records are listed from the database. It helps to load the dynamic data faster by dividing records into multiple pages. The pagination functionality can be integrated using PHP easily. In PHP Pagination, the data is loaded by navigating the links with page reload/refresh. If you want to improve the UI of the webpage and make the data list user-friendly, Ajax Pagination is the best choice to load data without page refresh.

Ajax Pagination helps to create pagination links and load dynamic data without page refresh from the database. You can add pagination to the data list without page refresh using Ajax and PHP. In this tutorial, we will show you how to integrate pagination on the web page using jQuery, Ajax, PHP, and MySQL.

In this example we will fetch members’ data from the MySQL database and display it in a table with pagination links. Initially, a limited number of records will be displayed in the table. When you click on the pagination links, the next set of records will be loaded without page refresh. We will use JavaScript to fetch members’ data from the server using Ajax and display it in the table without page reload.

File Structure

Before we start the integration process, let’s see the file structure of this Ajax pagination with PHP and MySQL example.

ajax_pagination_with_php/
├── index.html
├── fetch_members.php
└── css/
    └── style.css

Let’s start the integration process of pagination in PHP using Ajax and JavaScript.

Create Database Table

Create a MySQL database table named members to store members’ data. You can use the following SQL query to create the table.

CREATE TABLE `members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(25) NOT NULL,
  `last_name` varchar(25) NOT NULL,
  `email` varchar(50) NOT NULL,
  `gender` enum('Male','Female') NOT NULL,
  `country` varchar(20) NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Note: Add some dummy records in the members table to test the pagination functionality.

Create Members List Page with Ajax Pagination

Create an HTML file named index.html to list members’ data from the MySQL database with pagination links. In this file, we will create a table to display members’ data and a div to display pagination links. We will use JavaScript to fetch members’ data from the server using Ajax and display it in the table without page reload.

Here is the complete code of the index.html file. We divide the code into two parts: HTML and JavaScript.

HTML Code:
Define the HTML structure to display members’ data in a table and pagination links.

<div class="container">
    <h2>Members List</h2>

    <!-- Loader -->
    <div id="loader">Loading...</div>

    <!-- Members table -->
    <div class="table-responsive">
        <table id="members-table">
            <thead>
                <tr>
                    <th>First Name</th>
                    <th>Last Name</th>
                    <th>Email</th>
                    <th>Gender</th>
                    <th>Country</th>
                    <th>Created</th>
                </tr>
            </thead>
            <tbody></tbody>
        </table>
    </div>

    <!-- Pagination links -->
    <div class="pagination" id="pagination"></div>
</div>

JavaScript Code:
Use JavaScript to fetch members’ data from the server using Ajax and display it in the table without page reload. Also, handle pagination links to navigate through different pages.

• The fetchMembers() function fetches members’ data from the server for a given page number. The function uses the Fetch API to make an asynchronous request to the server-side script (fetch_members.php) and retrieves the data in JSON format. The fetched data is then displayed in the table body, and pagination links are created dynamically based on the total number of pages. Click events on the pagination links are handled to fetch data for the selected page without page reload.

<script>
// Fetch members data and handle pagination
function fetchMembers(page = 1) {
    // Show loader
    document.getElementById('loader').style.display = 'block';
    
    // Fetch data from server
    fetch('fetch_members.php?page=' + page)
    .then(res => res.json())
    .then(data => {
        const tbody = document.querySelector('#members-table tbody');
        tbody.innerHTML = '';
        data.members.forEach(m => {
            tbody.innerHTML += `<tr>
                <td data-label="First Name">${m.first_name}</td>
                <td data-label="Last Name">${m.last_name}</td>
                <td data-label="Email">${m.email}</td>
                <td data-label="Gender">${m.gender}</td>
                <td data-label="Country">${m.country}</td>
                <td data-label="Created">${m.created}</td>
            </tr>`;
        });

        // Pagination with First, Previous, Next, Last
        let pagHtml = '';
        const current = data.current;
        const totalPages = data.pages;

        // First and Previous
        pagHtml += `<button ${current === 1 ? 'disabled' : ''} onclick="fetchMembers(1)">First</button>`;
        pagHtml += `<button ${current === 1 ? 'disabled' : ''} onclick="fetchMembers(${current - 1})">Previous</button>`;

        // Page numbers (show range around current)
        let range = 2;
        let start = Math.max(1, current - range);
        let end = Math.min(totalPages, current + range);
        if (start > 1) {
            pagHtml += `<button onclick="fetchMembers(1)">1</button>`;
            if (start > 2) pagHtml += `<span>...</span>`;
        }
        for (let i = start; i <= end; i++) {
            pagHtml += `<button ${i === current ? 'disabled' : ''} onclick="fetchMembers(${i})">${i}</button>`;
        }
        if (end < totalPages) {
            if (end < totalPages - 1) pagHtml += `<span>...</span>`;
            pagHtml += `<button onclick="fetchMembers(${totalPages})">${totalPages}</button>`;
        }

        // Next and Last
        pagHtml += `<button ${current === totalPages ? 'disabled' : ''} onclick="fetchMembers(${current + 1})">Next</button>`;
        pagHtml += `<button ${current === totalPages ? 'disabled' : ''} onclick="fetchMembers(${totalPages})">Last</button>`;

        // Update pagination
        document.getElementById('pagination').innerHTML = pagHtml;

        // Hide loader
        document.getElementById('loader').style.display = 'none';
    });
}

// Initial fetch on page load
fetchMembers();
</script>

Fetch Members Data from Database

Create a PHP file named fetch_members.php to fetch members’ data from the MySQL database and return it in JSON format. In this file, we will connect to the database, retrieve members’ data based on the requested page number, and return the data along with pagination information.

• Set the header for JSON response using header('Content-Type: application/json');.
• Define database configuration variables such as host ($dbHost), username ($dbUsername), password ($dbPassword), database name ($dbName), and table name ($dbTable).
• Create a database connection using mysqli and check for connection errors.
• Get the page number from the query string using $_GET['page']. If not set, default to page 1.
• Define the number of records to display per page (e.g., 10) in $limit variable.
• Calculate the offset for the SQL query based on the current page number.
• Fetch the total number of records from the database to calculate the total number of pages.
• Fetch members’ data for the current page using a SQL query with LIMIT and OFFSET.
• Store the fetched data in an array and prepare the response with members’ data, current page, and total pages.
• Return the response in JSON format using json_encode().

<?php 
// Set header for JSON response 
header('Content-Type: application/json'); 
 
// Database configuration 
$dbHost     = "localhost"; 
$dbUsername = "root"; 
$dbPassword = "root"; 
$dbName     = "codexworld_db"; 
$dbTable    = 'members'; // Change to your DB table name from which to fetch records 
 
// Create database connection 
$conn = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName); 
 
// Check connection 
if ($conn->connect_error) die("Connection failed"); 
 
// Get page number from query string 
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1; 
if ($page < 1) $page = 1; 
 
// Number of records per page 
$limit = 7; 
 
// Calculate the offset for the SQL query 
$offset = ($page - 1) * $limit; 
 
// Get total records count 
$totalRes = $conn->query("SELECT COUNT(*) as cnt FROM {$dbTable}"); 
$totalRow = $totalRes->fetch_assoc(); 
$total = $totalRow['cnt']; 
$pages = ceil($total / $limit); 
 
// Fetch records with limit and offset 
$sql = "SELECT * FROM {$dbTable} ORDER BY created DESC LIMIT $limit OFFSET $offset"; 
$result = $conn->query($sql); 
 
// Fetch all records and store in an array 
$members = []; 
while ($row = $result->fetch_assoc()) { 
    $members[] = $row; 
} 
 
// Close the database connection 
$conn->close(); 
 
// Return JSON response 
echo json_encode([ 
    'members' => $members, 
    'pages' => $pages, 
    'current' => $page 
]); 
?>

Note: Make sure to replace the database configuration variables with your actual database credentials.

Load more data from Database using jQuery, Ajax and PHP

Conclusion

In this tutorial, we created a simple web application to display members’ data from a MySQL database with Ajax-based pagination using JavaScript and PHP. The application fetches data asynchronously without page refresh, providing a smooth user experience. You can further enhance this application by adding features like search, sorting, or filtering based on your requirements.