DATABASE CLASSES

The last chapter ended by saying we would create a database class in order to test the
versatility of the page navigator. That’s what we’re going to do in this chapter. I noted earlier
that it’s sometimes difficult to identify objects, because often what’s needed is something conceptual rather than something physical and concrete. The database class or classes that we are going to create in this chapter are definitely of this nature. We can probably determine some of the requirements by looking at the DirectoryItems class—after all, as you learned in Chapter 5, this class is similar to a database table.

Using What You Know

Pursuing this line of thought, you need to:

Filter and order records
Know the total number of records
Be able to extract sequential subsets of the total

In the context of database classes, the description of the requirements immediately suggests the use of the SQL ORDER BY, WHERE, and LIMIT clauses to order, filter, and extract subsets respectively. You had to create this kind of functionality for the DirectoryItems class, but why recreate what’s already available in SQL?
Just as PHP’s built-in image manipulation functions helped create the ThumbnailImage class, look also for assistance from the existing MySQL-related functions. If you’ve used these functions before, you’ll immediately know which ones are the most important and the most commonly required. Obvi- ously, you’ll need mysql_connect to create a connection to a specific server. Creating a connection is a prerequisite for using mysql_select_db to select
a database and mysql_query to execute a query and return a result set of rows.

One Lump or Two?

There are two distinct classes that can be built around these existing PHP MySQL functions, depending upon your preferences: a connection class and a result set class. First, you’ll create a database connection class, as you might imagine, making use of the mysql_connect function. A connection is server- specific and can be used to create any number of result sets taken from any database on that server. It simply sets up communication between a web page and a database server. A result set makes use of a connection in order to
update or display data. You’ll build a MySQL result set class around the
mysql_select_db and mysql_query functions.
You will develop fairly skeletal versions of these two classes, emphasizing any unexplored areas of object-oriented programming (OOP). Nevertheless, these classes will be perfectly fit for the task of testing the versatility of the PageNavigator class.
In this chapter, we’ll take a slightly different approach to the code. I’ll show the data members and the methods of the class, but I’ll only reproduce code that requires comment. As usual, the complete code is available at the companion website, so don’t hesitate to download it and refer to it if you find this helpful.

The MySQLConnect Class

The MySQLConnect class is a fairly modest class with only two data members and four public methods.

//data members private $connection
private static $instances = 0
//methods
public function __construct($hostname, $username, $password)
public function __destruct()
public function createResultSet($strSQL, $databasename)
public function close()

What is immediately noteworthy about this class is the use of the keyword static to modify a data member. Identifying a variable as static means that it is shared among all instances of a class. If one instance changes the value of a static variable, it is changed for all instances. Unique variables are created each time a class is instantiated, and they belong to that specific instance. Not so with static variables—they belong to the class as a whole (and for this reason are sometimes referred to as class variables). Let’s look at the code for the constructor and see how this can be useful.

A Class-Conscious Variable

The parameters passed to the constructor are those necessary to make a database connection using the built-in PHP function, mysql_connect. Again, this method is a wrapper method but with a few additional bells and whistles.

public function __construct($hostname, $username, $password){
if( MySQLConnect::$instances == 0){
$this->connection = mysql_connect($hostname,$username,$password) or die ( mysql_error(). " Error no:".mysql_errno()); MySQLConnect::$instances = 1;
}else{
$msg = "Close the existing instance of the ". "MySQLConnect class.";
die($msg);
}
}

This class won’t be instantiated if there is already an existing instance.
If the $instances variable has a value of 0, a connection is made to the server, and the value of $instances is set to 1. Checking the value of this static variable makes sense because it is shared among all instances, it’s available to all instances, and its value is the same for all instances.
The syntax for referencing a static variable ( ) is different from that used to reference a normal data member. It would not make sense to use the pseudo-variable $this with $instances, since $this refers to the current object and by definition, static variables belong to the class rather than a specific instance. Quite sensibly, the class name is used instead, and the arrow operator is replaced by a double colon—the scope resolution operator.
The scope resolution operator is principally used when referencing static data members or static methods. In Chapter 10 you’ll see the two other occasions when this operator is used, but for now you need only concern yourself with its use with static data members. When referencing a static variable from within its class, you also have the option of replacing the class name with the keyword self. In this case, the expression self::$instances is equivalent to MySQLConnect::$instances. Static members referenced outside the confines of their class must use the class name. You don’t need to worry about that here, since $instances is private and cannot be referenced outside the MySQLConnect class.

At this point you may be thinking, “That’s all well and good, but why would I want a class that I can only create one instance of ?” Creating a database connection is an expensive operation so restricting creation of connections conserves resources.

NOTE By restricting the connection class to a single instance, we are mimicking the built-in mysql_connect function. Its default behavior is to reuse a connection resource rather than create a new one.

However, there are some circumstances where a new connection is a necessity.

Making Other Connections

Two different connection objects are required if a single script needs to con- nect to two different servers. The close method makes it possible to connect to a different server.

public function close(){ MySQLConnect::$instances = 0; if(isset($this->connection)){
mysql_close($this->connection);
unset($this->connection);
}
}

Two instances of the MySQLConnect class can exist, but not simultaneously. If you want to create a connection to another server, you must first close the existing connection. The close method closes the current connection and resets the static variable $instances to 0. Manipulating the $instances variable in this way allows you to create a new connection, but only after the current one is closed.
Explicitly closing a connection and unsetting it makes for clearer error messages should you accidentally call a result set method after closing its connection. The requirement to close the current connection also serves
as a reminder that a result set is a dependent object.
To make this even clearer, let’s look at how a result set is created.

You Can Only Get There from Here

The following method serves as a very strong reminder that you first need a connection in order to create a result set:

public function createResultSet($strSQL, $databasename){
$rs = new MySQLResultSet($strSQL, $databasename, $this->connection );
return $rs;
}

The creation of a MySQLResultSet requires a reference to the connection data member of the MySQLConnect class. This data member is private and does not have an accessor method, so it’s only available from within the MySQLConnect

class. Short of reverting to procedural programming to create a connection resource, you cannot create an instance of the MySQLResultSet class except by using this method of the MySQLConnect class. This makes it very clear that a result set is a dependent object. You can’t create one without first having a connection to a server. Instantiating an object of the MySQLResultSet class from within the MySQLConnect class serves not only to remind you of this dependency, but it enforces it programmatically. To understand the connection class, you’ve had to look ahead at the constructor for the result set class.
Let’s examine the rest of this class in detail.

The MySQLResultSet Class

Not surprisingly, the MySQLResultSet class (shown in Listing 9-1) has more data members and methods than the MySQLConnect class. However, in many ways, it’s a much simpler class and requires much less explanation. To get an overview of this class, find all its data members and methods listed here:

//data members private $strSQL private $databasename private $connection private $result
// public methods
public function __construct($strSQL, $databasename, $connection)
public function __destruct()
//return current record public function getRow()
//accessor method for returning database name public function getDatabaseName()
public function getNumberColumns()
public function getNumberRows()
//get id of most recently inserted record public function getInsertId()
//find total number without a LIMIT clause
public function getUnlimitedNumberRows()
public function getFieldNames()
public function findVersionNumber()
//private methods
//make sure the sql is a SELECT
private function checkForSelect()
//close result set and unset private function close()
//version specific count methods private function countVersionFour()
private function countVersionThree($tempsql, $end)

Listing 9-1: The MySQLResultSet class

You’ve already seen the constructor for this class, but a few general comments are in order before looking at any of the methods in more detail. One notable absence from the list of methods is a method equivalent to the

getArraySlice method of the DirectoryItems class. You could have created something equivalent by selecting all the required records and then using the built-in function mysql_data_seek to reposition the record pointer as necessary, but the price to pay for this relatively easy implementation would be poor performance. Imagine paging through 1,000 records 10 records at a time and for each page, bringing over all 1,000 records. The more scalable solution is to restrict the number of records selected by using a LIMIT clause in the SQL that creates the result set.
However, in order for your page navigator to function, you also need to know the total number of records without a LIMIT clause. With MySQL versions
4.0 and higher, there is an easy way of doing this using SQL_CALC_FOUND_ROWS, followed by a call to the FOUND_ROWS function. For MySQL version 3, you can use the COUNT function without a LIMIT clause.
This is a fairly easy process to automate, so to make things easier on yourselves and your client programmers, you create the getUnlimitedNumberRows method. Briefly, the getUnlimitedNumberRows method confirms that the query is a SELECT, determines the MySQL version number, and discovers the total number of records that would be returned without a LIMIT clause by calling the private method countVersionThree or countVersionFour.
Most of the remaining methods are simply wrapper methods for exist- ing MySQL functions, or they make use of these functions to perform fairly straightforward tasks. You won’t actually be using some of these methods— getNumberColumns, for instance—but they give you an idea of how this class could be expanded.
This isn’t the last you’ll see of the MySQLResultSet class. We’ll return to it again in Chapter 10 because it provides an ideal opportunity for further exploring OO programming. Right now though, your primary concern is to see how it functions with the PageNavigator class.

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.

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

Listing 9-2: The SQL statement

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 />\n";
}
echo "<br />";
echo "</div>\n";

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 = "&amp;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.

Where to Go After the Navigator

We developed these database classes because they are useful in themselves, but they also show the versatility of the PageNavigator class—this is not a one trick pony but a class that can be reused in a variety of situations. Along the way, you’ve also learned more about OOP and the process of class creation. This is not something that takes place in a vacuum. Knowledge of existing PHP functions and of SQL was essential to the process and conditioned the result. What you already know about PHP as a procedural programmer and about SQL has proven to be an invaluable asset.
In the next chapter we’ll improve on the database classes introduced here and explore one of the most important concepts of OOP, inheritance. We’ll also look at one of the classes built in to PHP 5, namely Exception. From now on we will make use of classes built in to PHP 5 so code compatible with PHP 4 can no longer be provided.