Getting Started with MDB2

Let's discuss the necessary steps to install MDB2, to create an MDB2 object, and then set up some options to set the data fetch mode and finally disconnect from the database.

Installing MDB2

When installing MDB2, keep in mind that the MDB2 package does not include any database drivers, so these will need to be installed separately. MDB2 is stable, but as explained earlier, since the packages have different release cycles, the status of the package you plan to use may be beta, alpha, or still in development. This will need to be taken into consideration when installing a driver package.

The easiest way to install MDB2 is by using the PEAR installer:

> pear install MDB2

This command will install the core MDB2 classes, but none of the database drivers. To install the driver for the database you'll be using, type:

> pear install MDB2_Driver_mysql

This will install the driver for MySQL. If you wish to install the driver for SQLite, type:

> pear install MDB2_Driver_sqlite

The full list of currently available drivers is as follows:

• fbsql: FrontBase
• ibase: InterBase
• mssql: MS SQL Server
• mysql: MySQL
• mysqli: MySQL using the mysqli PHP extension; for more details, visit http://php.net/mysqli
• oci8: Oracle
• pgsql: PostgreSQL
• querysim: Querysim
• sqlite: SQLite

Connecting to the Database

To connect to your database after a successful installation, you need to set up the DSN (Data Source Name) first. The DSN can be a string or an array and it defines the parameters for your connection, such as the name of the database, the type of the RDBMS, the username and password to access the database, and so on.

DSN Array

If the DSN is defined as an array, it will look something like this:

$dsn = array ( 'phptype' => 'mysql',
'hostspec' => 'localhost:3306',
'username' => 'user',
'password' => 'pass',
'database' => 'mdb2test'
);

Here's a list of keys available to use in the DSN array:

• phptype: The name of the driver to be used, in other words, it defines the type of the RDBMS
• hostspec: (host specification) can look like hostname:port or it can be only the hostname while the port can be defined separately in a port array key
• database: The name of the actual database to connect to
• dbsyntax: If different than the phptype
• protocol: The protocol, for example TCP
• socket: Mentioned if connecting via a socket
• mode: Used for defining the mode when opening the database file

DSN String

A quicker and friendlier way (once you get used to it) to define the DSN is to use a string that looks similar to a URL. The basic syntax is:

phptype://username:password@hostspec/database

The example above becomes:

$dsn = 'mysql://user:pass@localhost:3306/mdb2test';

More details on the DSN and more DSN string examples are available in the PEAR manual at http://pear.php.net/manual/en/package.database.mdb2.intro-dsn.php.

Instantiating an MDB2 object

There are three methods to create an MDB2 object:

$mdb2 =& MDB2::connect($dsn);
$mdb2 =& MDB2::factory($dsn);
$mdb2 =& MDB2::singleton($dsn);

connect() will create an object and will connect to the database. factory() will create an object, but will not establish a connection until it's needed. singleton() is like factory() but it makes sure that only one MDB2 object exists with the same DSN. If the requested object exists, it's returned; otherwise a new one is created.

One scenario exists where you can "break" the singleton functionality by using setDatabase() to set the current database to a database different from the one specified in the DSN.

$dsn = 'mysql://root@localhost/mdb2test';
$mdb2_first =& MDB2::singleton($dsn);
$mdb2_first->setDatabase('another_db');
$mdb2_second =& MDB2::singleton($dsn);

In this case you'll have two different MDB2 instances.

All three methods will create an object of the database driver class. For example, when using the MySQL driver, the variable $mdb2 defined above will be an instance of the MDB2_Driver_mysql class.

Options

MDB2 accepts quite a few options that can be set with the call to connect(), factory(), or singleton(), or they can be set later using the setOption() method (to set one option a time) or the setOptions() method (to set several options at once). For example:

$options = array ( 'persistent' => true,
'ssl' => true,
);
$mdb2 =& $MDB2::factory($dsn, $options);

or

$mdb2->setOption('portability', MDB2_PORTABILITY_NONE);

The full list of available options can be found in the package's API docs at: http://pear.php.net/package/MDB2/docs/. Let's take a look at two important ones right away.

Option "persistent"

This Boolean option defines whether or not a persistent connection should be established.

Note. There is an informative article on mysql.com about the pros and cons of using persistent connections in MySQL; it's located at http://www.mysql.com/ news-and-events/newsletter/2002-11/a0000000086.html.

The default value is false. If you want to override the default, you can set it when the object is created:

$options = array ( 'persistent' => true );
$mdb2 =& MDB2::factory($dsn, $options);

Using setOption() you can define options after the object has been created:

$mdb2->setOption('persistent', true);

Option "portability"

MDB2 tries to address some inconsistencies in the way different DBMS implement certain features. You can define to which extent the database layer should worry about the portability of your scripts by setting the portability option.

The different portability options are defined as constants prefixed with MDB2_PORTABILITY_* and the default value is MDB2_PORTABILITY_ALL, meaning "do everything possible to ensure portability". The full list of portability constants and their meaning can be found at http://pear.php.net/manual/en/package.database.mdb2.intro-portability.php.

You can include several portability options or include all with some exceptions by using bitwise operations, exactly as you would do when setting error reporting in PHP. The following example will set the portability to all but lowercasing:

MDB2_PORTABILITY_ALL ^ MDB2_PORTABILITY_LOWERCASE

If you don't want use the full portability features of MDB2 but only trim white space in results and convert empty values to null strings:

MDB2_PORTABILITY_RTRIM | MDB2_PORTABILITY_EMPTY_TO_NULL

Probably the best thing to do is to leave the default MDB2_PORTABILITY_ALL; this way if you run into some problems with your application, you can double-check the database access part to ensure that the application is as portable as possible.

Setting Fetch Mode

One more setting you'd probably want to define upfront is the fetch mode, or the way results will be returned to you. You can have them as an enumerated list (default option), associative arrays, or objects. Here are examples of setting the fetch mode:

$mdb2->setFetchMode(MDB2_FETCHMODE_ORDERED);
$mdb2->setFetchMode(MDB2_FETCHMODE_ASSOC);
$mdb2->setFetchMode(MDB2_FETCHMODE_OBJECT);

Probably the friendliest and the most common fetch mode is the associative array, because it gives you the results as arrays where the keys are the names of the table columns. To illustrate the differences, consider the different ways of accessing the data in your result sets:

echo $result[0]; // ordered/enumerated array, default in MDB2
echo $result['name']; // associative array
echo $result->name; // object

There is one more fetch mode type, which is MDB2_FETCHMODE_FLIPPED. It's a bit exotic and its behavior is explained in the MDB2 API documentation as:

"For multi-dimensional results, normally the first level of arrays is the row number, and the second level indexed by column number or name. MDB2_FETCHMODE_FLIPPED switches this order, so the first level of arrays is the column name, and the second level the row number."

Disconnecting

If you want to explicitly disconnect from the database, you can call:

$mdb2->disconnect();

Even if you do not disconnect explicitly, MDB2 will do that for you in its destructor.