Writing an Object-Oriented MySQL Interface for PHP

2017-11-03 09:05:05

2.10.1 Problem

You want an approach for writing PHP scripts that is less tied to PHP's native MySQL-specific functions.

2.10.2 Solution

Use one of the abstract interfaces that are available, or write your own.

2.10.3 Discussion

You may have noticed that the Perl, Python, and Java operations that connect to the MySQL server each return a value that allows you to process queries in an object-oriented manner. Perl has database and statement handles, Python has connection and cursor objects, and Java uses objects for everything in sight: connections, statements, result sets, and metadata. These object-oriented interfaces all are based on a two-level architecture.

The top level of this architecture provides database-independent methods that implement database access in a portable way that's the same no matter which database management system you're using, be it MySQL, PostgreSQL, Oracle, or whatever. The lower level consists of a set of drivers, each of which implements the details for a particular database system. The two-level architecture allows application programs to use an abstract interface that is not tied to the details involved with accessing any particular database server. This enhances portability of your programs, because you just select a different lower-level driver to use a different type of database. That's the theory, at least. In practice, perfect portability can be somewhat elusive:

The interface methods provided by the top level of the architecture are consistent regardless of the driver you use, but it's still possible to issue SQL statements that contain constructs supported only by a particular server. For MySQL, a good example is the SHOW statement that provides information about database and table structure. If you use SHOW with a non-MySQL server, an error is the likely result.

Lower-level drivers often extend the abstract interface to make it more convenient to get at database-specific features. For example, the MySQL driver for DBI makes the most recent AUTO_INCREMENT value available as an attribute of the database handle so that you can access it as $dbh->{mysql_insertid}. These features often make it easier to write a program initially, but at the same time make it less portable and require some rewriting should you port the program for use with another database system.

Despite these factors that compromise portability, the two-level architecture provides significant benefits for Perl, Python, and Java programmers. It would be nice to use this approach when writing PHP scripts, too, but PHP itself provides no such support. Its interface to MySQL consists of a set of functions, and these are inherently non-portable because their names all are of the form mysql_xxx( ). To work around this, you can write your own database abstraction mechanism.

That is the purpose of this section. It shows how to write an object-oriented PHP interface that hides many MySQL-specific details and is relatively database independentcertainly more so than PHP's function-based MySQL interface. As discussed here, the interface is written specifically for MySQL, but if you want to adapt it for use with a different database, you should be able to do so by supplying a different set of underlying class methods.

If you want to write PHP scripts in a database-independent fashion, but prefer not to write your own interface, you can use a third-party abstraction interface. One such is the database-access class that is a part of the PHP Extension and Add-on Repository (PEAR). PEAR is included with current releases of PHP 4.

The following discussion shows how to write a MySQL_Access class that implements an object-oriented interface to MySQL, and a Cookbook_DB_Access class that is built on top of MySQL_Access but automatically supplies default values for connecting to the cookbook database. (If you're not familiar with PHP classes, you may want to consult the "Classes and Objects" chapter of the PHP manual for background information.) The primary goal of this class interface is to make it easier to use MySQL by reducing the number of operations your scripts must perform explicitly:

The interface automatically establishes a connection to the MySQL server if you issue a query without connecting first; you need never issue a connect call explicitly. The connection parameters must be specified somehow, of course, but as we'll see, that can be done automatically as well.

The interface provides automatic error checking for MySQL calls. This is more convenient than checking for them yourself, and helps eliminate one of the most common problems with PHP scripts: failure to check for database errors on a consistent basis. The default behavior is to exit with an error message when a problem occurs, but you can override that if you want to handle errors yourself.

When you reach the end of a result set while fetching rows, the class automatically releases the set.

The class-based interface also provides a method for quoting data values to make them safe for use in queries, and a placeholder mechanism so you don't need to do any quoting at all if you don't want to. These capabilities are not present in PHP's native function-based interface.

The following example illustrates how using an object-oriented interface changes the way you write PHP scripts to access MySQL, compared to writing function-based scripts. A script based on PHP's native function calls typically accesses MySQL something like this:

if (!($conn_id = mysql_connect ("localhost", "cbuser", "cbpass"))) die ("Cannot connect to database "); if (!mysql_select_db ("cookbook", $conn_id)) die ("Cannot select database "); $query = "UPDATE profile SET cats=cats+1 WHERE name = 'Fred'"; $result_id = mysql_query ($query, $conn_id); if (!$result_id) die (mysql_error ($conn_id)); print (mysql_affected_rows ($conn_id) . " rows were updated "); $query = "SELECT id, name, cats FROM profile"; $result_id = mysql_query ($query, $conn_id); if (!$result_id) die (mysql_error ($conn_id)); while ($row = mysql_fetch_row ($result_id)) print ("id: $row[0], name: $row[1], cats: $row[2] "); mysql_free_result ($result_id);

A first step toward eliminating some of that code is to replace the first few lines by calling the cookbook_connect( ) function from the PHP library file, Cookbook.php, developed in Recipe 2.4. That function encapsulates the connection and database selection operations:

include "Cookbook.php"; $conn_id = cookbook_connect ( ); $query = "UPDATE profile SET cats=cats+1 WHERE name = 'Fred'"; $result_id = mysql_query ($query, $conn_id); if (!$result_id) die (mysql_error ($conn_id)); print (mysql_affected_rows ($conn_id) . " rows were updated "); $query = "SELECT id, name, cats FROM profile"; $result_id = mysql_query ($query, $conn_id); if (!$result_id) die (mysql_error ($conn_id)); while ($row = mysql_fetch_row ($result_id)) print ("id: $row[0], name: $row[1], cats: $row[2] "); mysql_free_result ($result_id);

A class-based interface can carry encapsulation further and shorten the script even more by eliminating the need to connect explicitly, to check for errors, or to close the result set. All of that can be handled automatically:

include "Cookbook_DB_Access.php"; $conn = new Cookbook_DB_Access; $query = "UPDATE profile SET cats=cats+1 WHERE name = 'Fred'"; $conn->issue_query ($query); print ($conn->num_rows . " rows were updated "); $query = "SELECT id, name, cats FROM profile"; $conn->issue_query ($query); while ($row = $conn->fetch_row ( )) print ("id: $row[0], name: $row[1], cats: $row[2] ");

A class interface can make MySQL easier to use by reducing the amount of code you need to write when creating new scripts, but it has other benefits as well. For example, it can also serve as a recipe translation aid. Suppose a program in a later chapter is shown in Perl, but you'd rather use in it PHP and there is no PHP version on the Cookbook web site. Perl DBI is object oriented, so you'll likely find it easier to translate a Perl script into a PHP script that is object oriented, rather than into one that is function based.

2.10.4 Class Overview

The class interface implementation uses the PHP recipes and techniques developed earlier in this chapter, so you should familiarize yourself with those. For example, the class interface needs to know how to make connections to the server and process queries, and we'll use include (library) files to encapsulate the interface so that it can be used easily from multiple PHP scripts.

The interface shown here works only with PHP 4. This is something that is not true of PHP's native MySQL routines, which work both with PHP 3 and PHP 4. The restriction is necessitated by the use of a few constructs that are not available or do not work properly in PHP 3. Specifically, the interface assumes the availability of the include_once statement and the PHP NULL value. It also assumes that count( ) correctly counts unset values in arrays, which is true only for PHP 4.

The implementation strategy involves two classes. The first is a generic base class MySQL_Access that provides the variables and methods needed for using MySQL. The second is a derived class Cookbook_DB_Access that has access to everything in the base class but automatically sets up connection parameters specifically for the cookbook database so we don't have to do that ourselves. An alternative implementation might use just a single class and hardwire the cookbook database parameters directly into it. However, writing the base class to be generic allows it to be used more easily for scripts that access a database other than cookbook. (For such scripts, you'd just write another derived class that uses the base class but provides a different set of connection parameters.)

A PHP class definition begins with a class line that specifies the class name, then defines the variables and methods associated with the class. An outline of the base class, MySQL_Access, looks like this:

class MySQL_Access { var $host_name = ""; var $user_name = ""; var $password = ""; var $db_name = ""; var $conn_id = 0; var $errno = 0; var $errstr = ""; var $halt_on_error = 1; var $query_pieces = array ( ); var $result_id = 0; var $num_rows = 0; var $row = array ( ); # ... method definitions ... } # end MySQL_Access

The class definition begins with several variables that are used as follows:

The first few variables hold the parameters for connecting to the MySQL server ($host_name, $user_name, $password, and $db_name). These are empty initially and must be set before attempting a connection.

Once a connection is made, the connection identifier is stored in $conn_id. Its initial value, 0, means "no connection." This allows a class instance to determine whether or not it has connected to the database server yet.

$errno and $errstr hold error information; the class sets them after each MySQL operation to indicate the success or failure of the operation. The initial values, and the empty string, mean "no error." For errors that occur but not as a result of interacting with the server, $errno is set to -1, which is a nonzero error value never used by MySQL. This can happen, for example, if you use placeholder characters in a query string but don't provide the correct number of data values when you bind them to the placeholders. In that case, the class detects the error without sending anything to the server.

$halt_on_error determines whether or not to terminate script execution when an error occurs. The default is to do so. Scripts that want to perform their own error-checking can set this to zero.

$query_pieces is used to hold a query string for prepared statements and parameter binding. (I'll explain later why this variable is an array.)

$result_id, $num_rows, and $row are used during result set processing to hold the result set identifier, the number of rows changed by or returned by the query, and the current row of the result set.

PHP Class Constructor Functions

In PHP, you can designate a constructor function in a class definition to be called automatically when new class instances are created. This is done by giving the function the same name as the class. You might do this, for example, if you need to initialize an object's variables to non-constant values. (In PHP 4, object variables can only take constant initializers.) The MySQL_Access class has no constructor because its variables all have constant initial values.

The "method definitions" line near the end of the class outline is where we'll put the functions that connect to the MySQL server, check for errors, issue queries, and so forth. We'll fill in that part shortly, but before doing so, let's get a sense of how the class can be used. We can put the code for the class in an include file, MySQL_Access.php, and install it in a directory that PHP searches when looking for include files (for example, /usr/local/apache/lib/php, as described in Recipe 2.4.) Then we can use the file by referencing it with an include statement, creating an instance of the class to get a connection object $conn, and setting up the connection parameters for that object:

include "MySQL_Access.php"; # include the MySQL_Access class $conn = new MySQL_Access; # create new class object $conn->host_name = "localhost"; # initialize connection parameters $conn->db_name = "cookbook"; $conn->user_name = "cbuser"; $conn->password = "cbpass";

However, using the class this way wouldn't really make it very convenient to connect to the server, due to the need to write all those assignment statements that set the connection parameters. Here's where a derived class that uses the base class comes in handy, because the derived class can be written to set the parameters automatically. To that end, let's create a class, Cookbook_DB_Access, that extends MySQL_Access by supplying parameters for connecting to the cookbook database. Then you can write scripts that prepare to access the cookbook database with just two lines of code:

include "Cookbook_DB_Access.php"; $conn = new Cookbook_DB_Access;

The implementation of Cookbook_DB_Access is quite simple. Create a file, Cookbook_DB_Access.php, that looks like this:

include_once "MySQL_Access.php"; class Cookbook_DB_Access extends MySQL_Access { # override default class variable values var $host_name = "localhost"; var $user_name = "cbuser"; var $password = "cbpass"; var $db_name = "cookbook"; }

The class line names the class, Cookbook_DB_Access, and the extends clause indicates that it's based on the MySQL_Access class. Extending a class this way is called subclassing the base class, or creating a derived class from the base class. The new class definition is almost trivial, containing only variable assignments for connection parameters. These override the (empty) values that are supplied by the base class. The effect is that when you create an instance of the Cookbook_DB_Access class, you get an object that's just like a MySQL_Access object, except that the connection parameters are set automatically for connecting to the cookbook database.

Now you can see more clearly why we left the connection parameters in the MySQL_Access class empty rather than setting them for accessing the cookbook database. By leaving them blank, we create a more generic class that can be extended for any number of databases by creating different derived classes. Cookbook_DB_Access is one such class. If you're writing a set of scripts that use a different database, derive another extended class that supplies appropriate connection parameters for that database. Then have the scripts use the second extended class rather than Cookbook_DB_Access.php.

Incidentally, the reason that Cookbook_DB_Access.php includes MySQL_Access.php is so that you don't need to. When your scripts include Cookbook_DB_Access.php, they get MySQL_Access.php "for free." The include_once statement is used rather than include to prevent duplicate-definition problems from occurring if your scripts happen to include MySQL_Access.php anyway.

2.10.5 Connecting and Disconnecting

Now we need to write the methods of the base class, MySQL_Access, that interact with MySQL. These go in the MySQL_Access.php source file. First, we need a connect( ) method that sets up a connection to the MySQL server:

The connect( ) method checks for an existing connection and attempts to establish one only if it hasn't already done so. connect( ) does this so other class methods that require a connection can call this method to make sure there is one. Specifically, we can write the query-issuing method to call connect( ) before sending a query. That way, all a script has to do is create a class object and start issuing queries; the class methods automatically take care of making the connection for us. By writing the class this way, it becomes unnecessary for scripts that use the class ever to establish a connection explicitly.

For a successful connection attempt, or if a connection is already in place, connect( ) returns the connection identifier (a non-FALSE value). If an error occurs, connect( ) calls error( ) and one of two things can happen:

If $halt_on_error is set, error( ) prints a message and terminates the script.

Otherwise, error( ) does nothing and returns to connect( ), which returns FALSE.

Note that if a connection failure occurs, connect( ) tries to use mysql_errno( ) and mysql_error( ) if they are the versions provided in PHP 4.0.6 and up that return usable information for mysql_connect( ) errors (see Recipe 2.3). Otherwise, it sets $errno to -1 and $errstr to $php_errormsg.

There is also a disconnect( ) method corresponding to connect( ) in case you want to disconnect explicitly. (Otherwise, PHP closes the connection for you when your script exits.) The method calls mysql_close( ) if a connection is open:

function disconnect ( ) { if ($this->conn_id != 0) # there's a connection open; close it { mysql_close ($this->conn_id); $this->conn_id = 0; } return (TRUE); }

2.10.6 Error Handling

MySQL_Access methods handle errors by calling the error( ) method. The behavior of this method depends on the value of the $halt_on_error variable. If $halt_on_error is true (nonzero), error( ) prints an error message and exits. This is the default behavior, which means you never need to check for errors if you don't want to. If you disable $halt_on_error by setting it to zero, error( ) simply returns to its caller, which then can pass back an error return status to its own caller. Thus, error-handling code within MySQL_Access typically looks like this:

if (some error occurred) { $this->error ("some error occurred"); return (FALSE); }

If $halt_on_error is enabled when an error occurs, error( ) is invoked and terminates the script. Otherwise, it returns and the return( ) statement that follows it is executed.

To write code that does its own error checking, disable $halt_on_error. In that case, you may also want to access the $errno and $errstr variables, which hold the MySQL numeric error code and descriptive text message. The following example shows how to disable $halt_on_error for the duration of a single operation:

$conn->halt_on_error = 0; print ("Test of error-trapping: "); if (!$conn->issue_query ($query_str)) print ("Hey, error $conn->errno occurred: $conn->errstr "); $conn->halt_on_error = 1;

When error( ) prints a message, it also displays the values of the error variables if $errno is nonzero. error( ) converts the message to properly escaped HTML, on the assumption that the class will be used in a web environment:

function error ($msg) { if (!$this->halt_on_error) # return silently return; $msg .= " "; if ($this->errno) # if an error code is known, include error info $msg .= sprintf ("Error: %s (%d) ", $this->errstr, $this->errno); die (nl2br (htmlspecialchars ($msg))); }

2.10.7 Issuing Queries and Processing the Results

Now we get to the heart of the matter, issuing queries. To execute a statement, pass it to issue_query( ):

function issue_query ($query_str) { if (!$this->connect ( )) # establish connection to server if return (FALSE); # necessary $this->num_rows = 0; $this->result_id = mysql_query ($query_str, $this->conn_id); $this->errno = mysql_errno ( ); $this->errstr = mysql_error ( ); if ($this->errno) { $this->error ("Cannot execute query: $query_str"); return (FALSE); } # get number of affected rows for non-SELECT; this also returns # number of rows for a SELECT $this->num_rows = mysql_affected_rows ($this->conn_id); return ($this->result_id); }

issue_query( ) first calls connect( ) to make sure that a connection has been established before it sends the query to the server. Then it executes the query, sets the error variables (which will be 0 and the empty string if no error occurs), and checks whether or not the query succeeded. If it failed, issue_query( ) takes the appropriate error-handling action. Otherwise, it sets $num_rows and the result set identifier becomes the return value. For a non-SELECT query, $num_rows indicates the number of rows changed by the query. For a SELECT query, it indicates the number of rows returned. (There's a little bit of cheating here. mysql_affected_rows( ) really is intended only for non-SELECT statements, but happens to return the number of rows in the result set for SELECT queries.)

If a query produces a result set, you'll want to fetch its rows. PHP provides several functions for this, which were discussed in Recipe 2.5: mysql_fetch_row( ), mysql_fetch_array( ), and mysql_fetch_object( ). These functions can be used as the basis for corresponding MySQL_Access methods fetch_row( ), fetch_array( ), and fetch_object( ). Each of these methods fetches the next row and returns it, or, if there are no more rows left, releases the result set and returns FALSE. They also set the error variables automatically on every call. The fetch_row( ) method is shown here; fetch_array( ) and fetch_object( ) are very similar:

# Fetch the next row as an array with numeric indexes function fetch_row ( ) { $this->row = mysql_fetch_row ($this->result_id); $this->errno = mysql_errno ( ); $this->errstr = mysql_error ( ); if ($this->errno) { $this->error ("fetch_row error"); return (FALSE); } if (is_array ($this->row)) return ($this->row); $this->free_result ( ); return (FALSE); }

The free_result( ) method used by the row-fetching methods releases the result set, if there is one:

function free_result ( ) { if ($this->result_id) mysql_free_result ($this->result_id); $this->result_id = 0; return (TRUE); }

Freeing the result set automatically when the last record has been fetched is one way the class interface simplifies MySQL access, compared to the PHP function-based interface. However, any script that fetches only part of a result set should invoke free_result( ) itself to release the set explicitly.

To determine whether or not a value from a result set represents a NULL value, compare it to the PHP NULL value by using the triple-equals operator:

if ($val === NULL) { # $val is a NULL value }

Alternatively, use isset( ):

if (!isset ($val)) { # $val is a NULL value }

At this point, enough machinery is present in the class interface that it is usable for writing scripts that issue queries and process the results:

# instantiate connection object include "Cookbook_DB_Access.php"; $conn = new Cookbook_DB_Access; # issue query that returns no result set $query = "UPDATE profile SET cats=cats+1 WHERE name = 'Fred'"; $conn->issue_query ($query); print ($conn->num_rows . " rows were updated "); # issue queries that fetch rows, using each row-fetching method $query = "SELECT id, name, cats FROM profile"; $conn->issue_query ($query); while ($row = $conn->fetch_row ( )) print ("id: $row[0], name: $row[1], cats: $row[2] "); $conn->issue_query ($query); while ($row = $conn->fetch_array ( )) { print ("id: $row[0], name: $row[1], cats: $row[2] "); print ("id: $row[id], name: $row[name], cats: $row[cats] "); } $conn->issue_query ($query); while ($row = $conn->fetch_object ( )) print ("id: $row->id, name: $row->name, cats: $row->cats ");

2.10.8 Quoting and Placeholder Support

In Recipe 2.9, we developed a PHP sql_quote( ) function for PHP to handle quoting, escaping, and NULL (unset) values, so that any value can be inserted easily into a query:

function sql_quote ($str) { if (!isset ($str)) return ("NULL"); $func = function_exists ("mysql_escape_string") ? "mysql_escape_string" : "addslashes"; return ("'" . $func ($str) . "'"); }

If we add sql_quote( ) to the MySQL_Access class, it becomes available automatically to any class instance as an object method and you can construct query strings that include properly quoted values like so:

$stmt = sprintf ("INSERT INTO profile (name,birth,color,foods,cats) VALUES(%s,%s,%s,%s,%s)", $conn->sql_quote ("De'Mont"), $conn->sql_quote ("1973-01-12"), $conn->sql_quote (NULL), $conn->sql_quote ("eggroll"), $conn->sql_quote (4)); $conn->issue_query ($stmt);

In fact, we can employ the sql_quote( ) method as the basis for a placeholder emulation mechanism, to be used as follows:

Begin by passing a query string to the prepare_query( ) method.

Indicate placeholders in the query string by using ? characters.

Execute the query and supply an array of values to be bound to the query, one value per placeholder. (To bind NULL to a placeholder, pass the PHP NULL value.)

One way to perform parameter binding is to do a lot of pattern matching and substitution in the query string wherever ? occurs as a placeholder character. An easier approach is simply to break the query string at the ? characters, then glue the pieces back together at query execution time with the properly quoted data values inserted between the pieces. Splitting the query also is an easy way to find out how many placeholders there are (it's the number of pieces, minus one). That's useful for determining whether or not the proper number of data values is present when it comes time to bind those values to the placeholders.

The prepare_query( ) method is quite simple. All it does is split up the query string at ? characters, placing the result into the $query_pieces array for later use at parameter-binding time:

function prepare_query ($query) { $this->query_pieces = explode ("?", $query); return (TRUE); }

We could invent new calls for binding data values to the query and for executing it, but it's also possible to modify issue_query( ) a little, to have it determine what to do by examining the type of its argument. If the argument is a string, it's interpreted as a query that should be executed directly (which is how issue_query( ) behaved before). If the argument is an array, it is assumed to contain data values to be bound to a previously prepared statement. With this change, issue_query( ) looks like this:

Now that quoting and placeholder support is in place, the class provides three ways of issuing queries. First, you can write out the entire query string literally and perform quoting, escaping, and NULL handling yourself:

$conn->issue_query ("INSERT INTO profile (name,birth,color,foods,cats) VALUES('De'Mont','1973-01-12',NULL,'eggroll','4')");

Second, you can use the sql_quote( ) method to insert data values into the query string:

Third, you can use placeholders and let the class interface handle all the work of binding values to the query:

$conn->prepare_query ("INSERT INTO profile (name,birth,color,foods,cats) VALUES(?,?,?,?,?)"); $conn->issue_query (array ("De'Mont", "1973-01-12", NULL, "eggroll", 4));

The MySQL_Access and Cookbook_DB_Access classes now provide a reasonably convenient means of writing PHP scripts that is easier to use than the native MySQL PHP calls. The class interface also includes placeholder support, something that PHP does not provide at all.

The development of these classes illustrates how you can write your own interface that hides MySQL-specific details. The interface is not without its shortcomings, naturally. For example, it allows you to prepare only one statement at a time, unlike DBI and JDBC, which support multiple simultaneous prepared statements. Should you require such functionality, you might consider how to reimplement MySQL_Access to provide it.

PHP Class Constructor Functions

Категории