Pagination MySQL PHP

Question

I'm trying to use pagination for a customer table using PHP, MySQL & Bootstrap. I wrote this code:

<div class="container mx-auto">
<!--Add class table-responsive for responsive table -->
<table class="table mx-auto">
    <thead>
    <tr>
        <th>Name</th>
        <th>Surname</th>
        <th>Email</th>
        <th>Phone</th>
        <th>Address</th>
        <th>Zipcode</th>
        <th>City</th>
        <th>Company</th>


    </tr>
    </thead>
    <tbody>
    <ul class="pagination">
        <?php
        if(isset($_GET['page'])){

            $previous = $_GET['page'] - 1;
            if($previous = -1 ){
                $previous = 0;
            }
            if($previous = 0){
                echo '<li class="page-item"><a class="page-link" href="#">Previous</a></li>';
            }
            else{
                echo '<li class="page-item"><a class="page-link" href="?page='. $previous.'">Previous</a></li>';
            }
        }
        $page_max = 10;
        $entriesInDatabase = $database->getData("SELECT count(id) FROM customers");
        $numberOfPages = ceil($entriesInDatabase['count(id)']/$page_max);

        for($i = 0; $i < $numberOfPages; $i++){

            echo '<li class="page-item"><a class="page-link" href="?page='. $i . '">'. $i. '</a></li>';
        }
        if(isset($_GET['page'])) {
            $page = $_GET['page'];
            $start = $page * 10;
            $end = ($page + 1) * 10;
            var_dump($start); 
            var_dump($end);
        }
        else{
            $page = 0;
            $start = $page * 10;
            $end = 10;
        }

        $customers = $database->getUsers("SELECT * FROM customers LIMIT $start, $end");

        ?>
        <li class="page-item"><a class="page-link" href="#">Next</a></li>
    </ul>
    <?php



    foreach($customers as $customer){
        $name = $customer ['name'];
        $surname = $customer['surname'];
        $email = $customer['email'];
        $phone = $customer['phone'];
        $address = $customer['address'];
        $zipcode = $customer['zipcode'];
        $city = $customer['city'];
        $company = $customer['company'];
        $id = $customer['id'];
        echo "<tr>
                <td>$name</td>
                <td>$surname</td>
                <td>$email</td>
                <td>$phone</td>
                <td>$address</td>
                <td>$zipcode</td>
                <td>$city</td>
                <td>$company</td>



              </tr>";
    }
    ?>

I want each page to show 10 records from the database so giving a limit to my SQL query. What happends now is the following; page 0 shows 10 records, page 1 shows 20 records, page 2 shows also 20 records but page 9 shows 11 records.

Could anyone help me out fixing this issue?

Answer 1

You'd be better off thinking about the principles of how it works rather than just looking at the code.

Firstly, you need to know:

• How many records are there in the table? You can do that with COUNT()
• How many entries to show per page? You may fix this, to say 10 per page, or any number.

You then need to understand how LIMIT works. If you do something such as LIMIT 50, 10 it means use 50 as the starting point ("from the 51st record in the table" - remember the indexes start at 0) and get the next 10 rows. The latter number, 10, is the number of rows you want to show per page.

In terms of constructing the links the easiest method is to make the ?page= parameter in the URL the first value for the LIMIT query because this changes per page (e.g. 50 in the example above), where as you know the other number (10) is constant. You can produce these links by doing a ceil on the number of records in the table, divided by the number of records per page. This will output the appropriate numbers for the URL.

Let's say you had 362 records in your database and wanted to show 10 per page. This would produce the URL's:

$per_page = 10;
$records_in_db_table = 362; // You must calculate this from a COUNT() query
$n = ceil($records_in_db_table / $per_page);

for ($x=0; $x<$n; $x++) {
    $page = ($x * $per_page);
    echo '?page=' . $page;
}

The above code outputs:

?page=0
?page=10
?page=20
?page=30
// ...
?page=360

Then you just feed these in to your query's LIMIT condition, e.g.

• ?page=10 == LIMIT 10, 10
• ?page=20 == LIMIT 20, 10
• ?page=30 == LIMIT 30, 10
• etc...

It's also worth noting that you don't need to care about what happens if you're trying to LIMIT to more records than exist. For example the last URL (?page=360) will do LIMIT 360, 10. There are only 362 records in your database so you might assume this won't work as there are only 2 records that could be returned from that query. However, it will just return the last 2, no problem.

Equally if you try and use a number which is way beyond the total number of records (?page=99999 which gives LIMIT 99999, 10) it will just return an empty result set, not an error. Some people code things in to do a simple check, e.g.

if ((int)$_GET['page'] > $records_in_db_table) { 
    // Display error message
}

Remember that you should sanitise $_GET['page'] or at the very least cast it to an integer. Do not inject anything from $_GET directly into your query, and use parameter binding (e.g. PDO) if possible.

You may also want to look at DataTables (https://datatables.net/) since this does a lot of what you need without writing any such code and works well with Bootstrap.