Listing MySQL Over Multiple Pages in PHP

The Problem
You have a whole heap of items that you want to display from a database. The problem is you have way too many to post on a single page. You need page numbers that allow the user to browse through pages of database records. Following is a tutorial on how to accomplish this using PHP. This tutorial has been written with the understanding that the reader has some knowledge of PHP and how it interacts with MySQL and very good knowledge of HTML – also that you know how to populate your database with data. If you don’t want to read through the tutorial you can skip straight though to the source code which is downloadable here.

The Solution
You have used a database backend to insert your data. You can output the records but the problem is you have a couple of thousand records and it loads all of them into a single page causing information overload. You want to show only 50 entries per page (which is a lot still in my opinion - it doesn’t have to be 50, but it seems like a nice round number). While this solution may not be the most elegant solution, it works and only requires PHP and MySQL. In order to solve this problem we need the following files:

+ Page 1 of our record display – first_page.php;

+ A file to pull the data out of the database and display it – get_data.inc;

+ Some PHP to display the page numbers as links – page_numbers.inc;

+ A single page that acts as page 2 onward – next_page.php;

first_page.php
Let’s start with the easiest page first; page 1 of our records which we will call ‘first_page.php’. It is assumed that your web host allows you to treat .php files as .html files and will load .php files as if they are html, this will allow us to use html and PHP in the same file! Examine the following code. It is the actual code for this page that sits in php tags within the body tags. Examine the comments in (note: I have not put the header in, just what is between the body and php tags).


Simple! Well, most of the work is done externally so this page doesn’t have much to it. Next we will look at our get_data.inc file, which is where it does get sort of tricky.

get_data.inc
Our first real PHP. This is the file that gets the data from the database and displays it. We will work with a hypothetical database called ‘products’ in this tutorial. Our products database has a table called ‘toys’. Records in the ‘toys’ table that we want to display are ‘product_id’, ‘toy_name’ and ‘toy_cost’. So we need to open the ‘products’ database, go to the ‘toys’ table and retrieve the records ‘product_id’, ‘toy_name’ and ‘toy_cost’. Our query (as PHP and including database connection code) will look like this:


Normally I would recommend splitting the database connection section into a separate script. Now we need to know what page we are on so we know what we need to display. Page 1 is not a problem because we set the page number in the PHP that is embedded into the html. The rest of the pages will have their page numbers set from the page_numbers.inc file. We will explain how the value is actually passed when we get to that file. For now we will just tell you how it will be obtained:


We will only need to know the page number if it is not page 1, so this becomes:

Now from that we can work out what page we are on so we can now do a little math to work out which records we want to show. What we need now is:

+ A variable to hold the amount of records we want to display per page

+ A variable that holds a value that is worked out by multiplying the page number by the amount of records per page. This way we know what record in the table to start from.

So to this ‘if’ statement (and prior) we need to add the following:

Great! Now we can display some records. We won’t do anything fancy, that will be up to you. We will display them in a simple fashion. Like this:

‘product_id’ | ‘ toy_name’ | ‘ toy_cost’

We will put 2 line break tags after each record to space them out a bit as well. Our display code will look like this:

Now we need to put it all together! Note: you will need PHP tags at the start and end.

page_numbers.inc
The hard part is over, now we need a loop to display the page numbers for the amount of pages we will need. However, we need to do a little more math first. To work out how many pages we need we have to divide the total number of records by the amount of records we want to show on each page. Finally we need to round this number off so we don’t end up with a decimal. Check out the following code. Pay close attention to the comments (note: code will need to be wrapped in PHP tags):

That’s all we need for that page. Next up is the last page that acts as every page after page 1. It is very similar to page 1.

next_page.php
Instead of assigning the page number within the actual PHP file, it gets it from the counter link in the previous file as it is passed to the page.

That is it! The code is not optimal; I can spot ways to fix it up in a few places. I am leaving it as it is so you can fix it up and optimise it for yourself. This is so you can apply your own knowledge to it and get a better understanding of how the code works. Here is a hint for one section of code that can be optimised: We don’t actually need first_page.php! Instead of copy/pasting out of the tutorial go here to download the full code.