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:  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:  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:  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:  Next: Conclusion >>
More MySQL Articles
More By Joe O'Donnell |
/ 32 
