Pagination with jQuery, AJAX and PHP

Share this article

In this article, we’re going to explain how easy it is to paginate your data set using PHP and AJAX via jQuery. We’re also going to use the Silex framework for simplicity.

jQuery Logo

The data source

First off, we need some data to paginate!

id name age
1 Jamie 43
2 Joe 24
3 Fred 23
4 Clive 92
5 Roy 73
6 Geoff 24
7 Ray 12
8 John 9
9 Pete 32
10 Ralph 34

For the purpose of this example we’re going to use MySQL, but of course we can swap this out easily for SQLite or any other RDBMS. We’re going to name our database example and the table will be named people.

The backend

As we’re going to build our application on Silex, we need to install Silex using Composer first. Install it through composer with the command composer require silex/silex.

Next we need set up our index.php file, connect to the data source and select the database. We’re going to use PDO for this as it is easy to change to other database software later, and it also handles escaping user input (thus preventing SQL injection attacks). If you’re still stuck on the mysqli or even worse the mysql extension, see this tutorial. We’re going to put the connection in the $app container so it’s easy to use later in our routes.

$app['db'] = function () {
    $host = 'localhost';
    $db_name = 'example';
    $user = 'root';
    $pass = '';

    return new \PDO(
        "mysql:host={$host};dbname={$db_name}",
        $user,
        $pass,
        array(\PDO::ATTR_EMULATE_PREPARES => false)
    );
};

Now that we’re connected to the database, we’re going to provide three routes in our app, which will enable;

  1. Retrieving the section of the result set we want to display
  2. Retrieving the total amount of rows in the result set
  3. Viewing the HTML frontend

The first route is as follows:

$app->get('/data/page/{page_num}/{rows_per_page}', function ($page_num, $rows_per_page) use ($app) {
    $start = ((int)$page_num - 1) * (int)$rows_per_page;
    $total_rows = (int)$rows_per_page;
		
    $stmt = $app['db']->prepare(
        'SELECT
            `name`
        FROM
            `people`
        ORDER BY
            `name`
        LIMIT
            :from, :total_rows'
    );
    $stmt->bindParam('from', $start);
    $stmt->bindParam('total_rows', $total_rows);
    $stmt->execute();

    $result = $stmt->fetchAll(\PDO::FETCH_ASSOC);
    return $app->json($result);
});

This enables our frontend to grab a subset of the result set from the database. Two parameters can be provided through the URI; one for the page number and one for how many rows should be on each page. The page number is used alongside the rows per page to work out which row in the result set we need to start retrieving data from.

In this example we are going to paginate all data from the table. However in a real application we will likely need to include a WHERE clause to filter the data returned. For example, if we wanted to just display people who are younger than 30, we would amend the above code to include a WHERE clause in the query:

$stmt = $app['db']->prepare(
    'SELECT
        `name`
    FROM
        `people`
    WHERE
        `age` < 30
    ORDER BY
        `name`
    LIMIT
        :from, :total_rows'
);

The query is using a prepared statement to insert the variables for which page number has been requested and how many rows to output per page. These are provided in the URI and then dynamically inserted into a LIMIT clause in the SQL query.

The second route provides the ability to execute a query to return the total amount of rows in the table. This is important because we want to utilize page number links on the frontend. That route should look like this:

$app->get('/data/countrows', function () use ($app) {
    $stmt = $app['db']->query(
        'SELECT
            COUNT(`id`) AS `total_rows`
        FROM
            `people`'
    );

    $result = $stmt->fetch(\PDO::FETCH_ASSOC);
    return $app->json($result);
});

Here we are making use of an aggregate SQL function called COUNT(). This is a GROUP BY function – meaning that it will group the selected rows together to provide a single row. In this case it provides a sum total of all selected rows as an integer.

Another important backend feature to note is that the data fetching routes should return as JSON, as this will ease the integration into the frontend. Silex takes care of this for us using the JSON helper method.

The last route is simply instructing the root URI to output an HTML page.

$app->get('/', function () use ($app) {
    return file_get_contents(__DIR__.'/../resources/views/index.html');
});

Which brings us to…

The frontend

Now the fun bit!

We need to make sure our frontend includes jQuery and has a container for both the page number links and the data itself.

<ul id="rows"></ul>
<ul id="page-numbers"></ul>

<script src="//code.jquery.com/jquery-1.11.2.min.js"></script>

In this example we’re going to use <ul>s, however in a real application we may want to use a <table> for the data (especially important if we want to display more than one piece of information per row).

We’ll need two custom functions in Javascript; one to fetch a specific page, and one to initialize the page number links. In our example we’ve also done a little bit of set up before we get to the main section of code:

var rows_per_page = 3;
var total_rows;

This initializes two global variables for the number of rows we want to show per page and the total amount of rows in the table (the latter will be fetched via AJAX shortly).

To initialize the page number links we’ll need to make an AJAX call to our PHP script to fetch the total amount of rows in the table. Then we’ll use the success function callback to inject the HTML to link to each page into our page number links container depending on the total amount of rows. It should look something like this:

function initPageNumbers()
{
    //Get total rows number
    $.get('data/countrows', function(data){
        total_rows = parseInt(data.total_rows);

        //Loop through every available page and output a page link
        var count = 1;
        for(var x = 0;  x < total_rows; x += rows_per_page)
        {
            $('#page-numbers').append('<li><a href="#'+count+'" onclick="getPage('+count+');">'+count+'</a></li>');
            count++;
        }
    });
}

Note that we’re injecting the HTML for the page links with a Javascript function call already attached. This means that clicking those links will trigger our page fetching function without us attaching a click event manually. Also note that the href attribute is set to a hash fragment with the page number – this means that the URI will be modified to reflect which page number we’re viewing.

The function for fetching a specific page also uses an AJAX call, but has a few key differences;

function getPage(page_num)
{
    //Clear the existing data view
    $('#rows').html('');

    //Get subset of data
    $.get('data/page/'+page_num+'/'+rows_per_page, function(data){
        //Loop through each row and output the data
        $(data).each(function(){
            $('#rows').append('<li>'+this.name+'</li>');
        });
    });					
}

The first difference to note is that the container is cleared first using jQuery’s html() function. This is because this function will be called multiple times, and needs a blank slate to work from each time. The second difference is that the data itself is looped through in the success function callback. This is done using the very useful jQuery each() function.

Both functions use the get() jQuery AJAX shorthand function for making a HTTP GET request. The jQuery ajax() function could be used instead, which allows for more configuration, but the shorthand is just fine for this example.

The getPage() function also differs from the initPageNumbers() function as it passes two parameters along with the HTTP GET request. These are provided in an object as the second parameter of the get() function call. These are then processed by the PHP script as defined previously.

Now all that’s left is to set up the page initialization logic. To do this we’ll be using the jQuery ready() function;

$(document).ready(function(){
    //Set up the page number links
    initPageNumbers();

    //Set the default page number
    var page_num = 1;

    //If there's a hash fragment specifying a page number
    if(window.location.hash !== '')
    {
        //Get the hash fragment as an integer
        var hash_num = parseInt(window.location.hash.substring(1));

        //If the hash fragment integer is valid
        if(hash_num > 0)
        {
            //Overwrite the default page number with the user supplied number
            page_num = hash_num;
        }
    }

    //Load the first page
    getPage(page_num);
});

This calls our page number initialisation function and fetches a page of the result set to display.

If a hash fragment has been provided on the end of the URI then this will be parsed into a page number and set as the page to show, otherwise page 1 is shown by default. This hash fragment functionality lets search engines index our individual pages rather than just the first page. It also allows us to provide external links directly to a specific page number.

An improvement to this technique would be to provide links such as app.com/page/2, app.com/page/3, etc. The benefits to doing this over using hash fragments or query strings (? suffix on the URI) is that it is better supported by search engines and website crawlers.

One thing to note about our page initialisation logic is that the two AJAX operations will happen asynchronously, meaning that the browser will go off and fetch a page of the data at the same time as it’s figuring out how many page links to display. This is great for user experience as it means less time to wait before getting a full experience.

Closing remarks and alternative solutions

The technique of paginating a result set using AJAX is best suited to large result sets, where the amount of data is so large that it is either:

  1. Detrimental to browser performance to include and manipulate all of the result set in the DOM at once, or;
  2. Very slow to fetch and receive the entire result set from the RDBMS in one query.

If the result set is not large enough to cause issues such as these then the preferrable technique would be to load the entire result set into the DOM on page load, and then use Javascript to paginate the data in the UI using a show/hide CSS technique.

And that’s all there is to it! If you would like to view and download the full source code for the example described you can do so via this GitHub repo.

Frequently Asked Questions (FAQs) about Pagination with jQuery, AJAX, and PHP

How can I implement AJAX pagination with jQuery and PHP?

Implementing AJAX pagination with jQuery and PHP involves several steps. First, you need to create a database and a table with data. Then, you need to connect your PHP file to the database. After that, you need to create a PHP file that will fetch data from the database and display it on the page. You will also need to create a jQuery script that will handle the pagination functionality. This script will send an AJAX request to the PHP file and update the page content without reloading it.

What are the benefits of using AJAX for pagination?

AJAX allows you to update a part of a web page without reloading the whole page. This results in a faster and smoother user experience. It also reduces the load on the server, as only a part of the page is requested and sent to the client.

Can I use AJAX pagination with other programming languages?

Yes, you can use AJAX pagination with any server-side programming language that can handle HTTP requests and generate HTML or JSON data. This includes languages like Python, Ruby, Java, and .NET, among others.

How can I handle errors in AJAX pagination?

You can handle errors in AJAX pagination by using the error callback function provided by jQuery’s AJAX method. This function will be called if the AJAX request fails for any reason. You can use this function to display an error message to the user or to log the error for debugging purposes.

How can I customize the look and feel of the pagination links?

You can customize the look and feel of the pagination links by using CSS. You can change the color, font, size, and other properties of the links. You can also add hover effects and transitions to make the pagination more interactive.

How can I add a loading spinner while the AJAX request is being processed?

You can add a loading spinner by using the beforeSend and complete callback functions provided by jQuery’s AJAX method. In the beforeSend function, you can show the spinner, and in the complete function, you can hide it.

Can I use AJAX pagination with a large dataset?

Yes, you can use AJAX pagination with a large dataset. However, you need to be careful not to fetch all the data at once, as this can slow down the server and the client. Instead, you should fetch and display only a small portion of the data at a time.

How can I make the AJAX pagination SEO-friendly?

Making AJAX pagination SEO-friendly can be a bit tricky, as search engines may not be able to crawl and index the content loaded via AJAX. However, you can use techniques like progressive enhancement and pushState to make the AJAX content crawlable and indexable.

Can I use AJAX pagination with a dynamic dataset?

Yes, you can use AJAX pagination with a dynamic dataset. You just need to make sure to update the pagination links whenever the dataset changes.

How can I test the AJAX pagination?

You can test the AJAX pagination by using tools like Postman or curl to send HTTP requests to the server and check the response. You can also use the network tab in the browser’s developer tools to inspect the AJAX requests and responses.

Jamie ShieldsJamie Shields
View Author

Jamie is co-founder and CTO at Flaunt Digital. An honest, innovative digital agency based in Leeds.

ajaxBrunoSframeworkjQueryOOPHPPHPphp frameworkphp frameworkssilex
Share this article
Read Next
Get the freshest news and resources for developers, designers and digital creators in your inbox each week
Loading form