Paging Results With PHP and MYSQL - Part 1

Submitted by Dharshin on Sun, 04/01/2007 - 08:34.

Lets say you got a site that prints out some result based ona search or lists some items and it has hundreds of rows. Therefore, you end uphaving 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’mgoing to show you a way of dividing results into several pages and allow usersto navigate through there results.

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

Use the following SQL instructions to create the database.

CREATE DATABASE `temp` DEFAULT CHARACTER SET latin1 COLLATElatin1_swedish_ci;

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');

<!DOCTYPE htmlPUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<htmlxmlns="http://www.w3.org/1999/xhtml">

<head>

<metahttp-equiv="Content-Type" content="text/html;charset=iso-8859-1" />

<title>UntitledDocument</title>

<styletype="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>

<tablewidth="593" border="0" cellspacing="0"cellpadding="0">

<tr>

<tdwidth="105"><div align="center"class="style1">Headline ID </div></td>

<tdwidth="183"><div align="center"><spanclass="style1">Site Name</span> </div></td>

<tdwidth="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.

<?php

$conn=mysql_connect('localhost', 'mysql', ‘mysqlpwd’)or die(mysql_error());

mysql_select_db('temp', $conn);

$page;

$totResults;

$maxResults=10;

if(isset($_GET['page'])) {

$page=$_GET['page'];

}

else {

$page=1;

}

/* Getting the total number of results in thedatabase */

$calcRowsQuery="Select COUNT(*) as rowsFROM headlines";

$calcRowsRes=mysql_query($calcRowsQuery,$conn);

$totResults=mysql_result($calcRowsRes, 0);

$from=$maxResults*($page-1);

$resQuery="SELECT * FROM headlines LIMIT$from, $maxResults";

$result=mysql_query($resQuery);

while($row = mysql_fetch_array($result)) {

echo "<trclass='style3'>";

echo "<td>".$row['hid']."</td>";

echo "<td>".$row['sitename']."</td>";

echo " <td>".$row['headlinesurl']."</td>";

echo "</tr>";

}

echo "</table> \n <pclass='style3'> <center>";

if($page != 1)

{

echo "<ahref='paging_results.php?page=". ($page-1) ."'> Previous</a>";

}

if($totResults > ($from+$maxResults))

{

echo "<ahref='paging_results.php?page=". ($page+1) ."'> Next</a>";

}

echo "</center></p> \n";

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 thenumber 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 thesecond page we’ll have ?page=2 appended to the URL. So it make sense to someonewho’s looking at the URL. We are not appending anything to the first page andlater 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 bythe database. I am just using SELECT * FROM query, bit you can use any to suityou need.

Here is the trick. We are using LIMIT option in SQL to limitout 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 theresults 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. Thesecond page number is 2 and the page value should start from 10 (lets say weare 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. Weare putting each item in a new row, in that while loop. We are only going tohave 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 pageto disable the next link. If the position of the lat result is smallerthan the total number of items, we have not still come to the last page.

</body>

</html>

Finally, we close body and HTML tags.

Next I’ll show you how to add a last and first and pagenumbers to the navigation area.

Thank you

Dharshin De Silva

Bookmark/Search this post with: