Create Pagination in PHP and MySQL – Complete code

In this tutorial, I am going to show full code to create pagination with PHP and MySQL. Usually, SQL Queries may return all the records stored in the table as a result. These records may vary from tens to millions of records in result depending on the data stored. It is not a good practice to fetch a huge amount of data in one go. So what we can do is, we can split the data into chunks, or say multiple sections sequentially. This method of fetching data into sections can be achieved by pagination.

What is Pagination?

Pagination is the procedure of separating content into discrete pages to make them more user-friendly. Pagination allows fetching limited data in chunks which makes data fetching fast and user-friendly.

I have already posted an article on Concept of Pagination by SQL Query in MySQL you can read this. It will help you in understanding the code easily.

Code example for How to create Pagination with PHP and MySQL

LIMIT clause in MySQL is the key to create a pagination feature. Likewise, I explained in an earlier topic, it uses two arguments. The first argument is and the second one is the number of records which will be returned from the database, that is, per page record Limit.

Simply follow the steps given below to create pagination in PHP – 

1. Set a page Limit

Firstly, we need to decide the number of records to be shown in a single page. I am going to set this limit to 10. That means one page will display at most 10 records.

$page_limit = 10;

2. Get and evaluate correct page number

After setting our page limit, the second thing to do is to have a correct page number. We will get the current page number with the help of ‘page’ key of $_GET array variable. so here, I can get page number from $_GET[‘page’].

Then evaluating to get the correct page number with the code I have given below. By default page number should be set to 1, i.e the case when there is no page number.

$page = (isset($_GET['page']) && $_GET['page'] > 0) ? intval($_GET['page']) : 1;

3. Calculate Offset to create Pagination with PHP and MySQL

Offset is essential for making paginations work properly. Offset is actually the starting point for the data to be shown in a page. Like it will 0 for page 1, as record start with zero like in arrays. For Page two with page limit as 10, the offset will be 10 and so on. Likewise, for page ten the offset will be 90. Below is the clear code for calculating offset for creating pagination.

$offset = ($page > 1) ? ($page_limit * ($page - 1)) : 0;

4. Count the total number of records in the table

Now, its time to find the total number of records in a table. We can find the number of records in any table with the help of MySQL count function.

Prefer to pass the primary key (unique data) column as an argument in COUNT() function. Some people give * instead of a single column which is not an efficient way in case the table contains a lot of columns. This will increase the query execution time as all the columns will be counted instead of a single column.

Back to the topic. I have assigned query string to $query_count, and the counted result is stored in $results_count and this is an array. To get the integer count value (this is what we need), I have stored the counted values in $total_count.

$query_count = "SELECT count(id) AS count FROM fruits";
$res_obj = mysqli_query($con, $query_count);
$results_count = mysqli_fetch_assoc($res_obj);
$total_count = $results_count['count'];

• How To Connect MySQL Database In PHP – PHP MySQL
• MySQL CHAR And VARCHAR Types
• How To Dynamically Call Methods In PHP
• MySQL official page on LIMIT Query Optimization

5. Fetch records for the current page

To display records in a page wee need to fetch records for that particular page by using offset and limit. These values are already calculated as I have mentioned above. All the records for the current page are fetched and stored in $results, which are maximum of 10 records. I have mentioned the full code below.

$query_col = "SELECT * FROM fruits ORDER BY id LIMIT $offset, $page_limit";
$res_obj = mysqli_query($con, $query_col);
$results = mysqli_fetch_all($res_obj, MYSQLI_ASSOC);

6. Find the page distribution count

Here I have mentioned a term page distribution count. When we will create pagination, the records will split into several numbers of pages. So, the total number of pages in which records will be distributed is page distribution count. Like, if I have 100 records and I have to display 10 records per page. Then the number of pages will 10. Similarly, for 115 records the page count will be 12 (110 records in 11 pages and last five records in a page. So total 12 pages).

We have $page_limit = 10. So 10 records will be displayed on each page and the rest of the records will be displayed on another page. Each page displays a maximum of 10 records. Hence if records are not evenly distributed (suppose total records = 102), the last page will have less than 10 records (2 records in case of 102).

$pages = ($total_count % $page_limit == 0) ? ($total_count / $page_limit) : (round($total_count / $page_limit, 0) + 1);

*Step 5 and six can be interchanges as fetched records will not affect page count.

7. Create an array for creating pagination links

In the process of creating pagination in a website with PHP and MySQL, creating links to all the pages is an essential part. It allows proper navigation from one page to another.

In this array, I have taken five values/indexes. Total, current, from and to are to display pagination string .ie for display purpose. pages index is used in creating pagination links. I have explained the relevance of all the indexes below.

1. ‘total’ is the total number of pages in which content is distributed
2. ‘current’ is the current page number.
3. ‘from’ – shows the starting number of records on the current page.
4. ‘to’ – shows the last number of record on the current page.
5. ‘pages’ – represent the number of pages in which records are distributed.

While creating the pagination links, I would recommend to disable the link of the current page and show it differently. This will make the user easy to understand the current page. I have done this in the code below. You can try and see.

$paging = [
    'total' => $total_count,
    'current' => $page,
    'from' => ($offset + 1),
    'to' => ($offset + $page_limit),
    'pages' => $pages
];

• Concept Of Pagination By SQL Query In MySQL
• MySQL CASE In WHERE CLAUSE: Conditional Conditions
• How To Export MySQL Database Using Command Prompt Or Batch File
• MySQL CHAR And VARCHAR Types

8. Display results with pagination PHP and MySQL

Finally, display the fetched records in a proper way which should be user-friendly. As the purpose of pagination is to make data user-friendly, the records must be displayed one in a row. To achieve this goal, representing data in tabular format is a good practice.

I have set a loop for $results. Each loop will print a table row with two columns.

After the data loop will finish, a row with colspan=2 will print a string to represent pagination information. Below this row will be the pagination links.

<?php
if (!empty($results)) {
    echo '<table>';
    echo ' <tr>';
    echo '<th>ID</th>';
    echo '<th>Name</th>';
    echo ' </tr>';
    foreach ($results as $res) {
        echo '<tr><td><b>#' . $res['id'] . '</b></td><td>' . $res['name'] . '</td></tr>';
    }
    echo ' <tr><td colspan="2">';
    echo 'Displaying '.$paging['from'].' to '.$paging['to'].' of '.$paging['total'].' records at page #'.$paging['current'];
    echo '</td></tr>';
    echo ' <tr><td colspan="2">';
    for ($page_counter = 1; $page_counter <= $paging['pages']; $page_counter++) {
        if ($page_counter == $paging['current']) { ?>
            <span class="pagenum"><?= $page_counter; ?></span>
        <?php } else { ?>
            <a class="pagenum" href="<?= 'mywebsite.com/mypage?page=' . $page_counter; ?>"><?= $page_counter; ?></a>
        <?php }
    }
    echo '</td></tr>';
    echo '</table>';
}
?>

9. Let’s assemble all the codes in one page

$db_user = 'your_db_user';
$db_pass = 'your_db_password';
$db_name = 'your_db_name';
$con = mysqli_connect($db_host, $db_user, $db_pass, $db_name);
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
    die;
}
$page_limit = 10;

$page = (isset($_GET['page']) && $_GET['page'] > 0) ? intval($_GET['page']) : 1;
$offset = ($page > 1) ? ($page_limit * ($page - 1)) : 0;

$query_count = "SELECT count(id) AS count FROM fruits";
$res_obj = mysqli_query($con, $query_count);
$results_count = mysqli_fetch_assoc($res_obj);
$total_count = $results_count['count'];

$query_col = "SELECT * FROM fruits ORDER BY id LIMIT $offset, $page_limit";
$res_obj = mysqli_query($con, $query_col);
$results = mysqli_fetch_all($res_obj, MYSQLI_ASSOC);
$pages = ($total_count % $page_limit == 0) ? ($total_count / $page_limit) : (round($total_count / $page_limit, 0) + 1);
$paging = [
    'total' => $total_count,
    'current' => $page,
    'from' => ($offset + 1),
    'to' => ($offset + $page_limit),
    'pages' => $pages
];

$page_link = 'mywebsite.com/mypage?page=';

if (!empty($results)) {
    echo '<table class="table table-striped table-bordered">';
    echo ' <tr>';
    echo '<th>ID</th>';
    echo '<th>Name</th>';
    echo ' </tr>';
    foreach ($results as $res) {
        echo '<tr><td><b>#' . $res['id'] . '</b></td><td>' . $res['name'] . '</td></tr>';
    }
    echo ' <tr><td colspan="2">';
    echo 'Displaying '.$paging['from'].' to '.$paging['to'].' of '.$paging['total'].' records at page #'.$paging['current'];
    echo '</td></tr>';
    echo ' <tr><td colspan="2">';
    for ($page_counter = 1; $page_counter <= $paging['pages']; $page_counter++) {
        if ($page_counter == $paging['current']) { ?>
            <span class="pagenum"><?= $page_counter; ?></span>
        <?php } else { ?>
            <a class="pagenum" href="<?= $page_link . $page_counter; ?>"><?= $page_counter; ?></a>
        <?php }
    }
    echo '</td></tr>';
    echo '</table>';
}

10. Get some styles for a proper layout to create Pagination in PHP and MySQL

<style>
    .pagenum {
        height: 30px;
        width: 30px;
        background: #a8c5e4;
        margin: 2px 10px;
        padding: 5px 15px;
        color: #0056b3;
        font-weight: bold;
    }

    a.pagenum {
        text-decoration: none;
    }

    span.pagenum {
        text-decoration: none;
        color: #e57373;
        background: #efcdad;
    }
    table {
        border-collapse: collapse;
    }
    .table {
        width: 100%;
        margin-bottom: 1rem;
        color: #212529;
    }
    .table-bordered {
        border: 1px solid #dee2e6;
    }
    .table-striped tbody tr:nth-of-type(odd) {
        background-color: rgba(0,0,0,.05);
    }
    .table-bordered td, .table-bordered th {
        border: 1px solid #dee2e6;
    }
    .table td, .table th {
        padding: .75rem;
        vertical-align: top;
        border-top: 1px solid #dee2e6;
    }
</style>