Using the Page Navigator

In order to use the page navigator to page through a result set, you'll need a database and a table. Almost any database will do; feel free to use one that you have at hand, but for your convenience, the following SQL statement creates the table used with the code example:

CREATE TABLE `tblbooks` (
  `inventorynumber` int(11) NOT NULL auto_increment,
  `cat` char(3) NOT NULL default '',
  `title` varchar(150) NOT NULL default '',
  `author` varchar(100) NOT NULL default '',
  `publisher` varchar(4) NOT NULL default '',
  `sold` tinyint(1) default 0,
  PRIMARY KEY  (`inventorynumber`),
  KEY `authidx` (`author`)
)

This is a fairly simple table, but it's perfectly adequate for your needs—as long as it's populated with a sufficient number of records. The example shows five records per page, so at least six records are required.

Note:

The SQL to create this table and insert a number of records is available with the downloads for this chapter. Find the file books.sql.

The code to use the page navigator with a result set is very similar to the code you used when testing the DirectoryItems class. I'll comment on the differences only.

require 'MySQLConnect.php';
require 'PageNavigator.php';
define("OFFSET", "offset");
//get query string
$offset = @$_GET[OFFSET];
//max per page
define("PERPAGE", 5);
//check variable
if (!isset($offset)){
    $recordoffset = 0;
}else{
    //calc record offset
    $recordoffset = $offset * PERPAGE;
}

To this point, the code is identical to the code in Chapter 8, but the MySQLConnect class replaces the DirectoryItems class. Remember that the MySQLResultSet class has been included within the MySQLConnect.php file, so it doesn't need to be included here with a require statement.

$category = ❶@$_GET["category"];
//check variable
if (!isset($category)){
    $category = "LIT";
}

To demonstrate the versatility of the PageNavigator class, another name/value pair is passed to this page. In addition to the $offset value, you pass in ❶ a $category value. Doing this allows you to use the identical query for any category of books you choose by simply adding another criterion to the WHERE clause of your SQL. Using the $category value also demonstrates, as I promised earlier, how the final parameter passed to the page navigator (in this case, $otherparameter) is used—but more about that shortly.

Ordering, Filtering, and Extracting

In plain English, your SQL statement (Listing 9-2) allows you to select the author and title for unsold books in the specified category. The books are ordered by the author name.

Listing 9-2. The SQL statement

$strsql = "SELECT author, title ".
    "FROM tblbooks ".
    "WHERE sold = 0 AND cat = '$category' ".
    "ORDER BY author LIMIT ❶$recordoffset,". ❷PERPAGE;

The MySQLResultSet class is created using a fairly simple SQL query with a LIMIT clause. This clause performs the same function as the getArraySlice method of the DirectoryItems class by selecting only a portion of the total. Notice that ❶ the first parameter—$recordoffset—indicates the start position within the result set, and ❷ the second parameter—PERPAGE—indicates the number of records that will be returned.

You create an instance of a MySQLConnect object by passing in the required parameters: host, username, and password.

$con = new MySQLConnect('localhost', 'webuser', 'webpassword'),

For the sake of clarity, literal values are shown, but in a real-life situation, you would probably want to use variables rather than literals and perhaps for security reasons, locate the file that contains these variables outside the web directory. Substitute values appropriate to your MySQL database for the literals given above. Likewise with the database name used when creating a result set.

Using a method of the MySQLConnect object, you create a MySQLResultSet—$rs.

//get result set
$rs = $con->createResultSet($strsql, 'mydatabase'),

The constructor for the result set class selects the database and executes the query against it.

Traversing the Result Set

All that remains before displaying your page navigator is to traverse the result and output it.

echo "<div style="text-align:center">";
while($row = $rs->getRow()){
    echo $row[0]." - ".$row[1];
    echo "<br />
";
}
echo "<br />";
echo "</div>
";

The getRow method of a MySQLResultSet calls the PHP function mysql_fetch_array, retrieving the current record and moving the record pointer forward to the next record. (This is a perfectly adequate way of iterating through your results, but you will develop a different approach in Chapter 10.) There are only two fields in your result set, and both of these are echoed to the screen centered within a div tag.

Your Navigator Needs Directions

Next, you need to collect the information needed by the page navigator.

$pagename = basename($_SERVER['PHP_SELF']);
//find total number of records
$totalrecords = $rs->getUnlimitedNumberRows();
$numpages = ceil($totalrecords/PERPAGE);

In Chapter 8, the DirectoryItems class simply called the built-in count function of an array to determine the total number of items but here the method getUnlimitedNumberRows is used. This method returns the total number of records that there would be if the SQL statement shown in Listing 9-2 was executed without a LIMIT clause. Remember, the LIMIT clause allows you to return a selection of records much like the getFileArraySlice method of the DirectoryItems class.

//create category parameter
$otherparameters = "&category=LIT";

It is often the case that web pages are invoked passing a query string that contains a number of name/value pairs; this is the purpose of the $otherparameters variable. When you used the PageNavigator class with the DirectoryItems class, you ignored this parameter and let it default to an empty string. Here, you are only passing one name/value pair, but any number may be passed as long as they are formatted properly using the character entity for an ampersand (&) and an equal sign (=). (In some cases, you may also need to URL-encode them.)

//create if needed
if($numpages > 1){
    //create navigator
    $nav = new PageNavigator($pagename, $totalrecords, PERPAGE,
        $recordoffset, 4, $otherparameters);
    echo $nav->getNavigator();
}

This PageNavigator instance is slightly different from the one in Chapter 8. In that chapter, you let the last two parameters default, but because you are making use of $otherparameters, and because this variable is the last value passed to the PageNavigator constructor, you have no choice but to specify all preceding values.

Note:

Remember that no parameter may have a default value if it is followed by a parameter with a specified value. (PHP enforces this at call time, not when the method is defined.)

Recall that the second-to-last value passed to the navigator determines the width of the navigator and the number of links shown. In the preceding code, its value is 4.

How the navigator actually appears depends on the number of records in the tblbooks table and, of course, on how you have configured the CSS classes that control the navigator's appearance. If you have been following along and coding as you read, you'll see that the PageNavigator class functions every bit as well with a database as it did with the DirectoryItems class—it is a reusable object.