CHAPTER 6 – PEAR DB – Graceful Error Handling

DB uses the PEAR errors to report errors. Here is an example that alerts the user if he tries to add a unique combination of keys twice: <?php require_once 'DB.php'; $dbh = DB::connect("mysql://test@localhost/world"); $dbh->setOption('portability', DB_PORTABILITY_ERRORS); $dbh->query("CREATE TABLE mypets (name CHAR(15), species CHAR(15))"); $dbh->query("CREATE UNIQUE INDEX mypets_idx ON mypets (name, species)"); $data = array('Bill', 'Mule'); for ($i = 0; $i < 2; $i++) { $result = $dbh->query("INSERT INTO mypets VALUES(?, ?)", $data); if (DB::isError($result) && $result->getCode() == DB_ERROR_CONSTRAINT) { print "Already have a $data[1] called $data[0]!<br />n"; } } $dbh->query("DROP TABLE mypets"); See Chapter 7, "Error Handling," for details on how to catch PEAR errors. 6.4.10 Convenience Methods Although PEAR DB is mostly a common API, it also contains some convenience features for retrieving all the data from a query easily. All these methods sup- port prepare/execute style queries, and all of them return PEAR errors on fail- ure. 6.4.10.1 $dbh->getOne($query, $params = array()) T h e g e t O n e ( ) method returns the first column from the first row of data. Use the $params parameter if $query contains placeholders (this applies to the rest of the conve- nience functions, too). Here's an example: $name = $dbh->getOne('SELECT name FROM users WHERE id = ?', array($_GET['userid'])); 6.4.10.2 $dbh->getRow($query, $params = array(), $fetchmode = DB_FETCHMODE_DEFAULT) The getRow() method returns an array with the first row of data. It will use the default fetch mode, defaulting to ordered. Ordered data will start at index 0. Here's an example: $data = $dbh->getRow('SELECT * FROM users WHERE id = ?', array($_GET['userid'])); 6.4.10.3 $dbh->getCol($query, $col = 0, $params = array()) The getCol() method returns an array with the $col'th element of each row. $col defaults to 0. Here's an example: $userids = $dbh->getCol('SELECT id FROM users'); 6.4.10.4 $dbh->getAssoc($query, $force_array = false, $params = array(), $fetchmode = DB_FETCHMODE_DEFAULT, $group = false) This method returns an associative array with the contents of the first column as key and the remaining column as value, like this (one line per row): array(col1row1 => col2row1, col1row2 => col2row2, ...) If the query returns more than two columns, the value will be an array of these values, indexed according to $fetchmode, like this: array(col1row1 => array(col2row1, col3row1...), col1row2 => array(col2row2, col3row2...), ...) or with DB_FETCHMODE_ASSOC: array(field1 => array(name1 => field2, name3 => field3...), field2 => array(name2 => field2, name3 => field3...), ...) The $force_array parameter makes the value an array even if the query returns only two columns. If the first column contains the same key more than once, a later occur- rence will overwrite the first. Finally, you set the $group parameter to TRUE, and getAssoc() will keep all the rows with the same key in another level of arrays: $data = $dbh->getAssoc("SELECT firstname, lastname FROM ppl", false, null, DB_FETCHMODE_ORDERED, true); This example would return something like this: array("Bob" => array("Jones", "the Builder", "Hope"), "John" => array("Doe", "Kerry", "Lennon"), ...) 6.4.10.5 $dbh->getAll($query, $params = array(), $fetchmode = DB_FETCHMODE_DEFAULT) This method returns all the data from all the rows as an array of arrays. The inner arrays are indexed according to $fetchmode: array(array(name1 => col1row1, name2 => col2row2...), array(name1 => col1row2, name2 => col2row2...), ...) Yo u c a n fl i p a r o u n d t h e d i m e n s i o n s i n t h i s a r r ay b y O R ' i n g DB_FETCHMODE_FLIPPED into fetch mode. With a fetch mode of DB_FETCHMODE_FLIPPED | DB_FETCHMODE_ASSOC, the result will look like this: array(name1 => array(col1row1, col1row2, ...), name2 => array(col2row1, col2row2, ...), ...)

6.5 SUMMARY This chapter introduced two new database extensions in PHP 5: mysqli and sqlite. It also presents PEAR DB, which is the most popular database abstrac- tion layer for PHP. In this chapter, you learned: Some of the strengths and weaknesses of mysql versus sqlite When it makes sense to use a database abstraction layer How to connect to databases using mysqli, sqlite, or DB Executing queries and fetching results with mysqli, sqlite, or DB Executing prepared queries with mysqli and DB The difference between buffered and unbuffered queries Various ways of fetching data from queries Database error handling Using triggers and user-defined functions with sqlite How to create portable database code with DB