Implementing Pagination using PHP and MySQL – Introduction
In order to understand how pagination works in php, we need to revisit how the LIMIT clause works in MYSQL queries. If you recall, we can fetch a predetermined number of records from a database table by using a query with a LIMIT clause as follows:
SELECT movie_id, movie_title, year_released FROM movies ORDER BY movie_title ASC LIMIT 10
The above SQL query selects the movie id, the movie title and the year of release from a table named ‘movies’. The results fetched are then ordered by the movie title in ASCENDING order, meaning that the movie titles are displayed in alphabetical order going from A to Z. The final part of the query uses the LIMIT clause and we give a value of 10. This value is saying ‘show me the first 10 records’ from that table.
Another way to write the LIMIT clause in the above query is by using whats called an ‘OFFSET’. If we wanted to explicitly state where we wanted to start fetching records from in our query, i.e if you want to start fetching records from 10 to 19 then we’d write our query like this:
SELECT movie_id, movie_title, year_released FROM movies ORDER BY movie_title ASC LIMIT 10,10
The above uses an offset of 10 followed by the number of rows we want returned (also 10). If we then wanted to retrieve all the rows (records) starting from 20 to 29 then our LIMIT clause would be LIMIT 20,10.
In case you’re wondering, we could just as easily have written our first query using an OFFSET too like this:
SELECT movie_id, movie_title, year_released FROM movies ORDER BY movie_title ASC LIMIT 0,10
The above simply begins with an offset of 0, meaning it tells us where to start fetching our first row from. If you don’t specify this offset, by default it will be 0. Also a key concept to remember is that the first record in a LIMIT clause is indexed at 0, the second at 1, the third at 2 and the tenth at 9.
In our above example the offset is the ‘starting value’ from where we want to begin retrieving records in the table and the 10 is the ‘results per page’, where results implies the maximum number of records you want to show on each page. If you only wanted five movies to be seen per page, then you’d simply change LIMIT 0,10 to LIMIT 0,5.
The view_movies.php page
Having digested the above, lets focus our attention on the table below which is titled ‘movies’:
As you can see there is also a column named ‘movies_cover_img’ which holds the names of the image to display for the movie poster or cover. We will be creating a two scripts, one named ‘add_movie.php’ and one named ‘view_movies.php’. The ‘add_movie.php’ script will house the form through which we’ll be able to submit details for a movie we’d like to add into our database. The ‘view_movies.php’ script will show all our movies from the movies table in a paginated manner. The below screenshot shows what the user will see on view_movies.php:
As you can see in the above screenshot, pagination links are created to view a particular paginated set of results. When this page loads for the very first time it will have a default ‘start’ value of 0, meaning it will load the first 5 records starting from index 0 and ending at 4. If the user clicks on ‘2’, it should then load the next set of records in the movies table starting from 5 and ending at 9, and if the user clicks on ‘3’ then it should retrieve records beginning at index 10 etc. No matter what link the pagination link gets clicked, the appropriate and relevant records will be retrieved and displayed on screen.
How it works
In order for this system to work, we will use the HTTP Request Method ‘GET’. Each time the user clicks a pagination hyperlink button, two GET parameters will be sent in the url:
1) The starting point from where to begin retrieving records
2) The total number of pages required to view all our records
When the view_movies.php loads the first time, it will load the first 5 records from the movies table. The start value (offset) will be 0 by default at this point and no HTTP Get request will have been made since the user has not clicked a pagination link. Likewise, the value for total pages required to show an ‘n’ amount of records will be determined via calculation.
When the user clicks on a pagination link, a HTTP Get Request will be made and the two GET Parameters get passed page to page, which we have already discussed. They will be passed in the URL as a query string. In our case if the user clicks on the 2nd pagination link, the query string will look like this:
view_movies.php?s=5&p=12
Writing the PHP code for view_users.php
The script files along with the movies_db database are available to download on my github page, so be sure to download these files if you want to see actual script in action. We will be working primarily with the movies table which also houses a column to hold the movie cover image. We will need to do all of the following things below in our script to make pagination work:
- Set the number of results/records to show per page
- Determine the total number of pages we’ll need to display our records
- Determine the starting point of where to fetch our records from the movies table
- Determine the current page number
- Display a previous link if current page is not the first page
- Create pagination numbered links for pages
- Display a next link if current page is not the last page
Open your editor and create a new file named view_users.php if you have not already done so. Begin creating your script as shown below:
1 – Set the number of results/records to show per page
We create a variable named ‘$results_per_page’ and give it a value of 5. This is the total maximum number of records we will show at any one time on a page. Next we are going to need to check whether or not the total number of pages required to view our records has been determined. If you remember earlier I mentioned that we’d be passing passing in two values as GET Parameters each time the user clicked a link, well these will differ depending on which pagination link the user would click. The first time this script is run, no GET request is made and this value will need to get calculated. The below segment of code checks to see just that:
Screenshot of code block
2 – Determine the total number of pages we’ll need to display our records
We check if a pagination link was clicked with the ‘if(isset($_GET[“p”] && is_numeric($_GET[“p”]’ statement. If it was clicked, then a value holding the number of pages will have been passed in via the GET request. If no pagination link was clicked then we enter the ‘else’ clause of our ‘if’ statement and proceed to calculate the number of pages manually.
Now we define a query that counts all the movies in the movies table and stores the result in a variable named ‘$q’ (short for quantity). Next we execute this query using the ‘mysqli_query’ function and pass in the database connection along with the saved query as arguments to this function call. The result of this function is saved in a variable named ‘$r’ (short for result). Since this query only returns a count, that is, it counts the total number of records in the table only, it will only return one row of information. This row of information will be a number which tells us the number of records in the movies table. We use the ‘mysqli_fetch_array’ function to get at the value held in this row and save it a variable named ‘$row’. The important thing to understand here is, since the result of our query is a number giving us the total number of records in a table, it will occupy one column only. There is nothing else we are getting here, just this.
The ‘mysqli_fetch_array’ function fetches a result row either as a numeric array, an associative array or both. It will always return one row of information (usually used in conjunction with a while loop to get at all rows inside a table) and takes two parameters: the result of the ‘mysqli_query’ and the type of array to return. The second parameter is a constant and in our case we used ‘MYSQLI_NUM’ which returns an indexed array, allowing us to easily get at the value held in that column. Finally we save this value in a variable named ‘$records’ as shown below:
Once we know how many records reside in our movies database, we then need to calculate how many pages we are going to require in order to display them all. We already know that we have a variable named ‘$results_per_page’ that tells us the maximum number of results/records we need to show on each page, so lets first check to see that we at least have more than this number:
If we have more than 5 records (which we do) then we need to display them across a number of pages. To determine how many pages we’ll need we divide the number of records in our movies table by how many we want to show on each page. If we have say, 27 records in our movies table and we can only show 5 per page, then the first five pages will show 5 records and the sixth page will show the remaining 2 record. The calculation ‘$records / $results_per_page’ allows us to get an answer with a decimal value and in our case 27 divided by 5 gives us 5.4. The ‘ceil’ function is applied to our calculation in order to round off our decimal value to the highest integer. For example, 5.4 gets rounded off to 6 and this makes perfect sense since our first five pages will show 5 records and our final sixth page will show the remaining 2. The result of this calculation is finally saved in a variable which we’ll be using later named ‘$pages’.
If the total number of records in the movies table is less than the maximum number of results allowed per page, then we do not need more than a page. The variable ‘$pages’ therefore will have a value of 1 and we exit the ‘if(isset($_GET[‘p’])) statement.
3 – Determine the starting point of where to fetch our records from the movies table
The code for determining the starting point from which to begin retrieving records starts off with a similar if statement as the one used to check if the number of required pages has been set. The below screenshot highlights this part of the view_users.php script:
Just to remind you once again, the GET Parameters being sent become part of the URL and are always shown as ‘name – value’ pairs separated by an ampersand ‘&’. It is only through using the pagination links that these get parameters will be passed into the URL and a GET request method will initiate. In the above screenshot the first line makes the query and saves it in a variable named ‘$q’. The query reads:
$q = "SELECT movie_id, movie_title, movie_cover_img, year_released FROM movies
ORDER BY movie_title ASC LIMIT $start, $results_per_page";
We’re selecting all the columns in the movies table and ordering them by their movie title in ascending order. This means movies titles that start with A will appear before movie titles that begin wtih C and any movie title that has numerals at the front of its title ‘i.e 21 grams’ will come movies that begin with alphabet characters. The last part of the query uses the LIMIT x,y clause with ‘$start’ and ‘$results_per_page’ replacing the x and y placeholders. If the script loads for the first time $start will have a value of 0 and $pages will get calculated. If the user decides to click on the pagination link ‘4’ to view the 4th page then the query would actually read:
$q = "SELECT movie_id, movie_title, movie_cover_img, year_released FROM movies
ORDER BY movie_title ASC LIMIT 15, 5";
LIMIT 15, 5 simply means ‘start fetching me records beginning from the 15th record onwards and show me a maximum of 5 results per page’.
Straight after defining and saving this query as ‘$q’, we then need to execute it using the ‘mysqli_query’ function:
$r = @mysqli_query($dbc, $q);
We check whether ‘$r’ was a success and if so we enter an if statement where we again perform another check, this time using the ‘mysqli_num_rows’ function:
$num = mysqli_num_rows($r);
All this function does is it returns the number of rows retrieved by a SELECT query. It takes as its only argument, a query result variable which in our case is saved as ‘$r’. We then make sure that the value of ‘$num’ is indeed greater than 0, otherwise it would mean our select query retrieved nothing. If that is not the case, we continue further in our script and begin outputting some html that mimics a table thanks to some css rules that make use of ‘display:table, display:table-row and display:table-cell’. The table headings are outputted to show the four main columns of the movies table, before finally entering a while loop that fetches and prints all the records one at a time using the ‘mysqli_fetch_array’ function.
// close the main div
// free up the resources
// close database connection
Up till now your script should have the following code:
The next and final stage in completing the view_movies.php script is to create the pagination links themselves. Besides having numbered pagination link buttons we’ll also need a button for ‘previous’ and ‘next’. A ‘previous’ link button ought to only be shown if the current page is not the first page, i.e we are not looking at records 0 to 4 (the first five). If the user is currently viewing records 20 to 29 lets say, they will be on page 5. They have the option to go backwards by one page each time clicking respective pagination buttons or they can click the ‘previous’ button instead.
This section of code will look like this:
In the above screenshot, the entire code sits inside a main if conditional, which checks whether the number of pages set is greater than 1. If we require more than one page to show our records, then we need pagination and associated links to paginate through records. If we don’t have more than 1 page, then the if statement code block simply doesn’t get run. To visualise all of the above code in a block, refer to the code block below:
Determine the Current Page
To determine the current page we need to divide the value of $start by $results_per_page and add 1 to that result. We need to determine this otherwise we will not be able to make our pagination links. Lets create a new variable named ‘$current_page’ which holds the result of our calculation ‘($start / $results_per_page) + 1’.
$current_page = ($start / $results_per_page) + 1;
When the script first loads, $start has a value of 0, therefore ( 0 / $results_per_page ) + 1 = ( 0 / 5 ) + 1 = 1. This is the first page, it shows us records at index 0 to 4 (first five records in our database). To retrieve and view records 6 to 10 (the next five records) we’d need to read from index 5, to retrieve and view records 11 to 15 we’d need to read from index 10 and so forth. These index values are what $start will have each time the pagination links get clicked. How these links will be generated is shown in the next step.
Create a ‘Previous’ Link
We also set a variable named $first_page and make it equal to 1, and then we check to see if the current page is equal to the value of $first_page:
Here if the current page is not the first page then always display a ‘Previous’ link in the pagination links holder. Assume you were viewing records 11 to 15, that would mean you would be on the 3rd page because records 1 to 5 are on page 1, records 6 to 10 are on page 2 and records 11 to 15 are on page 3. If we were therefore, viewing records 11 to 15 and on the 3rd page, clicking the ‘Previous’ link ought to take you back to the 2nd page of page so you can view records 6 to 10. The ‘Previous’ link which is being echoed out above consists of the script name ‘view_movies.php’ followed immediately by a question mark ‘?’ and then the name of the parameter followed by an equals sign and the value itself. We are passing two parameters in our URL this way, with each new one being preceded by an ampersand ‘&’ to separate them.
Assuming we were on the 3rd page showing records 11 through to 15, prior to clicking the ‘Previous’ link button the url in its entirety would look like this:
project/the_movies/view_movies.php?s=10&p=12
Clicking on the ‘Previous’ link button would now change the url to:
project/the_movies/view_movies.php?s=5&p=12
The value of the ‘s’ parameter gets determined when we either add the value of ‘$results_per_page’ to ‘$start’ or when we subtract the value of ‘$results_per_page’ from ‘$start’. Going from the 3rd page to the 2nd page by clicking on the ‘Previous’ link button requires that we calculate the ‘$start’ value for that page by echoing out the url and performing the subtraction of ‘$results_per_page’ from the value of ‘$start’:
Create the numbered pagination links
To create these links we will make use of a for loop which will start looping from the value 1 through to the value which is equal to the total number of pages. This value remember is being stored in stored in the variable ‘$pages’. While doing so, we will also use an ‘if’ statement to make sure that all our pages end up with clickable links except for the current page ( we’re already on that page so no need to include a link for it ).
The value for ‘s’ (the starting point) for each link being created is calculated here by subtracting from the value of ‘$i’ and then multiplying the answer with the value held in ‘$results_per_page’ which in our example is 5 (remember we only showing 5 results per page). When ‘$i’ is equal to 1, then ‘s’ will have the value of ‘0’. If ‘$i’ is equal to 2, then ‘s’ will have the value of ‘5’, when ‘$i’ is equal to 3 then ‘s’ will have the value of ’10’ and this keeps going on while $i is less than or equal to the total number of pages.
Creating our ‘Next’ pagination link
The code for showing the ‘next’ pagination link is fairly straight forward and is identical to the code for showing the ‘Previous’ pagination link, only difference here being instead of subtracting the value of ‘$results_per_page’ from ‘$start’, we are going to add it. This makes sense because if we were viewing the first five results on page 1 when the script first loads, then ‘$start’ would have a value of ‘0’ and we know ‘$start’ will keep incrementing by 5 as we move sequentially across to the next page.
Nathan Armltage says
This has been explained so elegantly and clearly that I wished I’d come across your article years earlier! I’ve applied the logic across other languages and it works! Easily the best tutorial I have come across on pagination in web dev. Thanks for sharing Manoj!
Manoj Kumar says
Hello Nathan Armitage,
As a Programmer, I’m truly delighted to hear that you found the explanation on pagination elegant and clear! It’s never too late to discover helpful resources, and I appreciate your kind words. I’m thrilled to know that you’ve been able to apply the logic across different languages successfully. Your feedback is incredibly encouraging, and I’m pleased that you consider it the best tutorial on web development pagination. If there’s anything specific you’d like to see in future articles or if you have any questions, feel free to reach out. Thank you for taking the time to share your thoughts!
Kind Regards,
Manoj Kumar