<<

. 63
( 132 .)



>>

, ˜password™ => ˜ydobon™
, ˜protocol™ => ˜tcp™
, ˜hostspec™ => ˜localhost™
Chapter 11: Content-Management System 365

, ˜port™ => NULL
, ˜socket™ => NULL
, ˜database™ => ˜tracking™
)
);
// remove NULL values to not override entries from dsn
$p = array_diff($p, array_filter($p,™is_null™));
if (isset($dsnlist[$p[˜application™]]))
{
$dsn = array_merge($dsnlist[$p[˜application™]],$p);
}
else
{
$dsn = array_merge($dsnlist[˜default™],$p);
}
return $dsn;
}

Typically, this function is called with just the application name as a parameter,
and will return the entry for that application from the static array of connection
parameters. But we can pass in other values as well, which are merged into the
returned array.

Functions from /book/functions/database
The functions of the PEAR DB library are powerful enough that in most circum-
stances you can use them either directly in the code of the Web pages or in functions
specific to an example. In a few instances you do the same work in all the examples,
though, and these general functions are stored in the /databases directory of the
general /functions directory.

db_connect()
The db_connect() function is similar to the mysql_connect() function we used in
previous examples. It creates a persistent connection to the MySQL server, getting
connection parameters from the db_dsnlist() function described earlier.

function db_connect()
{
static $_connections = array();
static $_defaults = array(
˜application™ => NULL
, ˜database™ => NULL
, ˜username™ => NULL
, ˜db_error_level™ => E_USER_ERROR
, ˜db_error_handler™ => ˜db_error_handler™
366 Part IV: Not So Simple Applications

, ˜options™ => array(
˜debug™ => 4
, ˜persistent™ => TRUE
, ˜autofree™ => TRUE
)
);
static $_simple = array(˜application™,™username™,™password™);

$dc = count($_connections);
$p = func_get_args();
if (empty($p))
{
if ($dc)
{
$dbh = array_pop(array_values($_connections));
if ($dbh === NULL) { user_error(˜Last connection is
NULL.™, E_USER_ERROR); exit; }
return $dbh;
}
user_error(˜No existing database connection found.™,
E_USER_ERROR);
exit;
}

$p = parse_arguments($p, $_simple, $_defaults);

if (empty($p[˜application™]))
{
$p[˜application™] = $p[˜database™];
if (!empty($p[˜username™]))
{
$p[˜application™] .= ˜:™.$p[˜username™];
}
}

$dbh = array_key_value($_connections,$p[˜application™],NULL);
if ($dbh !== NULL)
{
return $dbh;
}

$dsn = db_dsnlist($p);
$dbh = DB::connect($dsn, $p[˜options™]);
if (DB::isError($dbh))
{
$private_error = ˜dsn:™.var_export($dsn,TRUE).”\n”
Chapter 11: Content-Management System 367

.™ error:™.var_export($dbh,TRUE).”\n”
;
user_error(
˜Could not connect to database: ˜.$dbh->getMessage()
, $p[˜db_error_level™]
);
return FALSE;
}
if (is_string($p[˜db_error_handler™])
&& function_exists($p[˜db_error_handler™])
)
{
// it™s a function name - OK
}
elseif (is_array($p[˜db_error_handler™])
&& count($p[˜db_error_handler™]) == 2
&& method_exists($p[˜db_error_handler™][0],
$p[˜db_error_handler™][1])
)
{
// it™s an object method - OK
}
else
{
$p[˜db_error_handler™] = NULL;
}
if (!empty($p[˜db_error_handler™]))
{
$dbh->setErrorHandling(PEAR_ERROR_CALLBACK,
$p[˜db_error_handler™]);
}
else
{
$dbh-
>setErrorHandling(PEAR_ERROR_TRIGGER,$p[˜db_error_level™]);
}
$_connections[$p[˜application™]] = $dbh;
if ($dbh === NULL)
{
$private_error = var_export($_connection, TRUE);
user_error(˜connection is NULL.™, $p[˜db_error_level™]);
exit;
}
return $dbh;
}
368 Part IV: Not So Simple Applications

If db_connect() is called with no parameters, it hands back the handle of the
last DB object that was created. You™ll notice the use of this function throughout
this example and the examples that follow; we can call db_connect() from any
point in the application ” in a Web page, inside a function, and so on ” and get
access to the database, without having to set up a global variable, and without
making multiple connections. The more advanced object-oriented features of PHP
4.3 even let us do away with storing the object handle in a variable, and just use
the function in its place. Prior to PHP 4.3 we would have to do something like this:

$dbh = db_connect();
$dbh->query(˜delete * from mysql.users™);

But the new PHP object handling lets us just write

db_connect()->query(˜delete * from mysql.users™);

The db_connect() function also sets up how DB errors are handled. They can
either be passed on directly to a function or class method, or processed when they
trigger a PHP error of a given error level and thus go through whatever error
handling we™ve set up for general PHP errors. For the examples in this book, we
normally use the former method, passing DB errors on to a function of our own,
db_error_handler().

db_error_handler()
We use a special error-handling function for DB errors rather than only relying on
our regular error_handler() function. We do this so that we can roll back any
open transaction (if we still have an active database connection) and then trigger a
fatal error that will exit the page and stop any other queries from running. This is
key to the concept of atomic transactions, which are multi-stage procedures in
which, by rule, either all of the steps must occur, or none of them. This prevents
such problems as, in the case of a bank, money being credited to one account with-
out being subtracted from another one.

function db_error_handler($db_error)
{
$timestamp = time();
// this should be unnecessary but can™t hurt
$dbh = db_connect();
if (is_a($dbh,™DB™))
{
$last_query = $dbh->last_query;
$dbh->query(˜rollback™);
}
$skip_past_function = ˜mysqlraiseerror™;
$private_error = “DB error ($timestamp): “.$db_error->userinfo;
Chapter 11: Content-Management System 369

$error_level = E_USER_ERROR;
user_error(
“Database error - please contact the system
administrator.($timestamp)”
,$error_level
);
}

db_fetch_record()
This function provides a convenient way to get a record or set of records from a
table. It makes use of DB™s system for token replacement, which is a fancy way of
saying “placeholders.” As a simple example, you can run a query with DB like this:

$result = $dbh->query(˜select * from mytable where mykey = 1™);

But you can also pass in two arguments to DB::query(), the query string itself,
and an array of values to replace into the string:

$result = $dbh->query(
˜select * from mytable where mykey = ?™
, array($mykey)
);

The token character ? in the query string tells DB that it should replace it with
the content of a value from the array of arguments. If you have two ? characters in
your query string, it looks for two values in the array, and so on. The very nice
aspect of this ” beyond freeing you from having to build a new query string for
every new set of values you want to include in your query, which is no small pota-
toes ” is that DB takes care of quoting and escaping characters for you. A statement
like this:

$mykey = 1;
$myname = “O™Reilly”;
$result = $dbh->query(
˜select * from mytable where mykey = ? and myname = ?™
, array($mykey, $myname)
);

results in this query being run by MySQL:

select * from mytable where mykey = 1 and myname = ˜O\™Reilly™

and although this book is about PHP and MySQL, it™s worth noting here that DB
can be used with a wide variety of databases, handling the proper quotation and
escape syntax for each one. If you™ve ever had to port code from, say, Sybase or
PostgreSQL to MySQL, you can appreciate how valuable a feature that is.
370 Part IV: Not So Simple Applications

You can also make substitutions for literal parts of the query, using the ! token
character, like this:

$mykey = 1;
$myname = “O™Reilly”;
$result = $dbh->query(
˜select * from mytable where mykey = ? and ! = ?™
, array($mykey, ˜myname™, $myname)
);

DB interprets the ! character to indicate that it should put the corresponding
value from the argument list as-is, without quoting it, so that you can change the
name of the table of the column you query dynamically. You might be thinking,
looking at this example, what is the point of putting the literal string ˜myname™ in
the argument list, when you could have just written it into the query in the first
place? It™s only to show that you are not limited to using variables in your argu-
ment array.
DB even grabs the contents of an entire file for you, using the & token character,
like this:

$dbh->query(
˜insert into myfiles (filename, filecontents) values (?, &)™
, array(˜my file™, ˜myfile.txt™)
);

So now that we™ve seen a little more of what DB can do for us, look at an exam-
ple of using it, in the db_fetch_record() function:

function db_fetch_record()

<<

. 63
( 132 .)



>>