WebProNews

It is extremely common these days to make results display across multiple pages. Some examples are maybe browsing through picture galleries, store products, blog entries, etc.

Unless you are a veteran programmer, this might seem a little intimidating at first. Rest assured, it is an easy task.

In order to create your own paging system, you will need to get some information first. You will need the total number of list entries, number of results per page, current page number being viewed, the total number of pages and the offset.

Lets start with the total number of list entries. In order to do this, we need some test data in a MySQL table. For demonstration purposes, we are going to use the following MySQL table:

CREATE TABLE products ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL, name VARCHAR(50) NOT NULL, description TEXT NOT NULL ); INSERT INTO products (name,description) VALUES ('Product 1','Description for Product 1'); INSERT INTO products (name,description) VALUES ('Product 2','Description for Product 2'); INSERT INTO products (name,description) VALUES ('Product 3','Description for Product 3'); INSERT INTO products (name,description) VALUES ('Product 4','Description for Product 4'); INSERT INTO products (name,description) VALUES ('Product 5','Description for Product 5'); INSERT INTO products (name,description) VALUES ('Product 6','Description for Product 6'); INSERT INTO products (name,description) VALUES ('Product 7','Description for Product 7'); INSERT INTO products (name,description) VALUES ('Product 8','Description for Product 8'); INSERT INTO products (name,description) VALUES ('Product 9','Description for Product 9'); INSERT INTO products (name,description) VALUES ('Product 10','Description for Product 10');

Now that we have a MySQL table full of "fake" products, lets get the paging working!

First, we need the total number of list entries. To do this, we will use:

MySQL's COUNT() function. $result = mysql_query("SELECT COUNT(*) AS total_entries FROM products") or die(mysql_error()); $row = mysql_fetch_row($result); $total_entries = $row[0];

Now we have the total entries retrieved and stored in the $total_entries variable.

Next, we need to get the number of results per page. It is a common programming practice when developing scripts to use a central configuration file (such as config.php). For the sake of simplicity, we'll call our variable $entries_per_page.

$entries_per_page = 3;

Next, we need to get the current page number being viewed. We will call our page number variable >$page_number.

if(isset($_GET['page_number'])) { $page_number = $_GET['page_number']; } else { $page_number = 1; }

This means set $page_number to the page_number specified via query string if it exists, otherwise set it to page number 1.

Next, we need to get the total number of pages that there will be. An important thing to remember is that even if the very last page contains only one product, that one product will still be one page.

To do this, we use PHP's ceil() function. This means round up the number to the nearest integer. For example, ceil(2.1) will round up to 3. ceil(2.9) will round up to 3. In order to figure out the total number of pages, you simply do ceil(TOTAL ENTRIES / ENTRIES PER PAGE).

$total_pages = ceil($total_entries / $entries_per_page);

For this specific example, $total_pages has the value 4.

Finally, we need to determine the offset. What exactly is the "offset"? The offset is the number of the first entry to pull. It is what you use to tell MySQL where to start fetching data. For example, if we are viewing products on page two, we would want to return only products 4 through 7. If we are viewing products on page three, we would want to return only products 8 through 10. To get an offset, you simply do:

(CURRENT PAGE - 1) * ENTRIES PER PAGE

$offset = ($page_number - 1) * $entries_per_page;

Now that we have all of the data we require we are able to use MySQL to pull all products within our "page" being viewed.

$result = mysql_query("SELECT * FROM products LIMIT $offset, $entries_per_page") or die(mysql_error()); while($obj = mysql_fetch_object($result)) { // Display the data however you want here. print <<id - $obj->name - $obj->description<br>

EOD; }

And lastly, the whole point of this article is to demonstrate page numbering so now we will display the page numbers! What we are going to do is iterate through a loop that loops through all page numbers and makes them a link unless it's the current page.

for($i = 1; $i <= $total_pages; $i++) { if($i == $page_number) { // This is the current page. Don't make it a link. print "$i "; } else { // This is not the current page. Make it a link. print " <a href="products.php?page_number=$i">$i</a> "; } }

Of course, you could (and I encourage you to) experiment with the code shown here to make it display however you wish.

You can view a complete example of this script in action at http://www.phplabs.com/articles/products.php.

Add to Del.icio.us | DiggThis | Yahoo! My Web | Furl

Mike is an experienced web developer with a wide range of programming capabilities. He has been delivering top quality web applications for several years primarily using Perl/CGI and PHP combined with MySQL. His ability to deliver high quality solutions while meeting deadlines has put his services in high demand.