MDB2 SQL Abstraction

There are a number of features and items of SQL syntax that are implemented differently in the various database systems that MDB2 supports. MDB2 does its best to wrap the differences and provide a single interface for accessing those features, so that the developer doesn't need to worry about the implementation in the underlying database system.

Sequences

Auto-increment fields are a convenient way to define and update IDs as primary keys to your tables. The problem is that not all RDBMS support auto increments. To address this inconsistency, the concept of sequence tables is used in MDB2. The idea is that MDB2 will create and maintain a new table (without you having to worry about it) and will store and increment the last ID, which you can use later when inserting into in the main table.

Let's assume that the table people, which was used in this tutorial's examples, is empty. Before you insert into this table, you need the next consecutive ID. For this purpose you call the method nextId() to give you the new ID, like this:

$my_new_id = $mdb2->nextId('people');

Now $my_new_id has the value 1, and behind the scenes MDB2 will create a new table called people_seq with one field only, called sequence, and one row only, containing the value 1. The next time you call $mdb2->nextId('people'), MDB2 will increment the value in people_seq and return 2 to you.

sequence
1

You're free to pass any identifier as a parameter when calling nextId(). MDB2 will append _seq to your identifier and create a new table with that name, if one doesn't already exist. Unless you have special needs, it helps code readability if you use an identifier that is the name of the main table you're inserting into.

While sequence is the default name of the field in the sequence table, it can be overwritten by setting the seqcol_name option, like this:

$mdb2->setOption('seqcol_name', 'id');

Additionally, the name of the sequence table can be customized by setting the seqname_format option. Its default value is %s_seq, where %s is replaced by the identifier you pass to nextId().

Setting Limits

In MySQL you can limit the number of records returned by a query by using LIMIT. For example, the following query will give you only the first two records:

SELECT * FROM people LIMIT 0, 2;

LIMIT is MySQL-specific, so it may be missing from other database systems or implemented differently. To wrap all the differences and provide a common interface for limiting results, MDB2 offers the setLimit() method. An example:

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

If you want to define an offset (where to start when setting the limit), you specify the offset value as a second parameter:

$mdb2->setLimit(2, 1);

Note that setLimit() will affect only the next query; any query after that will behave as usual.

Another way to limit the results is by using the limitQuery() method from the Extended module. Instead of first setting the limit and then executing the query, you do it with one method call. To get two records starting from offset 1, write:

$mdb2->loadModule('Extended');
$sql = 'SELECT * FROM people';
$result = $mdb2->limitQuery($sql, null, 2, 1);

Using limitQuery() doesn't affect the queries executed after that and it returns an MDB2_Result object, just like query().

Replace Queries

MySQL supports the REPLACE statement in addition to UPDATE and INSERT. REPLACE will update the records that already exist or perform an insert otherwise. Using REPLACE directly will create portability issues in your application, which is why MDB2 wraps this functionality in the replace() method. You call replace() by providing the name of the table and an array of data about the records.

$fields=array ( 'id' => array ( 'value' => 6,
'key' => true
),
'name' => array ('value' => 'Stoyan'),
'family' => array ('value' => 'Stefanov'),
'birth_date' => array ('value' => '1975-06-20')
);
$mdb2->replace('people', $fields);

As you can see, the data to be written to the table was set using the value keys. It was also specified that the id is a key, so that (if using REPLACE directly is not an option) MDB2 can check if a record with this ID already exists. If you have a key that consists of several fields, set the 'key' => true index for all of them. Other array elements you can use are:

• type: to specify the MDB2 data type
• null: (true or false) to specify whether the null value should be used, ignoring the content in the value key

The replace() method will return the number of affected rows, just like exec() does. Technically, the replace operation is an insert if the record doesn't exist or otherwise a delete and then an insert. Therefore the replace() method will return 1 if the record didn't exist previously or 2 if an existing record was updated.

Sub-Select Support

You can pass an SQL query string to the subSelect() method. In this case, if the database system supports sub-selects, the result will be the same query unchanged. If sub-selects are not supported though, the method will execute the query and return a comma-delimited list of the result values. It is important that the query you pass to subSelect() returns only one column of results. Example:

// sub-select query string
$sql_ss = 'SELECT id FROM people WHERE id = 1 OR id = 2';
// the main query
$sql = 'SELECT * FROM people WHERE id IN (%s)';
// call subSelect()
$subselect = $mdb2->subSelect($sql_ss);
// update and print the main query
echo $sql = sprintf($sql, $subselect);
// execute
$data = $mdb2->queryAll($sql);

If sub-selects are supported, the echo statement above will output:

SELECT * FROM people WHERE id IN
(SELECT id FROM people WHERE id = 1 OR id = 2)

Otherwise you'll get:

SELECT * FROM people WHERE id IN (1, 2)

Note that subSelect() is not a full sub-query replacement, it just emulates the so-called non-correlated sub-queries. This means that your sub-selects and your main query should be executable as stand-alone queries, so in your sub-query you cannot refer to results returned by the main query, and vice-versa.

Prepared Statements

Prepared statements are a convenient and security-conscious method of writing to the database. Again, not all database systems support prepared statements, so MDB2 emulates this functionality when it's not provided natively by the RDBMS. The idea is to have the following:

• generic query with placeholders instead of real data that is passed to the prepare() method
• ome data about the records to be inserted, updated, or deleted
• call to execute() the prepared statement

The generic query may use unnamed or named placeholders, for example:

$sql = 'INSERT INTO people VALUES (?, ?, ?, ?)';

or

$sql = 'INSERT INTO people VALUES
(:id, :first_name, :last_name, :bdate)';

Then you call the prepare() method, which gives you an instance of the MDB2_Statement_* class corresponding to the current database driver you're using:

$statement = $mdb2->prepare($sql);

If you use unnamed parameters (the question marks), you need to have your data as an ordered array, like:

$data = array(
$mdb2->nextId('people'), 'Matt', 'Cameron', '1962-11-28'
);

And then you pass the data to the execute() method of the MDB2_Statement class:

$statement->execute($data);

Finally you release the resources taken:

$statement->free();

Named Parameters

If you use named parameters in your generic query, you have the convenience of using associative arrays when supplying data and not worrying about the order of the parameters as you would in the case of unnamed parameters. The following is a valid way to set data for a query with named parameters:

$data = array( 'first_name' => 'Jeff',
'last_name' => 'Ament',
'id' => $mdb2->nextId('people'),
'bdate' => '1963-03-10'
);

Binding Data

Another option for setting the data for a prepared statement is to use the bindParam() method. Here's an example:

// prepare the statement
$sql = 'INSERT INTO people VALUES
(:id, :first_name, :last_name, :bdate)';
$statement = $mdb2->prepare($sql);

// figure out the data
$id = $mdb2->nextId('people');
$first_name = 'Kirk';
$last_name = 'Hammett';
$bdate = '1962-11-18';

// bind the data
$statement->bindParam('id', $id);
$statement->bindParam('first_name', $first_name);
$statement->bindParam('last_name', $last_name);
$statement->bindParam('bdate', $bdate);

// execute and free
$statement->execute();
$statement->free();

One thing to note about bindParam() is that it takes a reference to the variable containing the data. If you're inserting several new records, therefore calling execute() multiple times, you don't have to call bindParam() for every execute(). Just calling it once and then changing the data variables is enough (in this case $id, $first_name, $last_name, and $bdate). But if you want to store the actual value when binding, you can use the method bindValue() instead of bindParam().

Another way to supply data before executing a prepared statement is to use the bindParamArray() method, which allows you to bind all parameters at once. In the code from the previous example you can replace the four calls to bindParam() with one call to bindParamArray():

$array_to_bind = array('id' => $id,
'first_name' => $first_name,
'last_name' => $last_name,
'bdate' => $bdate
);
$statement->bindParamArray($array_to_bind);

Execute Multiple

Once you have prepared a statement, you can insert multiple rows in one shot by using executeMultiple(). This method is also in the Extended MDB2 module, so you need to load it first. The data you specify must be in a multidimensional array where each element at the top level of the array is one record.

$sql = 'INSERT INTO people VALUES (?, ?, ?, ?)';
$statement = $mdb2->prepare($sql);
$data = array(
array($mdb2->nextId('people'), 'James', 'Hetfield', '1969-06-06'),
array($mdb2->nextId('people'), 'Lars', 'Ulrich', '1968-02-02')
);
$mdb2->loadModule('Extended');
$mdb2->executeMultiple($statement, $data);
$statement->free();

Auto Prepare

Instead of writing a generic query and then preparing a statement, you can have the autoPrepare() method do it for you. You supply only the name of the table, an array of field names, and the type of the queryinsert, update, or delete. If you do an update or delete, you can also give the WHERE condition as a string or an array containing different conditions, which MDB2 will concatenate with AND for you. An insert example would be:

$mdb2->loadModule('Extended');
$table = 'people';
$fields = array('id', 'name', 'family', 'birth_date');
$statement = $mdb2->autoPrepare($table, $fields, MDB2_AUTOQUERY_INSERT);

This way you'll get an MDB2_Statement object created from a generic query that looks like this:

INSERT INTO people (id, name, family, birth_date) VALUES (?, ?, ?, ?)

If you want an update statement, you can do something like this:

$mdb2->loadModule('Extended');
$table = 'people';
$fields = array('name', 'family', 'birth_date');
$where = 'id = ?';
$statement = $mdb2->autoPrepare($table, $fields, MDB2_AUTOQUERY_UPDATE, $where);

The code above will prepare this type of generic query:

UPDATE people SET name = ?, family = ?, birth_date = ? WHERE id = ?

Internally, autoPrepare() uses the buildManipSQL() method, which basically does all the work of creating the generic query, but doesn't call prepare() once the query is built. You might find this method useful in cases when you just need a query and do not intend to use prepared statements. Here's how you can delete all the records in the table with last names starting with S and s:

$mdb2->loadModule('Extended');
$sql = $mdb2->buildManipSQL(
'people',
false,
MDB2_AUTOQUERY_DELETE,
'family like "s%"');
echo $mdb2->exec($sql);

Auto Execute

The autoExecute() method is similar to autoPrepare() but it also executes the prepared statement. The difference in the parameters passed is that the array of fields should be an associative array containing bothe the field names and the data to be inserted or updated.

$mdb2->loadModule('Extended');
$table = 'people';
$fields = array ( 'id' => $mdb2->nextId('people'),
'name' => 'Cliff',
'family' => 'Burton',
'birth_date' => '1962-02-10'
);
$result = $mdb2->autoExecute($table, $fields, MDB2_AUTOQUERY_INSERT);

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.

if ($mdb2->supports('transactions'))
{
$mdb2->beginTransaction();
}
$result = $mdb2->exec('DELETE FROM people WHERE id = 33');
if (PEAR::isError($result))
{
if ($mdb2->inTransaction())
{
$mdb2->rollback();
}
}
$result = $mdb2->exec('DELETE FROM people WHERE id = invalid something');
if (PEAR::isError($result))
{
if ($mdb2->inTransaction())
{
$mdb2->rollback();
}
} elseif ($mdb2->inTransaction())
{
$mdb2->commit();
}

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.