Pagination with jQuery, AJAX and PHP

An excerpt from http://www.sitepoint.com/pagination-jquery-ajax-php/, by @jayshields

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.

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;

Retrieving the section of the result set we want to display
Retrieving the total amount of rows in the result set
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…

Continue reading this article on SitePoint!

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.