Home arrow MySQL arrow Page 5 - Building A Dynamic MySQL Paging Class With PHP
MYSQL

Building A Dynamic MySQL Paging Class With PHP


One handy class that Joe always keeps in his PHP toolbox is a custom MySQL recordset paging class, which he developed about 5 months ago. In this article Joe describes how he created the MySQL recordset paging class with PHP and also shows us how to use it with three cool examples!

Author Info:
By: Joe O'Donnell
Rating: 4 stars4 stars4 stars4 stars4 stars / 49
April 04, 2002
TABLE OF CONTENTS:
  1. · Building A Dynamic MySQL Paging Class With PHP
  2. · The class
  3. · The RecNav constructor
  4. · The ShowRecs function
  5. · Using the RecNav class
  6. · Conclusion

print this article
SEARCH DEVARTICLES

Building A Dynamic MySQL Paging Class With PHP - Using the RecNav class
(Page 5 of 6 )

Let's jump straight in and test the RecNav class out. If you haven't done so already, then make sure you download the support material for this article, which includes the RecNav class.

Using the MySQL console application, create the following database and table:

create database joesBooks;

use joesBooks;

create table Books

(

bookId int auto_increment not null,

bookTitle varchar(50) not null,

bookPrice decimal(3,2) default 0.00,

primary key(bookId),

unique id(bookId)

);

insert into Books values(0, 'Professional ADO.NET', 23.50);

insert into Books values(0, 'Apache Server Unleashed', 29.50);

insert into Books values(0, 'ASP.NET Unleashed', 39.95);

insert into Books values(0, 'Introducing .NET', 24.45);

insert into Books values(0, 'Professional C#', 38.45);

insert into Books values(0, 'Beginning C++', 41.40);

insert into Books values(0, 'Beginning J++', 44.30);

insert into Books values(0, 'Beginning PHP', 55.50);

insert into Books values(0, 'Beginning MySQL', 98.20);

insert into Books values(0, 'Beginning Visual Basic', 58.95);

insert into Books values(0, 'Beginning XHTML', 39.95);

insert into Books values(0, 'Hacking Exposed', 22.20);

insert into Books values(0, 'Effective Java', 91.20);

insert into Books values(0, 'JavaScript Bible', 33.55);

insert into Books values(0, 'Beginning PHP4 and XML', 48.50);

insert into Books values(0, 'VBScript Regular Expressions', 49.50);

insert into Books values(0, 'Programming ASP', 49.50);

insert into Books values(0, 'Programming PHP', 49.50);

insert into Books values(0, 'Programming C#', 49.50);

insert into Books values(0, 'Programming Java', 49.50);

insert into Books values(0, 'Introducing XML', 33.95);

insert into Books values(0, 'Introducing ASP', 33.95);

insert into Books values(0, 'Introducing Java', 33.95);

insert into Books values(0, 'Introducing JavaScript', 33.95);

insert into Books values(0, 'Introducing XPath and XQuery', 33.95);

insert into Books values(0, 'Beginning ASP Databases', 84.50);

insert into Books values(0, 'Visual Basic 6.0 Explained', 89.50);

insert into Books values(0, 'Web Services and C#', 47.95);

insert into Books values(0, 'Professional XSL', 49.95);

insert into Books values(0, 'PHP, SOAP, WDDI and WSDL', 59.95);


RecNav is stored in class.recnav.php, and we can use the following code to instantiate it, connect to MySQL on the local server, and page through the books table, 10 records at a time:

<html>

<head>

<title> Recordset Paging Example </title>

</head>

<body bgcolor="#FFFFFF">

<?php

require("class.recnav.php");



global $HTTP_GET_VARS;

$page = $HTTP_GET_VARS["page"];



$s = mysql_connect("localhost", "admin", "password");

$d = mysql_select_db("joesBooks", $s);

$r = new RecNav($s, "SELECT * FROM books ORDER BY bookId ASC", "", "", "", 10);



echo $r->ShowRecs($page);



?>

</body>

</html>


Seven lines of PHP code and we're in business with the RecNav class. The code above outputs everything we need to display 10 records per page, complete with paging links. Here's how it looks my browser:

The RecNav class in action

When I click on any of the paging links at the bottom, the script happily displays the correct books. Here's page three of the results:

Page three of the books

Notice how there's no next link and also no trailing |. It's nice to see the example above, but I haven’t really demonstrated the power and flexibility of the RecNav class yet have I? Well, if I create an instance of the RecNav class with this code:

<html>

<head>

<title> Recordset Paging Example </title>

</head>

<body bgcolor="#FFFFFF">

<?php

require("class.recnav.php");



global $HTTP_GET_VARS;

$page = $HTTP_GET_VARS["page"];



$headerTemplate = "<h1>Joes Bookshop</h1>";

$headerTemplate .= "<table width='300' border='1' bordercolor='maroon' cellspacing='0' cellpadding='0'>";

$headerTemplate .= "<tr>";

$headerTemplate .= "<td width='15%' height='20' bgcolor='maroon' align='center'>";

$headerTemplate .= " <font face='verdana' size='1' color='lightyellow'>";

$headerTemplate .= " ID";

$headerTemplate .= " </font>";

$headerTemplate .= "</td>";

$headerTemplate .= "<td width='85%' height='20' bgcolor='maroon' align='center'>";

$headerTemplate .= " <font face='verdana' size='1' color='lightyellow'>";

$headerTemplate .= " Book Title";

$headerTemplate .= " </font>";

$headerTemplate .= "</td>";

$headerTemplate .= "</tr>";



$bodyTemplate = "<tr>";

$bodyTemplate .= "<td width='15%' bgcolor='lightblue' align='center'>";

$bodyTemplate .= " <font face='verdana' size='1' color='darkblue'>";

$bodyTemplate .= " <| row0 |>";

$bodyTemplate .= " </font>";

$bodyTemplate .= "</td>";

$bodyTemplate .= "<td width='85%' bgcolor='lightyellow' height='50'>";

$bodyTemplate .= " <p style='margin-left:10'>";

$bodyTemplate .= " <a href='prod.php?prodId=<| row0 |>'>";

$bodyTemplate .= " <font face='verdana' size='1' color='red'>";

$bodyTemplate .= " <b><u><| row1 |></u></b>";

$bodyTemplate .= " </a>";

$bodyTemplate .= " </font>";

$bodyTemplate .= " <font face='verdana' size='1' color='black'>";

$bodyTemplate .= " <b><br>[ Only <| row2 |>] </b>";

$bodyTemplate .= " </font>";

$bodyTemplate .= "</td>";



$s = mysql_connect("localhost", "admin", "password");

$d = mysql_select_db("joesBooks", $s);

$r = new RecNav($s, "SELECT * FROM books ORDER BY bookId ASC", $bodyTemplate, $headerTemplate, "", 5);



echo $r->ShowRecs($page);



?>

</body>

</html>


Then I get the following output:

A cooler RecNav example

See what a different changing the templates made? The books now look much more presentable and I've used the power of RecNav's template system to actually link each book to another page. Here's the code that creates the links:

$bodyTemplate .= " <a href='prod.php?prodId=<| row0 |>'>";

$bodyTemplate .= " <font face='verdana' size='1' color='red'>";

$bodyTemplate .= " <b><u><| row1 |></u></b>";

$bodyTemplate .= " </a>";


When RecNav products the HTML output it replaces <| row0 |> with the ID of the book, so if I created a page called prod.php then I could actually use RecNav to build an eCommerce store as well!

There really are an unlimited number of ways that you can use RecNav. How about the following code, which grabs the books as an unordered list:

<html>

<head>

<title> Recordset Paging Example </title>

</head>

<body bgcolor="#FFFFFF">

<?php

require("class.recnav.php");



global $HTTP_GET_VARS;

$page = $HTTP_GET_VARS["page"];



$headerTemplate = "<h2>Joes Books</h2>";

$headerTemplate .= "<ul>";



$bodyTemplate = "<li><b><| row1 |>:</b> <| row2 |></li>";



$footerTemplate = "</ul>";



$s = mysql_connect("localhost", "admin", "password");

$d = mysql_select_db("joesBooks", $s);

$r = new RecNav($s, "SELECT * FROM books ORDER BY bookId ASC", $bodyTemplate, $headerTemplate, $footerTemplate, 5);



echo $r->ShowRecs($page);



?>

</body>

</html>


Running the code above gives me the following output:

Using RecNav to list records in a list
blog comments powered by Disqus
MYSQL ARTICLES

- MySQL and BLOBs
- Two Lessons in ASP and MySQL
- Lord Of The Strings Part 2
- Lord Of The Strings Part 1
- Importing Data into MySQL with Navicat
- Building a Sustainable Web Site
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- PhpED 3.2 – More Features Than You Can Poke ...
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- Security and Sessions in PHP
- Setup Your Personal Reminder System Using PHP
- Create a IP-Country Database Using PERL and ...
- Developing a Dynamic Document Search in PHP ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials