Database Abstraction with MDB2 from PEAR - Basic Usage
(Page 3 of 4 )
Now that we've got the packages installed we can start writing some code!
First things first. We need to establish a database connection. Connections are created with a Data Source Name, or DSN. This is a simple URL-like string that defines your connection. If you've used DBI with Perl or ODBC, this will look pretty familiar. Many of the pieces are optional, or will use defaults if not specified.
The basic format:
[DRIVER]://[USER]:[PASSWORD]@[PROTOCOL]+[HOST]/[DATABASE]?[OPTIONS]
Standard MySQL DSN:
mysql://user:password@localhost/mydata
Postgres on an unusual port
pgsql://user:pass@tcp(localhost:4179)/database
SQLite, specifying the unix file permissions for the database file:
sqlite:///path/to/database.db?mode=0666
Once you've created your DSN, you'll connect to the database and instantiate an MDB2 object. This will act as your database handle, and be used for all future interactions with the database in your code. Three methods are provided for creating this connection:
MDB2::factory() - Creates a new MDB2 object and returns it. Until you actually make queries, the actual connection is not established. Makes efficient use of your database resources.
MDB2::connect() - Creates a new MDB2 object, and establishes a connection.
MDB2::singleton() - Returns an MDB2 connection for the provided DSN. If the same DSN is requested again, the connection will be reused and returned. This is preferable to creating a global database variable.
$dsn = "mysql://user:password@localhost/mydata";
$db = MDB2::factory($dsn);
Operations with MDB2 can create an error. These are dealt with in the PEAR manner of returning a PEAR_Error class, and should be checked with the PEAR::isError() method. The getMessage() method on the error will provide a string detailing the particular error that occurred.
$db = MDB2::factory($dsn);
if (PEAR::isError($db)) {
// Uh oh!
die("There was a connecting to the database: " .
$db->getMessage());
}
Now that you have your connection, and have verified that the connection was successful, you'll want to execute some queries. Simple queries can be run with the query() method on your connection. Even if you don't expect a result set, you should still capture the return value and check for errors:
$result = $db->query("DELETE FROM users WHERE username='cdmoyer'");
if (PEAR::isError($result)) {
printf("There was an error deleting cdmoyer: %s",
$result->getMessage());
}
For SELECT statements, the result will allow you to both check for errors and retrieve the generated rows. The result includes a fetchRow() method which will return rows until the last row has been retrieved, and then will return false. fetchAll() is also provided, which will return an array of every row in the dataset. Other more specialized methods are provided for fetching individual columns from the data set.
$db->setLimit(10);
$result = $db->query("SELECT * FROM news WHERE category='3'");
if (PEAR::isError($result)) {
print "Sorry, there is no news at this time.";
}
else {
while ($news = $result->fetchRow(MDB2_FETCHMODE_ASSOC)) {
printf('<li>%s: %s</li>', $news['author'], $news['title']);
}
}
In this example, we see two important things, limits and the "fetch mode." setLimit() on the MDB2 connection provides an abstract way to limit the size of your dataset. In MySQL you'd simply write "LIMIT 10", but that syntax does not work with all databases. Using the abstract method ensures your code will work in all the environments where MDB2 will work. The "fetch mode" is passed to fetchRow() and determines the type of array returned.
MDB2_FETCHMODE_ASSOC returns an associative array with the column names as keys, whereas MDB2_FETCHMODE_ORDERED returns an ordered array with the columns in the order returned from the database. This mode is often slightly more efficient, but requires care in specifying the rows you wish to retrieve, lest you later change the database structure and wonder why your code is suddenly broken.
Finally, if you finish with the database you should disconnect. Normally PHP will clean up any open handles when the page finishes, but it is good practice to clean up.
$db->disconnect();
Next: Prepared Statements >>
More PEAR Articles Articles
More By Chris Moyer
/ 6 
