2.10 Writing an Object-Oriented MySQL Interface for PHP
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 independent�certainly 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\n");
if (!mysql_select_db ("cookbook", $conn_id))
die ("Cannot select database\n");
$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\n");
$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]\n");
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\n");
$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]\n");
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\n");
$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]\n");
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.
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:
function connect ( )
{
$this->errno = 0; # clear the error variables
$this->errstr = "";
if ($this->conn_id == 0) # connect if not already connected
{
$this->conn_id = @mysql_connect ($this->host_name,
$this->user_name,
$this->password);
# use mysql_errno( )/mysql_error( ) if they work for
# connection errors; use $php_errormsg otherwise
if (!$this->conn_id)
{
# mysql_errno( ) returns nonzero if it's
# functional for connection errors
if (mysql_errno ( ))
{
$this->errno = mysql_errno ( );
$this->errstr = mysql_error ( );
}
else
{
$this->errno = -1; # use alternate values
$this->errstr = $php_errormsg;
}
$this->error ("Cannot connect to server");
return (FALSE);
}
# select database if one has been specified
if (isset ($this->db_name) && $this->db_name != "")
{
if (!@mysql_select_db ($this->db_name, $this->conn_id))
{
$this->errno = mysql_errno ( );
$this->errstr = mysql_error ( );
$this->error ("Cannot select database");
return (FALSE);
}
}
}
return ($this->conn_id);
}
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:\n");
if (!$conn->issue_query ($query_str))
print ("Hey, error $conn->errno occurred: $conn->errstr\n");
$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 .= "\n";
if ($this->errno) # if an error code is known, include error info
$msg .= sprintf ("Error: %s (%d)\n", $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\n");
# 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]\n");
$conn->issue_query ($query);
while ($row = $conn->fetch_array ( ))
{
print ("id: $row[0], name: $row[1], cats: $row[2]\n");
print ("id: $row[id], name: $row[name], cats: $row[cats]\n");
}
$conn->issue_query ($query);
while ($row = $conn->fetch_object ( ))
print ("id: $row->id, name: $row->name, cats: $row->cats\n");
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:
function issue_query ($arg = "")
{
if ($arg == "") # if no argument, assume prepared statement
$arg = array ( ); # with no values to be bound
if (!$this->connect ( )) # establish connection to server if
return (FALSE); # necessary
if (is_string ($arg)) # $arg is a simple query
$query_str = $arg;
else if (is_array ($arg)) # $arg contains data values for placeholders
{
if (count ($arg) != count ($this->query_pieces) - 1)
{
$this->errno = -1;
$this->errstr = "data value/placeholder count mismatch";
$this->error ("Cannot execute query");
return (FALSE);
}
# insert data values into query at placeholder
# positions, quoting values as we go
$query_str = $this->query_pieces[0];
for ($i = 0; $i < count ($arg); $i++)
{
$query_str .= $this->sql_quote ($arg[$i])
. $this->query_pieces[$i+1];
}
}
else # $arg is garbage
{
$this->errno = -1;
$this->errstr = "unknown argument type to issue_query";
$this->error ("Cannot execute query");
return (FALSE);
}
$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);
}
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:
$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);
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.
| 
No comments:
Post a Comment