Using MDB2

Once you've connected to your database and have set some of the options and the fetch mode, you can start executing queries. For the purpose of the examples in this article, let's say you have a table called people that looks like this:

id		name		family		birth_date
1		Eddie		Vedder		1964-12-23
2		Mike		McCready		1966-04-05
3		Stone		Gossard		1966-07-20

A Quick Example

Here's a quick example, just to get a feeling of how MDB2 can be used. You'll learn the details in a bit, but take a moment to look at the code and see if you can figure it out yourself.

require_once 'MDB2.php';
// setup
$dsn = 'mysql://root:secret@localhost/mdb2test';
$options = array ('persistent' => true);
$mdb2 =& MDB2::factory($dsn, $options);
$mdb2->setFetchMode(MDB2_FETCHMODE_ASSOC);

// execute a query
$sql = 'SELECT * FROM people';
$result = $mdb2->query($sql);

// display first names
while ($row = $result->fetchRow()) {
echo $row['name'], '
';
}

// release resources
$result->free();

// disable queries
$mdb2->setOption('disable_query', true);

// delete the third record
$id = 3;
$sql = 'DELETE FROM people WHERE id=%d';
$sql = sprintf($sql, $mdb2->quote($id, 'integer'));
echo '
Affected rows: ';
echo $mdb2->exec($sql);

// close connection
$mdb2->disconnect();
?>

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.

Fetching Data

In the example above we had:

$sql = 'SELECT * FROM people';
$result = $mdb2->query($sql);

The variable $result will be an MDB2_Result object, or more specifically, it will be a database driver-dependent class that extends MDB2_Result, for example MDB2_Result_mysql. To navigate through the result set you can use the fetchRow() method in a loop.

while ($row = $result->fetchRow()) {
echo $row['name'], '
';
}

Every time you call fetchRow(), it will move to the next record and will give you a reference to the data contained in it. Apart from fetchRow(), there are also other methods of the fetch*() family:

• fetchAll() will give you an array of all records at once.
• fetchOne() will return the value from first field of the current row if called without any parameters, or it can return any single field of any row. For example, fetchOne(1,1) will return Mike, the second column of the second row.
• fetchCol($colnum) will return all the rows in the column with number $colnum, or the first column if the $colnum parameter is not set.

Note that fetchRow() and fetchOne() will move the internal pointer to the current record, while fetchAll() and fetchCol() will move it to the end of the result set. So in the example above if you call fetchOne(1) twice, you'll get Eddie then Mike. You can also use $result->nextResult() to move the pointer to the next record in the result set or $result->seek($rownum) to move the pointer to any row specified

by $rownum. If in doubt, $result->rowCount() will tell you where in the result set your pointer currently is.

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

$sql = 'SELECT * FROM people';
$result = $mdb2->query($sql);
echo $result->numCols(); // prints 4
echo $result->numRows(); // prints 3

Shortcuts for Retrieving Data

Often it is much more convenient to directly get the data as associative arrays (or your preferred fetch mode) and not worry about navigating the result set. MDB2 provides two sets of shortcut methods query*() methods and get*() methods. They take just one method call to do the following:

1. Execute a query

2. Fetch the data returned

3. Free the resources taken by the result

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:

// the SQL statement
$sql = 'SELECT * FROM people';
// one way of getting all the data
$result = $mdb2->query($sql);
$data = $result->fetchAll();
$result->free(); // not required, but a good habit
// the shortcut way
$data = $mdb2->queryAll($sql);

In both cases if you print_r() the contents in $data and use the associative array fetch mode, you'll get:

Array ( [0] => Array ( [id] => 1
[name] => Eddie
[family] => Vedder
[birth_date] => 1964-12-23
)
[1] => Array ( [id] => 2
[name] => Mike
[family] => McCready
[birth_date] => 1966-04-05
)
...
)

get*() Shortcuts

In addition to the query*() shortcuts, you have the get*() shortcuts, which behave in the same way, but also allow you to use parameters in your queries. Consider the following example:

$sql = 'SELECT * FROM people WHERE id=?';
$mdb2->loadModule('Extended');
$data = $mdb2->getRow($sql, null, array(1));

In this example the question mark in the statement is a placeholder that will be replaced by the value in the third parameter of getRow().

You can also use named parameters, like this:

$sql = 'SELECT * FROM people WHERE id=:the_id';
$mdb2->loadModule('Extended');
$data = $mdb2->getRow( $sql,
null,
array('the_id' => 1)
);

Note that the get*() methods are in the Extended MDB2 module, which means that they are not available until you load that module using $mdb2->loadModule('Extended').

Loading modules benefits from object overloading, which was not available before PHP5, so to get access to the methods of the Extended module in PHP4, you need to call them using:

$mdb2->extended->getAll($sql);

as opposed to:

$mdb2->getAll($sql);

getAssoc()

Another useful get*() method that doesn't have a directly corresponding fetch*() or query*() is getAssoc(). It returns results just like getAll(), but the keys in the result array are the values of the first column. In addition, if there are only two columns in the result set, since one of them is already used as an array index, the other one is returned as a string (аs opposed to an array with just one element). A few examples to illustrate the differences between getAll() and getAssoc():

$sql = 'SELECT id, name FROM people';
$mdb2->loadModule('Extended');
$data = $mdb2->getAll($sql);

getAll() will return an enumerated array and each element of the array is an associative array containing all the fields.

Array ( [0] => Array ( [id] => 1
[name] => Eddie
)
[1] => Array ( [id] => 2
[name] => Mike
)
...
)

If the same query is executed with getAssoc(), like $data = $mdb2->getAssoc($sql); the result is:

Array ( [1] => Eddie
[2] => Mike
[3] => Stone
)

If your query returns more than two rows, each row will be an array, not a scalar. The code follows:

$sql = 'SELECT id, name, family FROM people';
$mdb2->loadModule('Extended');
$data = $mdb2->getAssoc($sql);

And the result:

Array ( [1] => Array ( [name] => Eddie
[family] => Vedder
)
...
)

Data Types

To address the issue of different database systems supporting different field types, MDB2 comes with its own portable set of data types. You can use MDB2's data types and have the package ensure portability across different RDBMS by mapping those types to ones that the underlying database understands.

The MDB2 data types and their default values are as follows:

$valid_types = array ( 'text' => ,
'boolean' => true,
'integer' => 0,
'decimal' => 0.0,
'float' => 0.0,
'timestamp' => '1970-01-01 00:00:00',
'time' => '00:00:00',
'date' => '1970-01-01',
'clob' => ,
'blob' => ,
)

More detailed information on the data types is available in the datatypes.html document you can find in the docs folder of your PEAR installation. You can also find this document on the Web, in the PEAR CVS repository:

http://cvs.php.net/viewcvs.cgi/pear/MDB2/docs/datatypes.html?view=co

Setting Data Types

In all the data retrieval methods that you just saw (query*(), fetch*(), get*()) you can specify the type of the results you expect and MDB2 will convert the values to the expected data type. For example the query() method accepts an array of field data types as a second parameter.

$sql = 'SELECT * FROM people';

$types = array();

$result = $mdb2->query($sql, $types);
$row = $result->fetchRow();
var_dump($row);

Here the $types array was blank, so you'll get the default behavior (no data type conversion) and all the results will be strings. The output of this example is:

array(2)
{
["id"] => string(1) "1"
["name"]=> string(5) "Eddie"
...
}

But you can specify that the first field in each record is of type integer and the second is text by setting the $types array like this:

$types = array('integer', 'text');

In this case you'll get:

array(2)
{
["id"]=> int(1)
["name"]=> string(5) "Eddie"
...
}

When setting the types, you can also use an associative array where the keys are the table fields. You can even skip some fields if you don't need to set the type for them. Some valid examples:

$types = array( 'id' => 'integer',
'name' => 'text'
);
$types = array('name'=>'text');
$types = array('integer');

Setting Data Types when Fetching Results

If you didn't set the data types during a query() call, it's still not too late. Before you start fetching, you can set the types by calling the setResultTypes() method.

// execute query
$sql = 'SELECT * FROM people';
$result = $mdb2->query($sql);

// fetch first row without type conversion
$row = $result->fetchRow();
var_dump($row['id']);
// output is: string(1) "1"

// specify types
$types = array('integer');
$result->setResultTypes($types);

// all consecutive fetches will convert
// the first column as integer
$row = $result->fetchRow();
var_dump($row['id']);
// output is: int(2)

Setting Data Types for get*() and query*()

All the get*() and query*() methods that you saw earlier in this tutorial accept data types as a second parameter, just like query() does.

You can set the data types parameter not only as an array $types = array('integer'), but also as a string $types = 'integer'. This is convenient when you work with methods that return one column only, such as getOne(), queryOne(), getCol(), and queryCol(), but you should be careful when using it for *All() and *Row() methods because the string type parameter will set the type for all the fields in the record set.

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:

$sql = 'UPDATE %s SET %s=%s WHERE id=%d';
$sql = sprintf( $sql,
$mdb2->quoteIdentifier('people'),
$mdb2->quoteIdentifier('name'),
$mdb2->quote('Eddie'), // implicit data type
$mdb2->quote(1, 'integer') // explicit type
);

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

UPDATE `people` SET `name`='Eddie' WHERE id=1

In Oracle or SQLite the same code will return:

UPDATE "people" SET "name"='Eddie' WHERE id=1

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.

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:

foreach ($result as $row)
{
var_dump($row);
}

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

while ($row = $result->fetchRow())
{
var_dump($row);
}

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:

MDB2::loadFile('Iterator');

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

$query = 'SELECT * FROM people';
$result = $mdb2->query($query, null, true, 'MDB2_BufferedIterator');

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().

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.

$mdb2->setOption('debug', 1);

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

$mdb2->getDebugOutput();

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.

$mdb2->setOption('debug', 1);
$mdb2->setOption('log_line_break', "\n\t");

$sql = 'SELECT * FROM people';
$result = $mdb2->query($sql);
$sql = 'SELECT * FROM people WHERE id = 1';
$result = $mdb2->query($sql);
$sql = 'SELECT name FROM people';
$result = $mdb2->query($sql);

$debug_array = explode("\n\t", trim($mdb2->getDebugOutput()));

echo '
';
echo implode('
', $debug_array);
echo '
';

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.