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!
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:
$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:
$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:
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:
MySQL 
/ 49 
