Pagination MySQL PHP
I am trying to paginate clients table using PHP, MySQL and 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 display 10 records from the database, which gave a limit to my SQL query. What happens now is: page 0 shows 10 records, page 1 shows 20 records, page 2 also shows 20 records, but page 9 shows 11 records.
Can anyone help me solve this problem?
source to share
You'd better think about how it works rather than just looking at the code.
First, you need to know:
- How many records are in the table? You can do it with
COUNT()
- How many records per page? You can fix it, say 10 per page, or any number.
Then you need to understand how it works LIMIT
. If you do something like LIMIT 50, 10
this, it means using 50
as the starting point ("from the 51st record in the table" - remember that indexes start with 0
) and get the following rows 10
. The last number 10
is the number of lines you want to show on the page.
In terms of link building, the easiest way is to make the parameter ?page=
in the url the first value for the request LIMIT
, because that changes to the page (like 50
in the example above) where as you know the other number ( 10
) is constant. You can create these links by doing the ceil
number of records in the table divided by the number of records per page. This will display the corresponding numbers for the URL.
Let's say you had 362 records in your database and wanted to show 10 per page. This will create a url:
$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 load them into your request LIMIT
, eg.
-
?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 have to worry about what happens if you're trying to make LIMIT
more entries than there are. For example, the last url ( ?page=360
) will execute LIMIT 360, 10
. There are only 362 records in your database, so you can assume this will not work as only two records can be returned from this query. However, it will just return the last 2, no problem.
Likewise, if you try to use a number that is outside the total number of records ( ?page=99999
that gives LIMIT 99999, 10
), it will simply return an empty result set, not an error. Some people code things to do simple validation like.
if ((int)$_GET['page'] > $records_in_db_table) {
// Display error message
}
Remember that you must disinfect, $_GET['page']
or at least give it a whole number. Don't inject anything from $_GET
directly into your query, and use parameter binding (like PDO) if possible.
You can also take a look at DataTables ( https://datatables.net/ ) as it does a lot of what you need without writing any such code and works well with Bootstrap.
source to share
Your problem is because you don't know how the keyword works LIMIT
. If you provide only one parameter, it will be interpreted as the maximum number of records from the query that should be returned. If you pass two values, the first will be interpreted as the starting offset, and the second will remain the maximum number of records returned.
In other words, the entry LIMIT 50, 60
- which occurred in your case while preparing the fifth page - will start fetching results from the 51st matching entry, and will also return 59 of the next.
If you want to display 10 records per page, then write 10 as the second parameter LIMIT
and that should be better.
source to share