Extremely Simple Pagination
Ever wondered how pagination, or, a paging system works? Using PHP & MySQL I will show you an extremely simple way to create one.
Let's fetch some records
We'll be using MySQL as our database system for the pagination. NOTE: I'll assume that you already have an established MySQL connection in your script. In a PHP document, add the following code:
<?php
$sql = "SELECT * FROM `table` ORDER BY `field` DESC";
$q = mysql_query($sql);
$total_records = mysql_num_rows($q);
?>
$sql and $q are our MySQL related variables, $sql references the query, and $q is the MySQL resource used. Modify $sql to match your own database and table structure. $total_records is the total amount of records returned in the MySQL query, later used to calculate the amount of pages.
Let's talk pages
<?php
$sql = "SELECT * FROM `table` ORDER BY `field` DESC";
$q = mysql_query($sql);
$total_records = mysql_num_rows($q);
$records_per_page = 10;
$total_pages = ceil($total_records / $records_per_page);
$page = intval($_GET['page']);
if ($page < 1 || $page > $total_pages) {
$page = 1;
}
?>
I've added $records_per_page, $total_pages and $page. In order, these represent the amount of records per page, the total number of pages (calculated by dividing $total_pages and $records_per_page, rounded to the ceiling), and the page number as set by the user's browser request. We use intval() to grab the 'integer value' from the variable, which is a number. The if statement that follows this checks to make sure the page number is valid, meaning no less than one, and no more than the total amount of pages. If it isn't valid, we reset it to one.
We know all about pages now, what about the results?
Now that we know how many pages we have, and what page we're on, we need to build a MySQL query that can be used with this data.
<?php
$sql = "SELECT * FROM `table` ORDER BY `field` DESC";
$q = mysql_query($sql);
$total_records = mysql_num_rows($q);
$records_per_page = 10;
$total_pages = ceil($total_records / $records_per_page);
$page = intval($_GET['page']);
if ($page < 1 || $page > $total_pages) {
$page = 1;
}
$offset = ($page - 1) * $records_per_page;
$sql = $sql . " LIMIT $offset, $records_per_page";
?>
$offset is the offset at which we need to start our MySQL result set at, and $sql is the original query, with appended data: LIMIT X, Y. This is a MySQL SELECT syntax modifier, which tells the query what records we need to start at, and how many records should follow. X represents the offset, with 0 being the first record. This is why we subtract one from $page, so we start at the correct offset. Y represents the amount of records to display after the starting offset, which is $records_per_page.
What's next... ACTUAL pagination?
You got it! I don't need to go into detail on the subject of the MySQL queries anymore, your prepared query is $sql, which you may use to your disposal. So let's get to paginating!
<?php
$sql = "SELECT * FROM `table` ORDER BY `field` DESC";
$q = mysql_query($sql);
$total_records = mysql_num_rows($q);
$records_per_page = 10;
$total_pages = ceil($total_records / $records_per_page);
$page = intval($_GET['page']);
if ($page < 1 || $page > $total_pages) {
$page = 1;
}
$offset = ($page - 1) * $records_per_page;
$sql = $sql . " LIMIT $offset, $records_per_page";
//use $sql with a MySQL query operation: mysql_query($sql) ... etc
for ($i = 1; $i <= $total_pages; $i++) {
if ($i == $page) {
$class = ' class="active"';
$prepend = 'Page ';
}
echo '<a href="?page=' . $i . '"' . $class . '>' . $prepend . $i . "</a>\n";
unset($class, $prepend);
}
?>
With this for() loop, we start at one and loop until we reach $total_pages. If $i should happen to equal $page, then we need to give it a class name of "active", and prepend "Page" before the page number. This allows you to customize the current page style with CSS, and also gives it a nice output format; 1, 2, Page 3, 4, 5... and so on. At the end of each iteration of the loop, we need to unset $class and $prepend, so they are not accidently reused. For each iteration of the loop, we output a link to the page, with the link corresponding to the page selected.
And that's it! You've just completed a very simple PHP & MySQL pagination system.