Professional PHP5 (Programmer to Programmer Series)
ISBN: N/A
EAN: N/A
EAN: N/A
Year: 2003
Pages: 182
Pages: 182
For a far more sophisticated look at what's possible with database abstraction, have a look at the PEAR DB class that ships with PHP. This database abstraction layer is capable of simultaneously handling connections to more than a dozen databases types natively, and many more through its ODBC capabilities.
It uses the same syntax for all connection strings, unlike the native PHP database functions. To establish a connection using the DB class, the syntax is
[database type]://[user]:[password]@[hostname/[database name]
This string is called the data source name (DSN). Using this common DSN syntax, you can connect to any kind of database supported by DB.
// connect to a MySQL server on db01 using the root account // with the password passw0rd and connect to the database 'dbfoo' $dsn = 'mysql://root:passw0rd@db01/dbfoo'; // connect to a PostgreSQL server on the local machine using the // user postgres, no password, and connect to the database 'mydb' $dsn = 'pgsql://postgres@localhost/mydb';
The currently supported database backends and database type strings are as follows:
| PEAR DB Type | Database |
|---|---|
| dbase | dBase |
| fbsql | FrontBase |
| ibase | InterBase |
| ifx | Informix |
| msql | Mini SQL |
| mssql | Microsoft SQL Server |
| mysql | MySQL (for servers running MySQL <= 4.0 |
| mysqli | MySQL (for servers running MySQL >= 4.1 |
| oci8 | Oracle 7/8/9 |
| odbc | Open Database Connectivity |
| pgsql | PostgreSQL |
| sqlite | SQLite |
| sybase | Sybase |
Note that support for the relevant database must either be compiled into your build of PHP or loaded as a dynamic module for connections to any of the above databases to work. As with the previous example, the DB classes provide a wrapper around the native PHP functions. If those functions aren't available, you can't connect to a database of that type.
To establish a connection to a database using DB, you need to include the main DB class file and create a valid DSN. In the following example, DB_connect.php, a connection is created to a PostgreSQL database, we select some data from a table, and we print that data to the screen with var_dump.
<?php require_once('DB.php'); $dsn = 'pgsql://postgres@localhost/mydb'; $conn = DB::connect($dsn); if(DB::isError($conn)) { //you would probably want to do something a bit more graceful here print("Unable to connect to the database using the DSN $dsn"); die($conn->getMessage()); } //get all results as an associative array $conn->setFetchMode(DB_FETCHMODE_ASSOC); $sql = "SELECT * FROM mytable"; $data =&$conn->getAll($sql); //returns all rows. Only use with small recordsets // Always check that $data is not an error if (DB::isError($data)) { print("Error trying to run the query $sql"); die ($data->getMessage()); } var_dump($data); $conn->disconnect(); //close the connection ?>
Notice that all DB functions could return an object of class DB_Error. To check for this, use DB::isError() on all return values. In your application, you'll want to do something a bit more graceful than simply calling die(). You should also never print sensitive information such as the DSN or details of the query to the screen. It is far better to log this sort of information to an application log file. In Chapter 11 you'll look at creating a robust logging mechanism.
Also note that all the methods of the DB class are static. The DB class itself is a wrapper around lower- level classes that do all the real heavy lifting. It provides only some utility methods, such as connect(), disconnect(), isError() and a few others. None of these methods is dependent on any object state, and, as a result, each is declared static.
The DB package provides you with a few different methods for retrieving information from the database, all of which are useful at different times. You've already seen one of them in the DB_connect.php code file. The DB::connect() method returns a subclass of DB_common. The name of that subclass is determined by the first part of the DSN. In this example, you get a DB_pgsql object.The DB_common class exposes a method called getAll(), which returns all the records for the given query in the form specified to the fetchMode() method. The previous example specifies DB_FETCHMODE_ASSOC as the default. This means that each of the data-fetching methods will, by default, return the results as an associative array in which the keys of the array are the same as the field names in the database.
Other easy-to-use methods include getOne(), which returns the first field in the first row of a recordset. This method is especially useful when retrieving single values, such as ID number, from a database. The getRow() method returns the first row. If you're looking for an array of the values from a single column, use getCol(). The following sample code, which requires the same mydb database and mytable table you used in the previous section, shows the use of all these methods and the structure of the values they return.
$sql = "SELECT id, myval FROM mytable"; $arData = $conn->getAll($sql); /* * $arData = array ( * 0 => array('id' => 1, 'myval' => 'foo'), * 0 => array('id' => 2, 'myval' => 'bar'), * 0 => array('id' => 3, 'myval' => 'baz'), * ) */ $id = $conn->getOne($sql); /* *$id=1; */ $arData = $conn->getRow($sql); /* * $arData = array('id' => 1, 'myval' => 'foo'); */ $arData = $conn->getCol($sql); /* * $arData = array(1, 2, 3); */
All the get*() methods shown previously are utility wrappers around fetch*() methods. For most situations, you'll want to use fetchRow(). Unlike getAll(), which reads the entire recordset into memory, fetchRow() grabs only one row at a time. This allows you to iterate through large datasets without having to take up large quantities of memory, as the database is interrogated only one row at a time, as when you request another row from within your code.
The fetch*() methods require a DB_result object as an argument. This argument is obtained through the use of the query() method. To see how these functions work together, have a look at the sample following code.
// get all results as an associative array $conn->setFetchMode(DB_FETCHMODE_ASSOC); $sql = "SELECT * FROM mytable"; $result =& $conn->query($sql); // Always check that $result is not an error if (DB::isError($result)) { print("Error trying to run the query $sql"); die ($result->getMessage()); } // call the fetchRow() method to get each row, one at // a time from the resultset. while($row = $result->fetchRow()) { print $row['myval'] . "<br>\n"; } If you've used the native PHP database functions in the past, you'll recognize this syntax as similar to the *_fetch_row() function used by your favorite database extension. Each call to fetchRow() increments an internal counter that keeps track of which row should be retrieved next. It returns null at the end of the recordset.
The DB package provides a number of other functions that will be of use to you. This is by no means an exhaustive list, just some highlights.
The same query() method that performed SELECT statements will allow you to issue UPDATE and DELETE statements, as well.
$sql = "INSERT INTO mytable (myvalue) VALUES('Foobar')"; $result = $conn->query($sql); if(DB::isError($result)) { print("Unable to perform insert statement."); die($result->getMessage()); } $sql = "UPDATE mytable SET myvalue = 'foobar' WHERE id = 4"; $result = $conn->query($sql); if(DB::isError($result)) { print("Unable to perform update statement."); die($result->getMessage()); }
As with all methods of the DB_common class, the return value of all methods should be checked with DB::isError().
When you're performing an INSERT, finding a platform-independent way to retrieve the value of an auto-incrementing field can be difficult. Many RDBMSes support the concept of a sequence. A sequence is simply a persistent counter that starts at some predefined number (usually 1) and allows you to increment and fetch the new value. PostgreSQL, Oracle, and other RDBMSes that don't have a native auto-increment field use these structures to implement auto-increment field capabilities. MySQL does not have any such concept as a sequence (it supports auto-increment fields natively), but for portability the DB package emulates this capability by using a table with only one AUTOINCREMENT field, called id.
Here's how this is useful. Assuming that you're using PostgreSQL here, you might well create a table using the following shorthand SQL statements:
CREATE TABLE info ( id SERIAL PRIMARY KEY NOT NULL, data varchar(255) );
In fact, behind the scenes, PostgreSQL is actually creating a table using the following syntax:
CREATE SEQUENCE info_id_seq; CREATE TABLE info ( id integer NOT NULL PRIMARY KEY DEFAULT nextval('info_id_seq'), data varchar(255) );
As you can see, PostgreSQL has simulated the implementation of an auto-incrementing primary key by creating a sequence object called info_id_seq. This naming convention is used to indicate that this relation is a sequence object used to drive the column named id of the table called info. The info table uses the nextval() function to increment the sequence's value by one and return the new value. By using nextval('info_id_seq') as the default value of the id field, you can issue statements like this:
INSERT INTO info(data) VALUES('blah blah blah'); However, there is no way for you to reliably get the id value for this newly created record because there might be more than one record with 'blah blah blah' as the value for the data field. Oracle uses a pseudo field called ROWID, PostgreSQL has OID, and other RDBMSes use other means for retrieving information about the newly created row. To get around this difference in behavior, you can use the DB package's nextID() method to fetch the next value of a given sequence object and use it explicitly in your INSERT statements.
$newID = $conn->nextID('info_id', true); //uses table name, assumes $name_seq //The second parameter creates the //sequence info_id_seq if it doesn't //already exist. if(DB::isError($newID)) { print("Failed getting the new ID!"); die($newID->getMessage()); } $sql = "INSERT INTO info(id, data) VALUES($newID, 'blah blah blah')"; $result = $conn->query($sql); if(DB::isError($sql)) { print("Insert failed for $sql"); die($result->getMessage()); } else { print("Created record with id $newID"); }
The nextID() method returns the value of the next number in the sequence whose name is the value of the first parameter plus "_seq". The second parameter allows the sequence object to be created if it doesn't already exist in the database. This can be a useful option; however, we strongly recommend that you create the sequence objects outside your code (or, ideally, allow your RDBMS to do it for you) so that you have control over the creation options.
In the original simple abstraction layer from the beginning of the chapter, you used the pg_quote_string() function when using literal values in the insert(), update(), and delete() methods. This function automatically handles escaping characters such as ' in string values in the query. The DB package provides a similar method, called quoteSmart(), that is portable across RDBMS back ends. It takes a literal value as its sole parameter and spits out a string properly escaped based on the rules of the database to which you are connected.
function searchByName($name) { global $conn; //assume the connection was established somewhere else $sql = "SELECT * FROM users WHERE name=". $conn->quoteSmart($name); $result = $conn->query($sql); //make sure you use DB::isError to check for problems! return $result; }
For simplicity's sake, we didn't repeat the error handling code you've already seen several times. In this example, assuming that $name = "O'Malley", $conn->quoteSmart($name) will return "'O\'Malley'" for PostgreSQL (note the single quotation marks around the name, and the escaped apostrophe). The way in which this function escapes that apostrophe may be different depending on the backend to which you are connected. For example, Microsoft SQL Server uses two quotes in a row (")as part of its TransactSQL syntax.
You've looked at only a few of the methods provided by the classes of the DB package. The full documentation, including examples, can be found at http://pear.php.net/manual/en/package.database.php.