jQuery Ajax Pagination with PHP & MySql

Skill Level: Intermediate 

 

This is something I would like to cover with vanilla JavaScript as well as I feel like that will give a deeper understand of some programming concepts and understanding what's going on behind the scenes with jQuery. For now though, let's build an awesome pagination system that run flawlessly with jQuery ajax and PHP. 

When we have a large amount of records that we want to display on our website or web app, we need to be able to control how many records are shows at a time. You could always write a Limit clause in your SQL function, but that will only limit the amount of records shown. It will not let you cycle through all of the records, which is what we want to achieve! There are a lot of tutorials our there. Some simple to understand and some not so simple. Some keep the project extremely vague and some get too complicated, but without the needed explanation. We're going to make a somewhat advanced pagination simple, but I'll break down the code during each step. Some knowledge of PHP and jQuery and basic knowledge of Ajax is preferred to really understand all of the concepts covered in this project.

Let's get to it!

First things first. Prep work. Make sure you have a database connection setup, either on your hosting server or on you local server with Xampp or Wamp. Xampp is preferred as it runs on Windows, Linux, and Mac. If you need to get it installed, stop here and get your localhost setup. I'm not going to go into detail on how to set it up, but I will provide the link for the download site.

https://www.apachefriends.org/index.html

All good to go? Good! Here we go!

 

Step 1: Create Database and Insert Data

In my Xampp phpMyAdmin, I've created a database called pagination. Inside is a table called users and I created 4 columns. id, first_name, last_name, age. This will be the data that we pull from to paginate. Here is the SQL statement I used to create my data. The `id` column is set to the primary key on auto increment. 

INSERT INTO users (first_name, last_name, age) VALUES ( 'John', 'Smith', '32');
INSERT INTO users (first_name, last_name, age) VALUES ( 'Susie', 'Smith', '30');
INSERT INTO users (first_name, last_name, age) VALUES ( 'Jane', 'Doe', '24');
INSERT INTO users (first_name, last_name, age) VALUES ( 'Kim', 'Kardashian', '52');
INSERT INTO users (first_name, last_name, age) VALUES ( 'Kanye', 'West', '54');
INSERT INTO users (first_name, last_name, age) VALUES ( 'Ozzie', 'Ozborne', '64');
INSERT INTO users (first_name, last_name, age) VALUES ( 'Liv', 'Tyler', '44');
INSERT INTO users (first_name, last_name, age) VALUES ( 'Brett', 'Favre', '46');
INSERT INTO users (first_name, last_name, age) VALUES ( 'Joe', 'Montana', '58');

Step 2: Create Database Connection with PDO

Create a new document called index.php and another one called db.php. We will add a basic table structure to our index page to hold our data. In this index file, you'll need to link bootstrap and jQuery in the <head> section of your index page.

In this database connection, we create our php constants using the define() function. It takes three parameters, two of which are mandatory. In the first set of quotes, we name our constant, and in the second set of quotes we give it our value. We then create our $dsn variable which represents our "data source name". We give it our driver which in our case is mysql, followed by a colon and then the PDO syntax. When connecting to PDO, we create a new instance of PDO via driver invocation. Inside the new PDO instance, we pass in our $dsn variable, then our DB_USER constant, DB_PASS constant and then our $options array which just gives PDO some default settings.

<?php

define("DB_HOST", "localhost");
define("DB_USER", "root");
define("DB_PASS", "");
define("DB_NAME", "pagination");
define("DB_CHARSET", "utf8mb4");

$dsn = "mysql:host=" . DB_HOST . ";" . "dbname=" . DB_NAME . ";" . "charset=" . DB_CHARSET . "";

$options = [
    PDO::ATTR_ERRMODE               => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE    => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES      => false,
];

try {
    $pdo = new PDO($dsn, DB_USER, DB_PASS, $options);
}

catch (\PDOException $e) {
    throw new \PDO($e->getMessage(), (int)$e->getCode());
    
}

$con = $pdo;

if($pdo) {
    echo 'connected'; // if DB connection is true
} else {
    print_r($con->errorInfo()); // print error information if error with db connection
}

?>

 

 Step 3: Create our Index.php page

Inside our index.php page, we need to include our db.php file. This is a basic html skeleton with the php include at the top and our bootstrap link in the head section and our jQuery script just before the </body>. Our <div id="results"></div> will hold our data that will dynamically be generated with Ajax!

 

<?php include "db.php"; //Include your Database File?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0";>
    <title>jQuery, PHP, MySql, Ajax pagination!</title>
    <link rel="stylesheet" href="/styles.css" type="text/css">
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">
    
        
</head>
<body>
   

   <div class="container">
     
      <div id="results"></div>
   </div>
    
    

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js" integrity="sha384-wfSDF2E50Y2D1uUdj0O3uMBJnjuUD4Ih7YwaYd1iqfktj0Uod8GCExl3Og8ifwB6" crossorigin="anonymous"></script>
<script src="/scripts.js" type="text/javascript"></script>    
</body>
</html>

 

Step 4: Create JavaScript file.

We are going to create our JavaScript file that will hold our jQuery and Ajax. Create a file called scripts.js and put it at the BOTTOM of the other script files. All external JavaScript files that require jQuery need to be loaded after the jQuery API script.

Introducing our getResults() function.

In this file, we are going to start off by creating a function called getResults() and we are going to immediately call that function. Inside of that function we need to get the ID of the results div where our table with our data and pagination will be shown. Then we will create our Ajax function with a 'POST' request and we are going to send the data to our pagination.php page where we will get our results. We will create that page shortly. 

Let's start writing our JavaScript.

 

//create function that will run automatically on page load.

$(document).ready(function() {
    
    
getResults(1); //call function on page load with the default page which will be 1.

function getResults(page) { //Pass in the page argument into the function. This will be the ID of the page number later
    
    var results = $("#results"); // Our results DIV that will store our results
    
    $.ajax({
        type:'post',
        url:'pagination.php',
        data:{page:page},
               
    })
    
    .done(function(data) {
        
        console.log(data); // Log your data to the console if you want to check for errors or easily what data is being returned
        
        $(results).html(data); // Target the results div and load the data
        
    });
};
    
});

 

Step 5: Create pagination.php 

So far, all we have done is create a function that sends an Ajax request that sends our default page number which is 1.  In our Ajax request, we set the url to go to pagination.php. Let's create that now so we have a place to parse our data. In this file, we are going to achieve several things. 

1. Set our max page records to be shown on a page.

2. Create our starting records by setting our offset and count in our LIMIT clause.

3. Create a query that selects our data from the database and set our data to an array so we can loop through it.

4. Create our $output variable which will hold our table data structure.

5. Create a second query where we get the total number of pages and create the pagination.

 

It's not as complicated as it sounds!

 

Here we have set our total number of records to show to 4. We also made sure that if there is no POST data, that our default page number is set to 1, and if there is POST data, then our $page variable is equal to that data. 

When we go to query our database, we will need to set our offset and count in our LIMIT clause. This will determine how many records are shown on each page and how many records we need to offset by, or skip on each page. We will create our $start_from variable and set it to $page - 1 and multiply it by the total records per page. 

Example:

$start_from = ($page - 1) * $records_per_page;  This would translate to  $start_from =  (1 - 1) * 4 which equals 0; This means on our first page, we would offset the records by 0 which means we will be showing the first set of records. If we did $start_from = (2 - 1) * 4, then this equals 4 which means we would offset or skip the first 4 records. This is how the new pages are created! Cool!

 

include "db.php";


$records_per_page = 4; //Set the max amount of records to show per page

$page = ""; //Set $page variable to blank
$output = ""; // Set $output variable to blank

if($_POST['page']) {  //If page data is sent from ajax, set $page variable to our Post data
    
   $page = $_POST['page'];

} else {

    $page = 1;
}

$start_from = ($page - 1) * $records_per_page; // Set the starting record for each page. This creates the offset & count for our LIMIT clause.

 

So now that we have our records per page set and our POST data and our start records all set, it's time to pass this data into our query! In our Query, we're going to pass in our two variable, $start_from and $records_per_page.

We are also going to create the mark-up for our <table> to hold our data for each page. Then, create two empty arrays. One to store the data with our offset record data, and the second will store all records so we can get the count for our pagination.

After that, we need to fetch our data using a while loop and append a new empty array to our $obj_array variable which gives us a structured associative array.

Once we have that set, we pass in our $obj_array into our foreach loop so we can select the individual indexes that we need. Since it's an associative array, we can select it by name.

 

$stmt = $pdo->query("SELECT * FROM users LIMIT $start_from, $records_per_page"); // Query the database with our offset and count variables


$output = "<table id='results-table'class='table table-bordered'>
            <thead>User Data</thead>
             <tr>
               <th>First Name</th>
               <th>Last Name</th>
               <th>Age</th>
             </tr>";

$obj_array = []; //Create empty array to hold data from first query
$obj_array2 = []; // Create another empty array to hold data from second query

while($row = $stmt->fetch()) {
  
    $obj_array[] = $row; // Create a new array for each row of data and append it to our $obj_array variable.
        
}

foreach($obj_array as $arr) {
    
    $fn = $arr['first_name']; //Set first name variable to the `first_name` column
    $ln = $arr['last_name'];  //Set last name variable to the `last_name` column
    $age = $arr['age'];       //Set age variable to the `age` column
    $output .= "<tr>          
                <td>$fn</td>
                <td>$ln</td>
                <td>$age</td>
                </tr>";
}
$output .= "</table>";

 

You can echo our data from $obj_array so you can see what our associate arrays look like.

echo '<pre>',print_r($obj_array),'</pre>';

This statement will give us the following results.

 

Array
(
    [0] => Array
        (
            [id] => 1
            [first_name] => John
            [last_name] => Smith
            [age] => 32
        )

    [1] => Array
        (
            [id] => 2
            [first_name] => Susie
            [last_name] => Smith
            [age] => 30
        )

    [2] => Array
        (
            [id] => 3
            [first_name] => Jane
            [last_name] => Doe
            [age] => 24
        )

    [3] => Array
        (
            [id] => 4
            [first_name] => Kim
            [last_name] => Kardashian
            [age] => 52
        )

)

 

As you can see, we now have a well structured associative array where we can easily pull our data and assign it to variables to use in our table structure. 

Now that we have that information, we need to query the database again. This is where we create our pagination numbers. The first query is responsible for selecting and limiting our data for each page. This query is responsible for selecting all data without a LIMIT clause so we can display the proper count of all pages. If we tried to do this with the first query, it would only display the first page of results.

We will first create a new query and select all from our users table. Once we have the query set, we need to fetch the data in a while loop and assign each row to our $obj_array2 array variable. 

Introducing the PHP count() and ceil() functions.

Once we have the data, we need to get the count of all of the records buy using the PHP count() function and set it to our $total variable. This will give us the count of the total number of records.

Now create a variable called $records. We are going to set this variable to use the ceil() function which rounds up a number that is passed in as a parameter. We are going to pass in our $total/$records_per_page. This is going to divide our total records by the amount of records we want to show per page.

Next, we need to create a for loop. This loop will be responsible for creating our pagination numbers. We set $i to equal 1, and if $i is less than our $records, then we want to increment $i. In Our case, this should give us 3 pages.

All we need to do after this is echo our $output variable.

Easy right?! 

 

$stmt2 = $pdo->query("SELECT * FROM users"); //Query the database again to get all records

while($row = $stmt2->fetch()) {
    
    $obj_array2[] = $row; // Create a new array for each row of data and append to our $obj_array2 array variable.
    
};

$total = count($obj_array2); // Get the total count of our records

$records = ceil($total/$records_per_page); // Get our total number of pages

$previous = $page - 1; //Create our Previous and Next buttons. If $page is less than total records, subtract 1.

if($page < $records) { 
    
    $next = $page + 1; //Create our Previous and Next buttons. If $page is less than total records, add 1.
} else {
    
    $next = $page;  
}


$output .= "<div id='pag-container'>";
for($i = 1; $i <= $records; $i++) {        // Create a for loop to loop through our total pages and create the pagination links
    
     if($i == $page) {
        $output .= "<span class='pag-link active' id='$i'>$i</span>"; //Add active class to current page
    } else {
        $output .= "<span class='pag-link' id='$i'>$i</span>";
    }
}
$output .= "</div>";

echo $output;

 

Almost done! So far we are only getting the results for the first page. We are displaying all of the pagination numbers, but we need to pass in the ID of each number into our ajax on our scripts.js page. Go back to your scripts page and add this to the .done() callback.

We need to get the ID of the pagination number that is being clicked on and pass that ID into our getResults(page) function. 

 

    .done(function(data) {
        
        console.log(data); // Log your data to the console if you want to check for errors or easily what data is being returned

        $(results).html(data); // Target the results div and load the data
       
        $(".pag-link").on("click", function() {
          
            let page = $(this).attr("id"); //Get ID of clicked pagination number
            
            getResults(page); //pass the ID into our function
            
                
      });      
    });

 

At this point everything should be working, but it needs some styling...so let's create our styles.css page! We're also going to take it the next level and add a basic animation!

We need to center align our pagination and give it some margin, padding and some color. We also need to set our 'active' class with a different background so we know what page we are on.

First, let's add a piece of code to our scripts.js page so we can add our animation. We're going to use the setTimeout() function and set the duration to 200 miliseconds. This will allow the animation to play rather fast.

Add this directly after $(results).html(data).

 

 $("#results-table").addClass("animate");
    
            setTimeout(function() {
                $("#results-table").removeClass("animate");
            }, 200);

 

Now it's time to wrap it up and add our CSS styles.

 

#pag-container {
    text-align: center;
}

.pag-link {
    padding: 10px;
    background: #709cff;
    color: #fff; 
    margin: 3px;
    border-radius: 5px;
    box-shadow: 0px 4px 3px -3px #444;
    cursor: pointer;

}
#results {
    opacity:1;
}
.animate {
    animation-name:fade;
    animation-duration: .2s;
    animation-iteration-count: 1;
    
}
.active {
    background: #1a376b;
}

@keyframes fade {
    0% {
        opacity:1;
    }
    50% {
        opacity:0;
    }
    100% {
        opacity:1;
    }
}

 

That's it! You can feel free to add more styles to the table or the pagination as you wish. You can play around with the code and destruct it a bit and see how things work, or maybe even find a new approach to coding the project. If you do, I would love to hear about it! If you have any questions about the project, drop a line below or send me a message and I'll respond as soon as I can.

Cheers!

 

 

Comments (0)

Rated 0 out of 5 based on 0 voters
There are no comments posted here yet

Leave your comments

  1. Posting comment as a guest. Sign up or login to your account.
Rate this post:
Attachments (0 / 3)
Share Your Location
© 2019 The Code Crypt. All Rights Reserved.