(The files used in this tutorial are available to download here)
Sometimes when working with a MySQL powered website in PHP, we’ll come across situations where we have multiple records that we wish to display to the visitor. If there are many tens, hundreds or more of these results, we might not want them all shown on the one page as this would increase the loading time, and make the list harder to navigate.
To solve this problem, we introduce the concept of pagination in MySQL, which makes use of the handy LIMIT keyword and information passed by GET from the URL query string
For this example, we’re going to use a table of records containing popular boys and girls names beginning with “S” along with their general meaning. Because this table contains a lot of entries, I’m not going to display it all here, but the accompanying SQL file contains all the information you need to import the table into your MySQL database. Once you’ve done that, you can then proceed with the tutorial to learn how to split this data into pages and display the pages to your visitors along with a handy navigation bar.
download the full table in SQL here
| Name | Meaning |
| Ragnvald | Means “friend of God” in Hebrew |
| Raguel | Means “spacious” in Hebrew |
| Rahab | From the Old Norse name Ragnvaldr, a cognate of Reginwald |
| … | …231 more… |
Let’s perform our first query on this data to retrieve the first 30 records in the list. This is the main query we will be using to build our pagination application shortly
SELECT * FROM name_meanings LIMIT 0, 30 ORDER BY id ASC
If you run this SQL query, you’ll see the results returned are limited to only 30 and, more importantly, only the first 30. The LIMIT keyword takes, as arguments, two values - first, the record to start counting from and second, the number of records to return. So here, we could from record 0 and return 30 records. To return our second “page” of results, we would use
SELECT * FROM name_meanings LIMIT 30, 30 ORDER BY id ASC
Which then returns another 30 records, but this time returns the rows starting from record 30. We’re also using the ORDER BY keyword here, just to ensure that the results are ordered correctly. At the end of the tutorial, we’ll see how we can use the ORDER BY feature to add even more functionality to our site!
This is the fundamental principle behind creating a pagination system. We’ll now look to see how we can specify which page we want to view, and give the visitor control over this variable.
to actually determine which page to show at any given time, we’re going to build a little PHP script. This script will do a couple of things. Firstly, it’ll display whatever records we’re interested in, in a nicely formatted table and secondly, it will retrieve the current page we wish to view via the $_GET variable. (The $_GET array always contains any variables passed in the query string, e.g. if the URL is index.php?my_var=123, then the value of $_GET['my_var'] will be 123)
Let’s take a look at this code. This file is also available in the tutorial zip archive downloadable from here
<html> <html> <head> <title>Name Meanings</title> <style> html {font-family:tahoma,verdana,arial,sans serif;} body {font-size:62.5%;} table tr th{ font-size:0.8em; background-color:#ddb; padding:0.2em 0.6em 0.2em 0.6em; } table tr td{ font-size:0.8em; background-color:#eec; margin:0.3em; padding:0.3em; } </style> </head> <body> <?php //we always start out by connecting to our database //using a handy external file, db_connect.php include("db_connect.php"); //fetch our page number from the query string //the URL should look something like //name_meanings.php?page=4 $page = $_GET['page']; //define how many records we want to show per page $records_per_page = 15; if(!filter_var($page, VALIDATE_INT, 1)) $page=1; echo "<h3>Showing results for page $page</h3>"; //calculate the offset value to start counting from //in the MySQL query. To do this, we multiply the page //number, minus one, by the number of records we want //to show per page //e.g. for page 1 - Our starting record will be (1-1)*30 $offset = ($page-1) * $records_per_page; $result = mysql_query("SELECT * FROM name_meanings ORDER BY id ASC LIMIT $offset, $records_per_page"); echo "<table><tr><th>Name</th><th>Name Meaning</th></tr>"; if($row = mysql_fetch_array($result)){ do{ echo "<tr><td>{$row["name"]}</td><td>{$row["meaning"]}</td></tr>"; } while($row = mysql_fetch_array($result)); } echo "</table>"; ?> </body> </html>
This code will display on screen, a simple list of names and, depending on the query string appended to the URL, will start from a different point in the list, like so
Different query strings produce different tables
You can see how the URL name_meanings.php produces a different table to name_meanings.php?page=3
Now that we’ve created a system to allow us to display any page of results, we need to hand control to this over to the user. To do this, we can create a series of hyperlinks, all pointing back to the current page, but with a different value appended to the URL. The following code, appended to the end of the previous code, before the </body> and </html> tags, will achieve this
echo "<p>Navigation</p>"; $count_result = mysql_query("SELECT COUNT(*) FROM name_meanings"); $count_row = mysql_fetch_array($count_result); //fetch the total number of rows in the table $count = $count_row["COUNT(*)"]; //we will use this information to build our navigation list for($i=1; $i<=$count/$records_per_page; $i++){ echo "<a href='name_meanings.php?page=$i'>$i</a>"; //output a little symbol (|) to seperate the links //but not for the last link if($i!=$records_per_page) echo " | "; }
Reload your name_meanings.php file, and you should see a list of 16 links underneath the table, you can click on any of these to go to any of the pages.
How about if we add next and previous links to make navigation even easier? And also make the current page link inactive, so we can’t click on the same page link that we’re already viewing No problem! Make the following adjustment to the above code
echo "<p>Navigation</p>"; $count_result = mysql_query("SELECT COUNT(*) FROM name_meanings"); $count_row = mysql_fetch_array($count_result); //fetch the total number of rows in the table $count = $count_row["COUNT(*)"]; //we will use this information to build our navigation list echo (($page>1)?"<a href='name_meanings.php?page=".($page-1)."'>prev</a>":"prev")." | "; for($i=1; $i<=($count/$records_per_page)+1; $i++){ if($i!=$page) echo "<a href='name_meanings.php?page=$i'>$i</a>"; else echo $i; //output a little symbol (|) to seperate the links //but not for the last link if($i<$count/$records_per_page) echo " | "; } echo " | ".(($page<$count/$records_per_page)?"<a href='name_meanings.php?page=".($page+1)."'>next</a>":"next");
Resulting in…
Behold! The power of navigation!
See how we’ve made it so that the next and prev links are inactive if we’re on the last and first pages respectively? We can do this by performing some checks on the current values of $page and $count/$records_per_page (which gives us the maximum page number)
Note here that $count/$records_per_page actually gives us a value of 16.6, which we can still work with, but for a more accurate count of the full number of pages, you’d use floor($count/$records_per_page) to get 16
What if you already have an application you want to add pagination to, which makes use of query strings? Consider the following example and imagine that the query string variable section=code is actually important, and we mustn’t lose it when changing page
name_meanings.php?section=code&page=5
If we just stuck with the code above, our navigation links would direct us to the following pages
name_meanings.php?page=1 name_meanings.php?page=2 ...etc
Which is not good! We’ve lost our original query string variable, and so whatever purpose it served has now been lost and the page may display wrongly. However, using some simple PHP code, we can create a function that will automatically generate a valid link for us, complete with whatever variable we choose to append to it. The function is as follows (we’ll name it build_url() )
function build_url($filename, $key, $value){ $values = array(); $query_str = array(); //get the query string arguments and store them in //the $values array parse_str($_SERVER['QUERY_STRING'], $values); //loop through the $values array and add the appropriate //keys to the query string foreach($values as $k=>$v){ //IF, though, a key in the existing query string matches the same key //we're trying to add, ignore it, since we'll add it manually in a moment //This prevents having multiples of the same keys if($k!=$key){ $query_str[] = "{$k}={$v}"; } } //add in our new key and value $query_str[] = "{$key}={$value}"; //reconstruct the full URL using the implode() function to piece together all //the query string values in the $query_string array, joining them together with "&" return "$filename?".implode("&", $query_str); }
And we’ll rewrite some of our code to use this function to generate our links
echo (($page>1)?"<a href='".build_url("name_meanings.php", "page", $page-1)."'>prev</a>":"prev")." | "; for($i=1; $i<=($count/$records_per_page)+1; $i++){ if($i!=$page) echo "<a href='".build_url("name_meanings.php", "page", $i)."'>$i</a>"; else echo $i; //output a little symbol (|) to seperate the links //but not for the last link if($i<$count/$records_per_page) echo " | "; } echo " | ".(($page<$count/$records_per_page)?"<a href='".build_url("name_meanings.php", "page", $page+1)."'>next</a>":"next");
Using this function, we can now enter a URL like
name_meanings.php?section=code&page=5
and our page links will still be valid.
Now that we’ve introduced a function to keep our URLs intact, this opens up an interesting new possibility, we can now add more than one search parameter to our table, to allow us to further refine our results. Let’s add a couple of options, first a direction option to choose whether we want the table in ascending or descending order, and another option sort by to state whether we want to sort by “name” or by “meaning”. Add the following code, near the end of the file…
echo "<p>Table Order</p>"; echo "direction : "; echo $dir=="ASC"?"Ascending":"<a href='".build_url("name_meanings.php", "dir", "asc")."'>Ascending</a>"; echo " | "; echo $dir=="DESC"?"Descending":"<a href='".build_url("name_meanings.php", "dir", "desc")."'>Descending</a>"; echo "<br/>"; echo "sort by : "; echo $sort=="name"?"Name":"<a href='".build_url("name_meanings.php", "sort", "name")."'>Name</a>"; echo " | "; echo $sort=="meaning"?"Meaning":"<a href='".build_url("name_meanings.php", "sort", "meaning")."'>Meaning</a>";
This will create the appropriate links to jump to the necessary page URL. We’re looking to have URLs along the lines of
name_meanings.php?page=5&sort=name&dir=asc
We also need to add code earlier in the file to deal with these new variables. Near the top of the file, before the main SQL query
//get the direction we'd like to sort our results in. Defaults to ASC $dir = strtoupper($_GET['dir']); if($dir!="ASC" && $dir!="DESC") $dir = "ASC"; //get the database value to sort by. Defaults to "name" $sort = $_GET['sort']; if($sort!="name" && $sort!="meaning") $sort = "name";
And make the following changes to the SQL query itself
$result = mysql_query("SELECT * FROM name_meanings ORDER BY $sort $dir LIMIT $offset, $records_per_page");
You should now be able to load up the page, and see your new navigation links working fully! Try changing the sort order of the table - note that, when sorting by “meaning”, the blank entries will show up first.
Now we have even more control!
You should now have a simple knowledge of the basics of pagination for PHP and MySQL. As you’ve progressed through this tutorial, you may have noticed some shortfalls in the script, and areas for improvement. One of the things I haven’t mentioned, but which you may well want to try and implement yourself, is a way to get round the problem that the pagelinks themselves could get pretty numerous!. Try and find some interesting way of displaying the links so that you perhaps see them like this
prev | 1 | 2 ... 8 | 9 | 10 .... 20 | 21 | next
If you get really stuck, email me and I might let you know the solution 
The files used in this tutorial are available to download here.
September 18th, 2008 at 9:28 am
sir, i like to get pretty numerous page navigation tutorial. can you send it to me.
September 20th, 2008 at 1:43 pm
I would like to get the code for the “pretty numerous page navigation” code sample which is shown above. I would appreciate your help ASAP.
September 30th, 2008 at 4:12 pm
I wanted to know this. Ive got a sitemap which takes ages to load on one of my sites. This will make things alot easier.
October 14th, 2008 at 9:52 pm
I was interested in how you solved the “pretty numerous page navigation” issue. I’ve tried coming up with a solution and so far haven’t been successful.
Thanks.
October 24th, 2008 at 4:57 am
cool and useful tut!. anyone have dreamweaver extension how to do this? please post it for me….
Regard
Vireak.,
November 10th, 2008 at 2:08 am
Same here. I’ve tried several things, I can’t figure out the last example. Give us a hint, at least?
November 10th, 2008 at 7:02 am
Mine wasn’t working before, I was getting errors, and I couldn’t find Validate_int anywhere in the php help files, so I switched it out with
if(!filter_var($page, FILTER_VALIDATE_INT, 1))
and it works.
Thanks for the tutorial - your website has helped me bigtime.
November 30th, 2008 at 3:37 pm
Dear The rough guide to PHP,
Thank you for your ‘Pagination : Splitting data across multiple pages with MySQL and PHP’ tutorial. It was very helpful.
Do you know of a way to have the script find the right page from the ‘if’ of the record you wish to navigate to?
Example: I want to show the page with ‘Raine’ which is on page 7 of the results, so the normal link would be: ‘www.mysite.com/name_meanings.php?page=7′ however I wish to produce ad-hoc links with the id, so to produce links like: ‘www.mysite.com/name_meanings.php?id=103′ which will then automatically navigate to page 7.
This would be useful which a visitor to the site adds a new entry and needs to see what they have just added, but will not necessarily who the last page.
Kind regards,
Mike
December 19th, 2008 at 1:58 pm
hello, thank you for the tutorial, i found it great to learn, although i have found a little bug which im struggling to fix, for example if you have 10 items in your database and display 5 on each page, then you should see this:
prev 1 | 2 | next
however when the records match the above statement you see
prev 1 |23 | next
the link to page 3 appears although there is no records to display in that page, i looked at the floor method but could not get it to work, any ideas?