• Home...Archives
• Ajax
• Bash
• Java
• JavaScript
• jQuery
• Linux
• MySQL
• Perl
• PHP
• SQLite
• Ubuntu
• Vi
• Windows 7
• All Pages

MDB2

The goal here is to right database agnostic code in PHP. The PEAR package MDB2 » can help.

Run the following command to install MDB2.

Example of querying a database and returning a row via a loop.

Executing Queries

To execute any query, you can use the query() or exec() methods. The query() method returns an MBD2_Result object on success, while exec() returns the number of rows affected by the query, if any. So exec() is more suitable for queries that modify data.

While you can basically perform any database operation with query(), there are other methods, discussed later, that are better suited for more specific common tasks.

http://www.installationwiki.org/MDB2#Executing_Queries »

You also have access to the number of rows and the number of columns in a result set.

query*() Shortcuts

You have at your disposal the methods queryAll(), @queryRow(), queryOne(), and queryCol(), which correspond to the four fetch*() methods explained above. Here's an example to illustrate the difference between the query*() and the fetch*()@@ methods:

http://www.installationwiki.org/MDB2#query.2A.28.29_Shortcuts »

Quoting Values and Identifiers

The different RDBMS use different quoting styles (for example single quotes ' as opposed to double quotes ") and also quote different data types inconsistently. For example, in MySQL you may (or may not) wrap integer values in quotes, but for other databases you may not be allowed to quote them at all. It's a good idea to leave the quoting job to the database abstraction layer, because it "knows" the different databases.

MDB2 provides the method quote() for quoting data and quoteIdentifier() to quote database, table, and field names. All the quotes MDB2 inserts will be the ones appropriate for the underlying RDBMS. An example:

If you echo $sql in MySQL you'll get:

In Oracle or SQLite the same code will return:

As you can see in the example above, quote() accepts an optional second parameter that sets the type of data (MDB2 type) to be quoted. If you omit the second parameter, MDB2 will try to make a best guess for the data type.

http://www.installationwiki.org/MDB2#Quoting_Values_and_Identifiers »

Iterators

MDB2 benefits from the Standard PHP Library (http://php.net/spl »), and implements the Iterator interface, allowing you to navigate through query results in a simpler manner:

For every iteration, $row will contain the next record as an array. This is equivalent to calling fetchRow() in a loop, like this:

In order to benefit from the Iterator implementation, you need to include the file Iterator.php from MDB2's directory by using the loadFile() method:

Then when you call query(), you pass the name of the Iterator class as a fourth parameter, like this:

MDB2 comes with two Iterator classes:

• MDB2_Iterator: This implements SPL's Iterator and is suitable to work with unbuffered results.
• MDB2_BufferedIterator: This extends MDB2_Iterator and implements the SeekableIterator interface. When you work with buffered results (which is the default in MDB2), it's better to use MDB2_BufferedIterator, because it provides some more methods, like count() and rewind().

http://www.installationwiki.org/MDB2#Iterators »

Debugging

MDB2 allows you to keep a list of all queries executed in an instance, this way helping you debug your application. To enable the debugging, you need to set the debug option to a positive integer.

Then you can get the collected debugging data at any point using:

You can also set the option log_line_break, which specifies how the separate entries in the debug output will be delimited. The default delimiter is a line break \n.

Take a look at the following example that sets the debug option and the line separator, executes a few queries, and then draws an unordered list with the debug output.

This example will produce:

• query(1): SELECT * FROM people
• query(1): SELECT * FROM people WHERE id = 1
• query(1): SELECT name FROM people

It's a good idea to reset the debug level to 0 when your application is in production, so that you don't have the overhead of storing all executed queries in the debug log.

http://www.installationwiki.org/MDB2#Extending_MDB2 »

Get the next auto_increment value

Some databases are not capable of the auto_increment function. MDB2 tries to maintain that abstraction with the following method.

This example shows getting the next increment id for the table people. You would use this when inserting a row into a table. Instead of leaving off the auto_increment column, you would use $mdb2->nextId('table_name') as the value for the id column every time you insert data.

It does this by actually creating another table that contains the increment number of a table.

http://www.installationwiki.org/MDB2#Sequences »

Transactions

If transactions are supported by your RDBMS, using them is very good practice to keep your data in a consistent state, should an error occur in the middle of the process of writing several pieces of data to one or more tables.

You begin by checking whether transactions are supported by your RDBMS and then you initiate a new transaction with a call to beginTransaction(). Then you start executing the different queries that comprise your transaction. After every query you can check the result and if you find it's a PEAR_Error, you can roll back (undo) the transaction and all previously executed queries within it. Otherwise you commit (finalize) the transaction. Before the calls to rollback() or commit(), you need to check if you really are in transaction, using the inTransaction() method.

Note that if transactions are not supported by your RDBMS, MDB2 will not emulate this functionality, so it is your responsibility to keep the data in a consistent state.

http://www.installationwiki.org/MDB2#Transactions »

[Click to add or edit comments])

Please prepend comments below including a date

2012-02-09