Paging Results With PHP and MYSQL - Part 1

Posted by Dharshin on 10 May, 2009

1 comment so far

This item was filled under [ PHP ]

Lets say you got a site that prints out some result based on a search or lists some items and it has hundreds of rows. Therefore, you end up having a lengthy list, which is hard to read. If your list contains images, hundreds of images get loaded on a single page. This is not appealing. I’m going to show you a way of dividing results into several pages and allow users to navigate through there results.

For this example, I am using mysql database called temp and a table called links.

The following code is the SQL statements i ran to generate a sample database for this example. Database name is called ‘temp’ and it has one table called ‘links’ (see SQL statements bellow). Then I have added some data to that table.

View Code MYSQL

CREATE DATABASE 'temp';
 
 
USE 'temp';
 
 
CREATE TABLE 'links' (
  'hid' int(11) NOT NULL auto_increment,  
  'sitename' varchar(30) NOT NULL default '',  
  'headlinesurl' varchar(200) NOT NULL default '',  
  PRIMARY KEY  ('hid'),   KEY 'hid' ('hid')
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=27 ;
 
 
INSERT INTO 'links' ('hid', 'sitename', 'headlinesurl') VALUES
(1, 'Dharshins Home', 'http://dharshin.freehostia.com'),
(2, 'Dharshins Blog', 'http://dharshin.blogspot.com'),
(3, 'BrunchingShuttlecocks', 'http://www.brunching.com/brunching.rdf'),
(4, 'DailyDaemonNews', 'http://daily.daemonnews.org/ddn.rdf.php3'),
(5, 'DigitalTheatre', 'http://www.dtheatre.com/backend.php3?xml=yes'),
(6, 'DotKDE', 'http://dot.kde.org/rdf'),
(7, 'FreeDOS', 'http://www.freedos.org/channels/rss.cgi'),
(8, 'Freshmeat', 'http://rss.freshmeat.net/freshmeat/feeds/fm-releases-global'),
(9, 'Gnome Desktop', 'http://www.gnomedesktop.org/backend.php'),
(10, 'HappyPenguin', 'http://happypenguin.org/html/news.rdf'),
(11, 'HollywoodBitchslap', 'http://hollywoodbitchslap.com/hbs.rdf'),
(12, 'Learning Linux', 'http://www.learninglinux.com/backend.php'),
(13, 'LinuxCentral', 'http://linuxcentral.com/backend/lcnew.rdf'),
(14, 'LinuxJournal', 'http://www.linuxjournal.com/news.rss'),
(15, 'LinuxWeelyNews', 'http://lwn.net/headlines/rss'),
(16, 'Listology', 'http://listology.com/recent.rdf'),
(17, 'MozillaNewsBot', 'http://www.mozilla.org/newsbot/newsbot.rdf'),
(18, 'NewsForge', 'http://www.newsforge.com/newsforge.rdf'),
(19, 'NukeResources', 'http://www.nukeresources.com/backend.php'),
(20, 'WebReference', 'http://webreference.com/webreference.rdf'),
(21, 'PDABuzz', 'http://www.pdabuzz.com/netscape.txt'),
(22, 'PHP-Nuke', 'http://phpnuke.org/backend.php'),
(23, 'PHP.net', 'http://www.php.net/news.rss'),
(24, 'PHPBuilder', 'http://phpbuilder.com/rss_feed.php'),
(25, 'PerlMonks', 'http://www.perlmonks.org/headlines.rdf'),
(26, 'TheNextLevel', 'http://www.the-nextlevel.com/rdf/tnl.rdf');

View Code XML

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html;charset=iso-8859-1" />
<title>File Upload Form</title>
<style type="text/css">
	<!--
 
	.style1 {
		font-family:Arial, Helvetica, sans-serif;
		font-weight:bold;
		font-size:12px;
		color:#000099;
	}
 
	.style2{font-family: Arial, Helvetica, sans-serif}
 
	.style3{font-size: 12px}
 
	.style4 {color:#000099}
 
	-->
</style>
</head>
<body>
<table width="593" border="0" cellspacing="0"cellpadding="0">
  <tr>
    <td width="105"><div align="center"class="style1">Headline ID </div></td>
    <td width="183"><div align="center"><spanclass="style1">Site Name</span> </div></td>
    <td width="305"><div align="center"class="style1">Feed URL </div></td>
  </tr>

Here I have created some styles and a table. With our PHPcode we are going to fill values to this table.

8af6fd17d21b20f67b2c0ff27e779ba9002

First two lines are to make the connection to the database

Then, we have 3 variables. $page is the page number. $totResultsholds the value of total number of results in out table. $maxResults is the number of items we wish to see per page.

There are many ways to do this but I’m using variable passedin the URL. For the first page we only have the .php in the URL, but for the second page we’ll have ?page=2 appended to the URL. So it make sense to someone who’s looking at the URL. We are not appending anything to the first page and later we are making the calculations based on this page value, therefore forthe first page we need to set the $page value manually.

Then we calculate the total number of rows given to us by the database. I am just using SELECT * FROM query, bit you can use any to suit you need.

Here is the trick. We are using LIMIT option in SQL to limit out results. After the LIMIT is the stating position, then the next value isthe number of entries. So “SELECT * FROM headlines LIMIT 30 10” give us the results from 30 to 40. Now we need a way to set the from value. For that,we use page number. Our page numbers go from 1, 2, 3, 4 and so on. For thrfirst page out page value is 1 and results should start from 0 the item. The second page number is 2 and the page value should start from 10 (lets say we are going to have 10 items per page. First page has items from 0 to 9). So startingitem= (page number -1)* results per page. Then we close the table tag.

If you got that, 80% of this is done. The rest is simple. We are putting each item in a new row, in that while loop. We are only going to have a NEXT and a PREVIOUS for navigation. In the first page we are disabling the previous. That is simple. Now we need to find if we are at the last page to disable the next link. If the position of the lat result is smaller than the total number of items, we have not still come to the last page.

View Code XML