This tutorial is designed to guide you through the logic and code behind the process of paginating data in PHP. In this tutorial, the data is retrieved from mySQL database. I feel you will be able to grasp this tutorial better if you have a decent knowlege of PHP and mySQL. However, I plan to comment code as much as possible and describe the why we do each part.
What is Pagination?
There might be a few of you who are clueless to even what pagination is. For those with question, pagination is where you have more data that desired appearing on one page, and breaking up that data so that it is then presented on multiple pages. It is very likely that you have seen this before. To see a working example, click this link: Code List Notice at the very bottom, the page numbers listed and either "Previous" or "Next" to the left and right. To view more pagination, simply view any tutorial on this site (allsyntax.com) and navigate to the bottom of each page.
What are the Benifits?
Pagination is excellent in that it is user friendly. Users can navigate through what was normally a large amount of data, and it is now broken into pages which is easy to browse though. Paginating information on your site is also a benifit to you. No longer do you have large amounts of data cluttering up sections of your site. Also, if you were paginating by hand, well- then this method will save you a great amount of time, and you will not know what to do with hands that are not cramped up. This method is customizable in that you can even allow the user to determine the results per page as well.
What do I Need to get Started?
There are a few things you will need before you can get started. The first, being PHP. Check with your hosting if PHP is a feature. Next, access to mySQL database. The database part is not necessiarly a requirement, however this is what this tutorial is based around, and pagination is an accent to your data in the database.
Since we will be interacting with the database, one of your first lines of code can be to connect to the database with your username and password. I recommend having this in a single file, and just include the file when you need to connect in your scripts. See below:
Now we will run a query to find out how many items (rows) match the category being viewed. This will be the total amount of items that can be showed.This query can look something like this:
$q is the query, and after that we make sure the query was successful. If not, we display the error. After that, $total_items will contain the value of the max amount of results. Next, we can declare a few of our important variables, $limit, $cat and $page. The values of these variables are obtained through the $_GET method.
If for any reason, this data is not available, we can set defaults. This might be because someone hand typed the url containing these values, or because someone is intentionally attempting to cause errors in your scripts. So, we use the code:
The first condition will set $limit to 10 (default) if $limit was empty, non numeric, less than 10, greater than 50. The function, is_numeric(); is boolean, so it returns true if $limit is numerical, false if it is non numerical (containing non numeric data). The second condition is basically the same. If $page is empty, non numeric, less than zero, greater than the max amount of results; Then set $page to the value of 1 (default).
Continued...Next, we will set two more variables. These will be the total number of pages needed, and the limit we set to use in our query.
$total_pages: in this variable, we get the total number of pages needed. We use the ceil(); function - This will round up any remainder we have from the calculation within. We need it to round up, so that no items are left out. So, if $total_items were 27, and $limit was 10...that would return : 2.7 - If we didnt use ceil(); you cant have .7 of a page, and if we showed 2 pages, we would lose those .7 results. So, we round that up to 3 with ceil(); and in this case = 2 pages of 10, and 1 page of 7.
$set_limit: This will contain the value of where we will start, or where we left off, depending on the value of $page and $limit. If $page was 3 and limit was $10 then the variable will contain the value: 20. And that would be the value we use in out query for LIMIT.
The most important thing about pagination is the query. The part of our query that makes pagination possible is LIMIT. There are two ways to use LIMIT.
Example #1: SELECT `stuff` FROM `table` LIMIT 5
In the example above, the query will return the first 5 rows.
Example #2: SELECT `stuff` FROM `table` LIMIT 5, 10
In this example, the query will return 10 rows, beginning with row 5.
We will be using LIMIT with the #2 example; except where you see 5 and 10 above, we will place the variables, $set_limit and $limit.
Check out our query below:
This is the heart of our pagination, each time the query will return different results, depending on the value of $set_limit (which changes depending on the value of $page) and what our limit is set to in $limit. Now, we'll break down the code:
$q: We're selecting all data from the table where category of the table matches the category we're in, starting with row equaling value of $set_limit, limiting the results to follow only to the value of $limit. After that, we check that the query was successful, and display an error if it was not.
$err: Contains the number of rows returned from the query, if that number is zero, we indicate that no matches were found.
Results per PageWe can allow the user to determine how many results per page he or she would like to see. To do this, we will provide a few links that will directly alter the value of $limit, this works because limit is in our query string ($limit = $_GET['limit']).
Here, we display the links to view different results per page:
See where we just alter the value of limit in the query string? This is also why we set the conditions above where $limit cannot be less than 10, or greater than 50.
Now we have all of our variables declared, and the query ran, so we are now at position to display our results. We will use a while loop to display the results, see the code below:
The code above will display each row matching the query we previously set. In the echo statement, you see result->title -This would display the title of the item in your table if you had a column named title. This is only an example, im sure the code you have to display each individual item is much larger and prettier, I just made it simple for you to see whats going on within the loop.
Displaying Prev., Next and pages in betweenSo, we've got our results matching the query displayed, now what about the rest? Believe it or not, the hard part is done and it should be smooth sailing from here. First, we will determine if we have to display the "Previous" link or not. Heres the code:
$prev_page: This contains the value of what will be our previous page, by taking the value of our current page, and subtracting 1. Next, we check if $prev_page is greater or equal to 1. If it is, we need to display the link -so we echo it. Take a look at the link we used:
Notice in the url that we simply include the variables containing our current data. I would also like to take the time to say that this is a perfect case to use Mod_Rewrite for friendly URL's. Instead of having this long tedious query string, you can make the URL appear as simple as this:
Where php is your $cat, 10 is $limit, and 1 is $page. Much, much better. And to possibly save you time, if you were to use this same exact format of url, your Mod_Rewrite code would be:
Pages in between
Getting back on track, we will now display the pages in between (not previous or next). To do so, we will use a for loop. View the code below:
This is a basic for loop structure, where it will loop until it is less than or equal to the value of $total_pages. Inside the for loop, we set a condition that if $a equals the current page, to just echo that value, not linked (because we're already on that page). If not, echo the rest of the pages, linked.
Displaying the next page link is identical to displaying the previous page link. Your code should look a bit like this:
Set the value of next page in the $next_page variable by taking the value of the current page ($page) and adding 1. Now, we have the condition that will be executed and display the back link only if $next_page is less than or equal to the value of $total_pages. That is all for displaying the paginated links.
I would like to bring important information to your attention. If your category names contain spaces, like, "visual basic" when you try to use that in your link, it will not link properly. Because, it will only read until the space is encountered, and then stops. Your link would read, "visual" instead. Therefore, we will use the function, urlencode(); to replace those spaces so that the browser can interpret them. See the code below,
Simple as that. Now, if $cat was, "visual basic" it is now "visual+basic" and the links will be valid, and browser friendly. Just be sure to place this line of code before the code where you begin to display the links.
Now that we have covered though the entire script, piece by piece- follow the link below to view the entire code:
|4||Rating of 5 (1 Votes)|
|· Login to Rate This Article|
|· Login to Post a Comment|
|· Read more by this author|
|· Simple PHP Tutorial|
|· One File Website|
|· Building a Comments Script|
|· Complete Members System|
|· Variable Scope|