Home » Database » MDB2 » Manual

Introduction

Introduction – A feature overview

MDB2 Description

PEAR MDB2 is a merge of the PEAR DB and Metabase php database abstraction layers.

It provides a common API for all support RDBMS. The main difference to most other database abstraction packages is that MDB2 goes much further to ensure portability. Among other things MDB2 features:

• An OO-style query API
• A DSN (data source name) or array format for specifying database servers
• Datatype abstraction and on demand datatype conversion
• Portable error codes
• Sequential and non sequential row fetching as well as bulk fetching
• Ability to make buffered and unbuffered queries
• Ordered array and associative array for the fetched rows
• Prepare/execute (bind) emulation
• Sequence emulation
• Replace emulation
• Limited Subselect emulation
• Row limit support
• Transactions support
• Large Object support
• Index/Unique support
• Module Framework to load advanced functionality on demand
• Table information interface
• RDBMS management methods (creating, dropping, altering)
• Full integration into the PEAR Framework
• PHPDoc API documentation

Currently supported RDBMS:

• MySQL
• MySQLi (PHP5 only)
• PostgreSQL
• Oracle
• Frontbase (unmaintained)
• Querysim
• Interbase/Firebird (PHP5 only)
• MSSQL
• SQLite

Installation

When you install PEAR MDB2, you only get the base classes, but you also need to install the driver of the appropriate DBMS to have a working installation. For instance, to install the MySQL and the PostgreSQL drivers, you have to run these commands:

Still having problems? Check the F.A.Q.

External support resources

• external documentation
• Porting apps from DB to MDB2

DSN

DSN – The Data Source Name

Description

To connect to a database through PEAR::MDB2, you have to create a valid DSN - data source name. This DSN consists in the following parts:

• phptype: Database backend used in PHP (i.e. mysql , pgsql etc.)
• dbsyntax: Database used with regards to SQL syntax etc.
• protocol: Communication protocol to use ( i.e. tcp, unix etc.)
• hostspec: Host specification (hostname[:port])
• database: Database to use on the DBMS server
• username: User name for login
• password: Password for login
• proto_opts: Maybe used with protocol
• option: Additional connection options in URI query string format. options get separated by &. The Following table shows a non complete list of options:

The DSN can either be provided as an associative array or as a string. The array format is preferred, since it doesn't require a further parsing step (see the Connecting chapter for an example). The string format of the supplied DSN is in its fullest form:

phptype(dbsyntax)://username:password@protocol+hostspec/database?option=value

Most variations are allowed:

phptype://username:password@protocol+hostspec:110//usr/db_file.db
phptype://username:password@hostspec/database
phptype://username:password@hostspec
phptype://username@hostspec
phptype://hostspec/database
phptype://hostspec
phptype:///database
phptype:///database?option=value&anotheroption=anothervalue
phptype(dbsyntax)
phptype

The currently supported database backends are:

fbsql  -> FrontBase
ibase  -> InterBase / Firebird (requires PHP 5)
mssql  -> Microsoft SQL Server (NOT for Sybase. Compile PHP --with-mssql)
mysql  -> MySQL
mysqli -> MySQL (supports new authentication protocol) (requires PHP 5)
oci8   -> Oracle 7/8/9/10
pgsql  -> PostgreSQL
querysim -> QuerySim
sqlite -> SQLite 2

A second DSN format is supported

phptype(syntax)://user:pass@protocol(proto_opts)/database

If your database, option values, username or password contain characters used to delineate DSN parts, you can escape them via URI hex encodings:

: = %3a   / = %2f   @ = %40
+ = %2b   ( = %28   ) = %29
? = %3f   = = %3d   & = %26

Please note, that some features may be not supported by all database backends.

Example

Connect to database through a socket

mysql://user@unix(/path/to/socket)/pear

Connect to database on a non standard port

pgsql://user:pass@tcp(localhost:5555)/pear

Connect to SQLite on a Unix machine using options

sqlite:////full/unix/path/to/file.db?mode=0666

Connect to SQLite on a Windows machine using options

sqlite:///c:/full/windows/path/to/file.db?mode=0666

Connect to MySQLi using SSL

mysqli://user:pass@localhost/pear?key=client-key.pem&cert=client-cert.pem

Connect to Oracle using Service name

oci8://username:password@foo.example.com[:port]/?service=service

Connect to Oracle using "Easy Connect" syntax

username/password@[//]host[:port][/service_name]

Connecting

Connecting – Connecting and disconnecting a database

Description

To instantiate a database object you have several methods available using MDB2.

To connect to a database you have to use the function factory(), connect() or singleton(), which require a valid DSN as the first parameter. This parameter can either be a string or an array. If using an array, the array used gets merged with the default information:

$dsn = array(
    'phptype'  => false,
    'dbsyntax' => false,
    'username' => false,
    'password' => false,
    'protocol' => false,
    'hostspec' => false,
    'port'     => false,
    'socket'   => false,
    'database' => false,
    'new_link' => false,
    'service'  => false, // only in oci8
);

Any elements you set override the defaults and the remainder stay at their defaults.

The second parameter is the optional $options array that can contain runtime configuration settings for this package.

In case of success you get a new instance of the database class. It is strongly recommended to check this return value with PEAR::isError() (will detect PEAR_Error or any subclass) or the MDB2_Driver_Common specific isError().

To disconnect use the method disconnect() from your database class instance.

Connect and disconnect

<?php
require_once 'MDB2.php';

$dsn = 'pgsql://someuser:apasswd@localhost/thedb';
$options = array(
    'debug' => 2,
    'result_buffering' => false,
);

$mdb2 =& MDB2::factory($dsn, $options);
if (PEAR::isError($mdb2)) {
    die($mdb2->getMessage());
}

// ...

$mdb2->disconnect();
?>

Connect using an array for the DSN information

<?php
require_once 'MDB2.php';

$dsn = array(
    'phptype'  => 'pgsql',
    'username' => 'someuser',
    'password' => 'apasswd',
    'hostspec' => 'localhost',
    'database' => 'thedb',
);

$options = array(
    'debug'       => 2,
    'portability' => MDB2_PORTABILITY_ALL,
);

// uses MDB2::factory() to create the instance
// and also attempts to connect to the host
$mdb2 =& MDB2::connect($dsn, $options);
if (PEAR::isError($mdb2)) {
    die($mdb2->getMessage());
}
?>

When connecting to SQLite using a DSN array, the value of the mode element must be a string:

<?php
$dsn = array(
    'phptype'  => 'sqlite',
    'database' => 'thedb',
    'mode'     => '0644',
);
?>

Connect to MySQLi via SSL using an array for the DSN information

The ssl element of the $options array must be set to TRUE in order for SSL to work. Each of the extra elements in the $dsn array (key through cipher in the example below) are optional.

<?php
require_once 'MDB2.php';

$dsn = array(
    'phptype'  => 'mysqli',
    'username' => 'someuser',
    'password' => 'apasswd',
    'hostspec' => 'localhost',
    'database' => 'thedb',
    'key'      => 'client-key.pem',
    'cert'     => 'client-cert.pem',
    'ca'       => 'cacert.pem',
    'capath'   => '/path/to/ca/dir',
    'cipher'   => 'AES',
);

$options = array(
    'ssl' => true,
);

// gets an existing instance with the same DSN
// otherwise create a new instance using MDB2::factory()
$mdb2 =& MDB2::singleton($dsn, $options);
if (PEAR::isError($mdb2)) {
    die($mdb2->getMessage());
}
?>

Connect to a PostgreSQL database via a socket

<?php
require_once 'MDB2.php';

$dsn = 'pgsql://someuser:apasswd@unix(/tmp)/thedb';
$options = array(
    'debug'       => 2,
    'portability' => MDB2_PORTABILITY_ALL,
);

$mdb2 =& MDB2::factory($dsn, $options);
if (PEAR::isError($mdb2)) {
    die($mdb2->getMessage());
}
?>

Connect using singleton

<?php
require_once 'MDB2.php';

$dsn = 'pgsql://someuser:apasswd@localhost/thedb';

$mdb2 =& MDB2::singleton($dsn);
if (PEAR::isError($mdb2)) {
    die($mdb2->getMessage());
}

$blah =& new blah();
// is able to use the existing database connection
$blah->foo();

$mdb2->disconnect();

class blah
{
    function foo() {
        // get a reference to the existing database object
        $mdb2 =& MDB2::singleton();
        // ...
    }
}
?>

See

"Intro - Portability", options, setOption(), getOption().

Querying

Querying – Performing queries

Description

PEAR MDB2 provides several methods for querying databases. The most direct method is query(). It takes a SQL query string as an argument. There are two possible returns: A new MDB2_Result object for queries that return results (such as SELECT queries), or a MDB2_Error object on failure. It should not be used with statements that manipulate data (such as INSERT queries)

Doing a query

<?php
// Create a valid MDB2 object named $mdb2
// at the beginning of your program...
require_once 'MDB2.php';

$mdb2 =& MDB2::connect('pgsql://usr:pw@localhost/dbnam');
if (PEAR::isError($mdb2)) {
    die($mdb2->getMessage());
}

// Proceed with a query...
$res =& $mdb2->query('SELECT * FROM clients');

// Always check that result is not an error
if (PEAR::isError($res)) {
    die($res->getMessage());
}

// Disconnect
$mdb2->disconnect();
?>

exec() should be used for manipulation queries. There are two possible returns: An integer denoting the number of affected rows for statements that manipulate data (such as INSERT queries), or a MDB2_Error object on failure. It should not be used with statements that return results (such as SELECT queries)

Using exec to manipulate data

<?php
// Once you have a valid MDB2 object named $mdb2...
$sql  = "INSERT INTO clients (name, address) VALUES ($name, $address)";

$affected =& $mdb2->exec($sql);

// Always check that result is not an error
if (PEAR::isError($affected)) {
    die($affected->getMessage());
}
?>

Data types

MDB2 supports a number of data types across all drivers. These can be set for result sets at query or prepare time or using the setResultTypes() method. You can find an overview of the supported data types and their format here.

Limiting rows and reading from an offset

In order to read/write to only a limited number of rows from a result set and/or to start reading from a specific offset the setLimit() can be called prior to issueing the query. The limit and offset will only affected the next method call that will issue a query or prepare a statement and will automatically be reset after issueing the query. This also applies to any internal queries issues inside MDB2. Note that limit may not work with DML statements on RDBMS that emulate limit support and no error will be raised.

Using setLimit with query and exec

<?php
// Once you have a valid MDB2 object named $mdb2...

$sql = "SELECT * FROM clients";
// read 20 rows with an offset of 10
$mdb2->setLimit(20, 10);
$affected =& $mdb2->exec($sql);

$sql = "DELETE FROM clients";
if ($mdb2->supports('limit_queries') === 'emulated') {
    echo 'offset will likely be ignored'
}
// only delete 10 rows
$mdb2->setLimit(10);
$affected =& $mdb2->exec($sql);

?>

Quoting and escaping

Quoting and escaping – Quote values in a suitable format to compose a query.

Description

MDB2 provides a quote() method to quote a value into a DBMS specific format that is suitable to compose query statements. It has four parameters (only the first one is required): the value to be quoted, its datatype, whether or not to quote the value, and whether or not to escape the wildcards in the value. If you don't provide the datatype, it will be guessed from the value.

Doing a query with quoted values

<?php
// Create a valid MDB2 object named $mdb2
// at the beginning of your program...
require_once 'MDB2.php';

$mdb2 =& MDB2::connect('pgsql://usr:pw@localhost/dbnam');
if (PEAR::isError($mdb2)) {
    die($mdb2->getMessage());
}

// some sample values to be inserted
$id = 1;
$name = 'sample item';
$time = date('Y-m-d H:i:s');

// Proceed with a query...
$query = 'INSERT INTO tablename (id, itemname, saved_time) VALUES ('
    . $mdb2->quote($id,   'integer')   .', '
    . $mdb2->quote($name, 'text')      .', '
    . $mdb2->quote($time, 'timestamp') .')';
$res =& $mdb2->exec($query);
?>

With the third parameter of the quote() you can specify whether or not the above fields should be individually quoted:

Individually choose the values to be quoted

<?php
$query = 'INSERT INTO sometable (textfield1, boolfield2, datefield3) VALUES ('
    .$mdb2->quote($val1, "text", true).', '
    .$mdb2->quote($val2, "boolean", false).', '
    .$mdb2->quote($val3, "date", true).')';
?>

The above example will quote the fields and the resulting SQL will look as such:

INSERT INTO sometable FIELDS (textfield1, boolfield2, datefield3) VALUES ('blah', 1, '2006-02-21')

where the values defined were the values inserted accordingly. You will notice that the "boolfield2" is unquoted as we specified FALSE in the quote() method.

NB: If you use prepared statements, then quoting will be done automatically, you don't need to do it yourself.

Identifiers

You can quote the db identifiers (table and field names) with quoteIdentifier(). The delimiting style depends on which database driver is being used. NOTE: just because you CAN use delimited identifiers, it doesn't mean you SHOULD use them. In general, they end up causing way more problems than they solve. Anyway, it may be necessary when you have a reserved word as a field name (in this case, we suggest you to change it, if you can). Also, don't use quoteIdentifier() if you have a period in the table name itself (which, BTW, is a really bad idea), since it will consider it as a schema.table pair.

Some of the internal MDB2 methods generate queries. Enabling the quote_identifier option of MDB2 you can tell MDB2 to quote the identifiers in these generated queries. For all user supplied queries this option is irrelevant.

Portability is broken by using the following characters inside delimited identifiers:

• backtick (`) -- due to MySQL
• double quote (") -- due to Oracle
• brackets ([ or ]) -- due to Access

Delimited identifiers are known to generally work correctly under the following drivers:

• mssql
• mysql
• mysqli
• oci8
• pgsql
• sqlite

Firebird/InterBase doesn't seem to be able to use delimited identifiers via PHP 4. They work fine under PHP 5.

Quoting options

Within the MDB2 API there are a number of options to set the quoting options, one of which simply quotes the identifiers within the abstraction, the other quotes the field values on insert/update etc. when using the prepared statements methods.

When using the quote_identifier option, all of the field identifiers will be automatically quoted in the resulting SQL statements:

<?php
$mdb2->setOption('quote_identifier', true);
?>

will result in a SQL statement that all the field names are quoted with the backtick '`' operator (in MySQL).

SELECT * FROM `sometable` WHERE `id` = '123';

as opposed to:

SELECT * FROM sometable WHERE id='123';

Escape

If you want to escape a value, without surrounding it with quotes, you can use the escape() method. If you also want to escape the wildcards (_ and %), set the second parameter to TRUE

If you just want to escape the wildcards in a value, you can use the escapePattern() method.

Datatypes

Datatypes – An overview of datatype handling

Introduction

All DBMS provide multiple choice of data types for the information that can be stored in their database table fields. However, the set of data types made available varies from DBMS to DBMS.

To simplify the interface with the DBMS supported by MDB2, it was defined a base set of data types that applications may access independently of the underlying DBMS.

The MDB2 applications programming interface takes care of mapping data types when managing database options. It is also able to convert that is sent to and received from the underlying DBMS using the respective driver.

The following data type examples should be used with MDB2's createTable() method. The example array at the end of the data types section may be used with createTable() to create a portable table on the DBMS of choice (please refer to the main MDB2 documentation to find out what DBMS back ends are properly supported). It should also be noted that the following examples do not cover the creation and maintenance of indices, this chapter is only concerned with data types and the proper usage thereof.

"Global" table type modifiers

Within the MDB2 API there are a few modifiers that have been designed to aid in optimal table design. These are:

• The notnull modifiers
• The length modifiers
• The default modifiers
• unsigned modifiers for some field definitions, although not all DBMS's support this modifier for integer field types.
• fixed length modifiers for some field definitions.

Building upon the above, we can say that the modifiers alter the field definition to create more specific field types for specific usage scenarios. The notnull modifier will be used in the following way to set the default DBMS NOT NULL Flag on the field to true or false, depending on the DBMS's definition of the field value: In PostgreSQL the "NOT NULL" definition will be set to "NOT NULL", whilst in MySQL (for example) the "NULL" option will be set to "NO". In order to define a "NOT NULL" field type, we simply add an extra parameter to our definition array (See the examples in the following section)

<?php
'sometime' = array(
    'type'    = 'time',
    'default' = '12:34:05',
    'notnull' = true,
),
?>

Using the above example, we can also explore the default field operator. Default is set in the same way as the notnull operator to set a default value for the field. This value may be set in any character set that the DBMS supports for text fields, and any other valid data for the field's data type. In the above example, we have specified a valid time for the "Time" data type, '12:34:05'. Remember that when setting default dates and times, as well as datetimes, you should research and stay within the epoch of your chosen DBMS, otherwise you will encounter difficult to diagnose errors!

Example of the length modifier

<?php
'sometext' = array(
    'type'   = 'text',
    'length' = 12,
),
?>

The above example will create a character varying field of length 12 characters in the database table. If the length definition is left out, MDB2 will create a length of the maximum allowable length for the data type specified, which may create a problem with some field types and indexing. Best practice is to define lengths for all or most of your fields.

Text data type

The text data type is available with two options for the length: one that is explicitly length limited and another of undefined length that should be as large as the database allows.

The length limited option is the most recommended for efficiency reasons. The undefined length option allows very large fields but may prevent the use of indexes, nullability and may not allow sorting on fields of its type.

The fields of this type should be able to handle 8 bit characters. Drivers take care of DBMS specific escaping of characters of special meaning with the values of the strings to be converted to this type.

By default MDB2 will use variable length character types. If fixed length types should be used can be controlled via the fixed modifier.

Example of text data type with length and fixed option

<?php
'sometext' = array(
    'type'   => 'text',
    'length' => 12,
    'fixed'  => 'fixed',
),
?>

Boolean data type

The boolean data type represents only two values that can be either 1 or 0. Do not assume that these data types are stored as integers because some DBMS drivers may implement this type with single character text fields for a matter of efficiency. Ternary logic is possible by using null as the third possible value that may be assigned to fields of this type.

Example of boolean data type

<?php
'someboolean' = array(
    'type' => 'boolean',
),
?>

Integer data type

The integer data type may store integer values as large as each DBMS may handle. Fields of this type may be created optionally as unsigned integers but not all DBMS support it. Therefore, such option may be ignored. Truly portable applications should not rely on the availability of this option.

Example of integer data type

<?php
'someint' = array(
    'type'     => 'integer',
    'length'   => 10,
    'unsigned' => true,
),
?>

Decimal data type

The decimal data type may store decimal numbers accurately with a fixed number of decimal places. This data type is suitable for representing accurate values like currency amounts.

Some DBMS drivers may emulate the decimal data type using integers. Such drivers need to know in advance how many decimal places that should be used to perform eventual scale conversion when storing and retrieving values from a database. Despite this, applications may use arithmetic expressions and functions with the values stored on decimal type fields as long as any constant values that are used in the expressions are also converted with the respective MDB2 conversion functions.

The number of places that are used to the left and the right of the decimal point is pre-determined and fixed for all decimal values stored in the same database. By default, MDB2 uses 2 places to the right of the decimal point, but this may be changed when setting the database connection. The number of places available to the right of the decimal point depend on the DBMS.

It is not recommended to change the number places used to represent decimal values in database after it is installed. MDB2 does not keep track of changes in the number of decimal places. The number of decimal places can be set using the setOption() method.

Example of decimal data type

<?php
'somedecimal' = array(
    'type' => 'decimal',
),
?>

Float data type

The float data type may store floating point decimal numbers. This data type is suitable for representing numbers within a large scale range that do not require high accuracy. The scale and the precision limits of the values that may be stored in a database depends on the DBMS that it is used.

Example of float data type

<?php
'somefloat' = array(
    'type' => 'float',
),
?>

Date data type

The date data type may represent dates with year, month and day. DBMS independent representation of dates is accomplished by using text strings formatted according to the IS0-8601 standard.

The format defined by the ISO-8601 standard for dates is YYYY-MM-DD where YYYY is the number of the year (Gregorian calendar), MM is the number of the month from 01 to 12 and DD is the number of the day from 01 to 31. Months or days numbered below 10 should be padded on the left with 0.

Some DBMS have native support for date formats, but for others the DBMS driver may have to represent them as integers or text values. In any case, it is always possible to make comparisons between date values as well sort query results by fields of this type.

Example of date data type

<?php
'somedate' = array(
    'type' => 'date',
),
?>

Time data type

The time data type may represent the time of a given moment of the day. DBMS independent representation of the time of the day is also accomplished by using text strings formatted according to the ISO-8601 standard.

The format defined by the ISO-8601 standard for the time of the day is HH:MI:SS where HH is the number of hour the day from 00 to 23 and MI and SS are respectively the number of the minute and of the second from 00 to 59. Hours, minutes and seconds numbered below 10 should be padded on the left with 0.

Some DBMS have native support for time of the day formats, but for others the DBMS driver may have to represent them as integers or text values. In any case, it is always possible to make comparisons between time values as well sort query results by fields of this type.

Example of time data type

<?php
'sometime' = array(
    'type'    => 'time',
    'default' => '12:34:05',
    'notnull' => true,
),
?>

Timestamp data type

The timestamp data type is a mere combination of the date and the time of the day data types. The representation of values of the time stamp type is accomplished by joining the date and time string values in a single string joined by a space. Therefore, the format template is YYYY-MM-DD HH:MI:SS. The represented values obey the same rules and ranges described for the date and time data types

Example of timestamp data type

<?php
'sometimestamp' = array(
    'type' => 'timestamp',
),
?>

Large object (file) data types

The large object data types are meant to store data of undefined length that may be too large to store in text fields, like data that is usually stored in files.

MDB2 supports two types of large object fields: Character Large OBjects (CLOBs) and Binary Large OBjects (BLOBs). CLOB fields are meant to store only data made of printable ASCII characters. BLOB fields are meant to store all types of data.

Large object fields are usually not meant to be used as parameters of query search clause (WHERE) unless the underlying DBMS supports a feature usually known as "full text search"

Example of large object data types

<?php
'someblob' = array(
    'type' => 'blob',
),
// or
'someclob' = array(
    'type' => 'clob',
),
?>

Putting it all together

Example of field definition

<?php
$fields = array(
    'id' => array(
        'type'     => 'text',
        'length'   => 32,
        'fixed' => true,
    ),
    'someint' => array(
        'type'     => 'integer',
        'length'   => 10,
        'unsigned' => true,
    ),
    'sometext' => array(
        'type'   => 'text',
        'length' => 12,
    ),
    'somedate' => array(
        'type' => 'date',
    ),
    'sometimestamp' => array(
        'type' => 'timestamp',
    ),
    'someboolean' => array(
        'type' => 'boolean',
    ),
    'somedecimal' => array(
        'type' => 'decimal',
    ),
    'somefloat' => array(
        'type' => 'float',
    ),
    'sometime' => array(
        'type'    => 'time',
        'default' => '12:34:05',
        'notnull' => true,
    ),
    'somedate' => array(
        'type' => 'date',
    ),
    'someclob' => array(
        'type' => 'clob',
    ),
    'someblob' => array(
        'type' => 'blob',
    ),
);
?>

The above example will create a database table as such:

Custom Data Types and Further reading

Custom data types can be defined. This will be explored soon. For now you can refer to these blog posts:

• To serialize or to not serialize?
• Custom Types in PEAR::MDB2

Further reading should be done at the following URL's: getTypeDeclaration, getDeclaration.

Results

Results – Obtaining data from query results

Description

Fetching Individual Rows From Query Results

The MDB2_Result_Common object provides two methods for fetching data from rows of a result set: fetchOne(), fetchRow(), fetchCol() and fetchAll().

fetchRow() and fetchOne() read an entire row or a single field from a column respectively. The result pointer gets moved to the next row each time these methods are called. NULL is returned when the end of the result set is reached.

fetchAll() and fetchCol() read all rows in the result set and therefore move the result pointer to the end. While fetchAll() reads the entire row data, fetchCol() only reads a single column.

MDB2_Error is returned if an error is encountered.

Fetching a result set

<?php
// Create a valid MDB2 object named $mdb2
// at the beginning of your program...
require_once 'MDB2.php';

$mdb2 =& MDB2::connect('pgsql://usr:pw@localhost/dbnam');
if (PEAR::isError($mdb2)) {
    die($mdb2->getMessage());
}

// Proceed with getting some data...
$res =& $mdb2->query('SELECT * FROM mytable');

// Get each row of data on each iteration until
// there are no more rows
while (($row = $res->fetchRow())) {
    // Assuming MDB2's default fetchmode is MDB2_FETCHMODE_ORDERED
    echo $row[0] . "\n";
}

// while (($one = $res->fetchOne())) {
//     echo $one . "\n";
// }

?>

Formats of Fetched Rows

The data from the row of a query result can be placed into one of three constructs: an ordered array (with column numbers as keys), an associative array (with column names as keys) or an object (with column names as properties).

MDB2_FETCHMODE_ORDERED (default)

     
Array
(
    [0] => 28
    [1] => hi
)
     
    

MDB2_FETCHMODE_ASSOC

     
Array
(
    [a] => 28
    [b] => hi
)
     
    

MDB2_FETCHMODE_OBJECT

     
stdClass Object
(
    [a] => 28
    [b] => hi
)
     
    

NOTE: When a query contains the same column name more than once (such as when joining tables which have duplicate column names) and the fetch mode is MDB2_FETCHMODE_ASSOC or MDB2_FETCHMODE_OBJECT, the data from the last column with a given name will be the one returned. There are two immediate options to work around this issue:

• Use aliases in your query, for example People.Name AS PersonName
• Change the fetch mode to MDB2_FETCHMODE_ORDERED

TIP: If you are running into this issue, it likely indicates poor planning of the database schema. Either data is needlessly being duplicated or the same names are being used for different kinds of data.

How to Set Formats

You can set the fetch mode each time you call a fetch method and/or you can set the default fetch mode for the whole MDB2 instance by using the setFetchMode() method.

Determining fetch mode per call

<?php
// Once you have a valid MDB2 object named $mdb2...
$res =& $mdb2->query('SELECT * FROM users');

while ($row = $res->fetchRow(MDB2_FETCHMODE_ASSOC)) {
    echo $row['id'] . "\n";
}
?>

Changing default fetch mode

<?php
// Once you have a valid MDB2 object named $mdb2...
$mdb2->setFetchMode(MDB2_FETCHMODE_ASSOC);

$res =& $mdb2->query('SELECT * FROM users');

while ($row = $res->fetchRow()) {
    echo $row['id'] . "\n";
}
?>

Fetch Rows by Number

The PEAR MDB2 fetch system also supports an extra parameter to the fetch statement. So you can fetch rows from a result by number. This is especially helpful if you only want to show sets of an entire result (for example in building paginated HTML lists), fetch rows in an special order, etc.

Fetching by number

<?php
// Once you have a valid MDB2_Result object named $res...

// the row to start fetching
$from = 50;

// how many results per page
$resPage = 10;

// the last row to fetch for this page
$to = $from + $resPage;

foreach (range($from, $to) as $rowNum) {
    if (!($row = $res->fetchRow(MDB2_FETCHMODE_ORDERED, $rowNum))) {
        break;
    }
    echo $row[0] . "\n";
}
?>

Getting Entire Result Sets

The MDB2_Result_Common object provides several methods to read entire results sets: fetchCol() and fetchAll().

Freeing Result Sets

Once you finish using a result set, if your script continues for a while, it's a good idea to save memory by freeing the result set via Use free().

Freeing

<?php
// Once you have a valid MDB2 object named $mdb2...
$res =& $mdb2->query('SELECT name, address FROM clients');
while ($row = $res->fetchRow(MDB2_FETCHMODE_ASSOC)) {
    echo $row['name'] . ', ' . $row['address'] . "\n";
}
$res->free();
?>

Getting the native result resource

If whatever data you need to read from a result set is not yet implemented in MDB2 you can get the native result resource using the getResource() method and then call the underlying PHP extension directly (though this would of course require that it is now up to you to make this sufficiently portable).

Native result resource

<?php
// Once you have a valid MDB2 object named $mdb2...
$res =& $mdb2->query('SELECT name, address FROM clients');
$native_result = $res->getResource();
?>

Getting More Information From Query Results

With MDB2 there are four ways to retrieve useful information about the query result sets themselves:

numRows() tells how many rows are in a SELECT query result

<?php
// Once you have a valid MDB2 object named $mdb2...
$res =& $mdb2->query('SELECT * FROM phptest');
if ($mdb2->getOption('result_buffering')) {
    echo $res->numRows();
} else {
    echo 'cannot get number of rows in the result set when "result_buffering" is disabled';
}
?>

numCols() tells how many columns are in a SELECT query result

<?php
// Once you have a valid MDB2 object named $mdb2...
$res =& $mdb2->query('SELECT * FROM phptest');
echo $res->numCols();
?>

rowCount() tells which row number the internal result row pointer currently points to

<?php
// Once you have a valid MDB2 object named $mdb2...
$res =& $mdb2->query('SELECT * FROM phptest');
$res->fetchRow();
// returns 2 if the result set contains at least 2 rows
echo $res->rowCount();

?>

getColumnNames() returns an associative array with the names of the column of the result set as keys and the position inside the result set as the values

<?php
// Once you have a valid MDB2 object named $mdb2...
$res =& $mdb2->query('SELECT * FROM phptest');
print_r($res->getColumnNames());

?>

seek() allows to seek to a specific row inside a result set. Note that seeking to previously read rows is only possible if the 'result_buffering' option is left enabled, otherwise only forward seeking is supported.

<?php
// Once you have a valid MDB2 object named $mdb2...
$res =& $mdb2->query('SELECT * FROM phptest');
// Seek to the 10th row in the result set
$res->seek(10);

?>

nextResult() allows iterate over multiple results returned by a multi query.

<?php
// Once you have a valid MDB2 object named $mdb2...
$multi_query = $this->db->setOption('multi_query', true);
// check if multi_query can be enabled
if (!PEAR::isError($multi_query)) {
    $res =& $mdb2->query('SELECT * FROM phptest; SELECT * FROM phptest2;');
    $data1 = $res->fetchAll();
    // move result pointer to the next result
    $res->nextResult();
    $data2 = $res->fetchAll();
} else {
    echo 'multi_query option is not supported';
}

?>

Another nextResult() example with a Stored Procedure returning multiple result sets.

<?php
$result = $db->executeStoredProcedure('procedureName', array('argument1'));
do {
    while ($row = $result->fetchRow(MDB2_FETCHMODE_OBJECT)) {
        var_dump($row);
    }
} while ($result->nextResult());
$result->free();
?>

bindColumn() allows to bind a reference to a user variable to a specific field inside the result set. This means that when fetching the next row, this variable is automatically updated.

<?php
// Once you have a valid MDB2 object named $mdb2...
$name = $address = null;
$res =& $mdb2->query('SELECT id, name, address FROM clients', array('id' => 'integer'));
$res->bindColumn('id', $id);
// provide a type for the column not included in the query() call
$res->bindColumn('name', $name, 'text');
// but specifying a type is as always optional in MDB2
$res->bindColumn('address', $address);
while ($res->fetchRow()) {
    echo "The address of '$name' (user id '$id') is '$address'\n";
}
?>

Querying and fetching in one call

All of the fetch methods are also available in a variant that executes a query directly: queryOne(), queryRow(), queryCol() and queryAll().

<?php
// Once you have a valid MDB2 object named $mdb2...
$data = $mdb2->queryAll('SELECT * FROM phptest');
print_r($data);

?>

Users that prefer to use prepared statements can make use of the following methods from the Extended module: getOne(), getRow(), getCol(), getAll() and getAssoc().

<?php
// Once you have a valid MDB2 object named $mdb2...
$mdb2->loadModule('Extended');
$query = 'SELECT * FROM phptest WHERE id = ?';
$data = $mdb2->extended->getRow($query, null, array(1), array('integer'));
print_r($data);

?>

Data types

MDB2 supports a number of data types across all drivers. These can be set for result sets at query or prepare time or using the setResultTypes() method. You can find an overview of the supported data types and their format here.

Fetching LOBs

To retrieve a Large Object (BLOB or CLOB), you can use streams, as you were reading a file

Fetching LOBs with streams.

<?php
$result =& $mdb2->query('SELECT document, picture FROM files WHERE id = 1', array('clob', 'blob'));
if (PEAR::isError($result) || !$result->valid()) {
    //uh-oh
}
$row = $result->fetchRow();

//fetch the Character LOB into the $clob_value variable
$clob = $row[0];
if (!PEAR::isError($clob) && is_resource($clob)) {
    $clob_value = '';
    //use streams
    while (!feof($clob)) {
        $clob_value .= fread($clob, 8192);
    }
    $mdb2->datatype->destroyLOB($clob);
}

//fetch the Binary LOB into the $blob_value variable
$blob = $row[1];
if (!PEAR::isError($blob) && is_resource($blob)) {
    $blob_value = '';
    while (!feof($blob)) {
        $blob_value.= fread($blob, 8192);
    }
    $mdb2->datatype->destroyLOB($blob);
}

//free the result
$result->free();
?>

Checking for Errors

Don't forget to use isError() to check if your actions return a MDB2_Error object.

Prepare & Execute

Prepare & Execute – Prepare and execute SQL statements

Description

Purpose

prepare() and execute() give you more power and flexibilty for query execution. Prepare/execute mode is helpful when you have to run the same query several times but with different values in it, such as adding a list of addresses into a database.

Another place prepare/execute is useful is supporting databases which have different SQL syntaxes. Imagine you want to support two databases with different INSERT syntax:

Corresponding to create multi-lingual scripts you can create a array with queries like this:

<?php
$statement['db1']['INSERT_PERSON'] = 'INSERT INTO person
    (surname, name, age) VALUES (?, ?, ?)';

$statement['db2']['INSERT_PERSON'] = 'INSERT INTO person
    SET surname=?, name=?, age=?';
?>

Furthermore it is also possible to use named placeholders as inspired by Oracle. Using named placeholders also allows using the same placeholder name multiple times inside a single statement:

<?php
$statement['db1']['INSERT_PERSON'] = 'INSERT INTO person
    (surname, name, age) VALUES (:surname, :lastname, :age)';

$statement['db2']['INSERT_PERSON'] = 'INSERT INTO person
    SET surname=:surname, name=:lastname, age=:age';
?>

NB: we don't recommend using non-standard SQL syntax. The example above is just meant to show what you can do with prepared statements, but if you use MDB2 because you heart portability, then be sure you're using a standard SQL syntax (hint: the db1 INSERT is correct).

Prepare

To use the features above, you have to do two steps. Step one is to prepare the statement which returns an instance of the MDB2_Statement_Common class. The second step is to execute it.

To start out, you need to prepare() a generic SQL statement. Create a generic statement by writing the SQL query as usual:

SELECT surname, name, age
    FROM person
    WHERE name = 'name_to_find' AND age < age_limit

Then substitute "placeholders" for the literal values which will be provided at run time:

SELECT surname, name, age
    FROM person
    WHERE name = ? AND age < ?

Then pass this SQL statement to prepare(), which returns a statement class instance to be used when calling execute().

prepare() can handle different types of placeholders (a.k.a. wildcards). By default all placeholders are handled as strings. However passing an array of data types as the second parameter makes it possible to set a specific type for each placeholder.

Since DML (data manipulation language - INSERT, UPDATE, DELETE) statements have different return values than data fetches the prepare() accepts a third parameter. This parameter should be set to MDB2_PREPARE_MANIP for DML statements (this way the number of affected rows will be returned). For data reads it should either be set to MDB2_PREPARE_RESULT, an array of data types for each of the columns in the result set or NULL in order to automatically detect the data types in the result set.

Execute

After preparing the statement, you can execute the query. This means to assign the variables to the prepared statement. To do this, execute() requires one argument a scalar or array with the values to assign.

Passing scalars to execute()

<?php
// Once you have a valid MDB2 object named $mdb2...
$sth = $mdb2->prepare('INSERT INTO numbers (number) VALUES (?)', array('integer'), MDB2_PREPARE_MANIP);
$sth->execute(1);
$sth->execute(8);
?>

When a prepared statement has multiple placeholders, you must use an array to pass the values to execute(). The first entry of the array represents the first placeholder, the second the second placeholder, etc. The order is independent of the type of placeholder used.

Passing an array to execute()

<?php
// Once you have a valid MDB2 object named $mdb2...
$types = array('integer', 'text', 'text');
$sth = $mdb2->prepare('INSERT INTO numbers VALUES (?, ?, ?)', $types, MDB2_PREPARE_MANIP);

$data = array(1, 'one', 'en');
$affectedRows = $sth->execute($data);
?>

When using named placeholders the data array needs to be an associative array with the keys matching the placeholder names.

Passing an array to execute()

<?php
// Once you have a valid MDB2 object named $mdb2...
$types = array('integer', 'text', 'text');
$sth = $mdb2->prepare('INSERT INTO numbers VALUES (:id, :name, :lang)', $types);

$data = array('id' => 1, 'name' => 'one', 'lang' => 'en');
$affectedRows = $sth->execute($data);
?>

When using named placeholders the data array needs to be an associative array with the keys matching the placeholder names.

Passing an array to execute()

<?php
// Once you have a valid MDB2 object named $mdb2...
$sth = $mdb2->prepare('SELECT name, lang FROM numbers WHERE id = ?', array('integer'), array('text', 'text'));

$result = $sth->execute(1);
?>

The values passed in $data must be literals. Do not submit SQL functions (for example CURDATE()). SQL functions that should be performed at execution time need to be put in the prepared statement. Similarly, identifiers (i.e. table names and column names) can not be used because the names get validated during the prepare phase.

ExecuteMultiple

MDB2 contains a process for executing several queries at once. So, rather than having to execute them manually, like this:

Passing arrays to execute()

<?php
// Once you have a valid MDB2 object named $mdb2...
$alldata = array(array(1, 'one', 'en'),
                 array(2, 'two', 'to'),
                 array(3, 'three', 'tre'),
                 array(4, 'four', 'fire'));
$sth = $mdb2->prepare('INSERT INTO numbers VALUES (?, ?, ?)');
foreach ($alldata as $row) {
    $sth->execute($row);
}
?>

which would issue four queries:

INSERT INTO numbers VALUES ('1', 'one', 'en')
INSERT INTO numbers VALUES ('2', 'two', 'to')
INSERT INTO numbers VALUES ('3', 'three', 'tre')
INSERT INTO numbers VALUES ('4', 'four', 'fire')

you can use executeMultiple() to avoid the explicit foreach in the example above:

Using executeMultiple() from the Extended Module instead of execute()

<?php
// Once you have a valid MDB2 object named $mdb2...
$mdb2->loadModule('Extended', null, false);
$alldata = array(array(1, 'one', 'en'),
                 array(2, 'two', 'to'),
                 array(3, 'three', 'tre'),
                 array(4, 'four', 'fire'));
$sth = $mdb2->prepare('INSERT INTO numbers VALUES (?, ?, ?)');
$mdb2->extended->executeMultiple($sth, $alldata);
?>

The result is the same. If one of the records failed, the unfinished records will not be executed.

execute() has three possible returns: a new MDB2_Result_Common object for queries that return results (such as SELECT queries), integer for queries that manipulate data (such as INSERT queries) or a MDB2_Error object on failure

Data types

MDB2 supports a number of data types across all drivers. These can be set for result sets at query or prepare time or using the setResultTypes() method. You can find an overview of the supported data types and their format here.

Freeing Prepared Statements

Once you finish using prepared statements, if your script continues for a while, it's a good idea to save memory by freeing the prepared statement set via Use free().

Freeing

<?php
// Once you have a valid MDB2 object named $mdb2...
$sth = $mdb2->prepare('SELECT name, lang FROM numbers WHERE id = ?', array('integer'), array('text', 'text'));

$result = $sth->execute(1);

$sth->free();
?>

Limiting rows and reading from an offset

In order to read/write to only a limited number of rows from a result set and/or to start reading from a specific offset, the setLimit() can be called prior to calling prepare(). The limit and offset will only affect the next method call that will issue a query or prepare a statement and will automatically be reset after issuing the query. This also applies to any internal queries issued inside MDB2. Note that limit may not work with DML statements on RDBMS that emulate limit support and no error will be raised.

Using setLimit with prepare

<?php
// Once you have a valid MDB2 object named $mdb2...
// read 20 rows with an offset of 10
$mdb2->setLimit(20, 10);
$sth = $mdb2->prepare('SELECT name, lang FROM numbers WHERE group_id = ?', array('integer'), array('text', 'text'));

?>

Transactions

Transactions – Performing transactions

Description

PEAR MDB2 defaults to auto-committing all queries. However using the beginTransaction() method one can open a new transaction and with the commit() and rollback() methods, a transaction is finished. These three methods optionally accept a string name of a savepoint to set, release or rollback to respectively. The method inTransaction() may be used to check if a transaction is currently open.

Doing a transaction

<?php
// Create a valid MDB2 object named $mdb2
// at the beginning of your program...
require_once 'MDB2.php';

$mdb2 =& MDB2::connect('pgsql://usr:pw@localhost/dbnam');
if (PEAR::isError($mdb2)) {
    die($mdb2->getMessage());
}

// check if transaction are supported by this driver
if (!$mdb2->supports('transactions')) {
    exit();
}

// Open a transaction
$res = $mdb2->beginTransaction();

..

// check if we are inside a transaction and if savepoints are supported
if ($mdb2->inTransaction() && $mdb2->supports('savepoints')) {
    // Set a savepoint
    $savepoint = 'MYSAVEPOINT';
    $res = $mdb2->beginTransaction($savepoint);

    ..

    // determine if the savepoint should be released or to rollback to the savepoint
    if ($error_condition) {
        $res = $mdb2->rollback($savepoint);
    } else {
        $res = $mdb2->commit($savepoint);
    }
}

..

// determine if the commit or rollback
if ($error_condition) {
    $res = $mdb2->rollback();
} else {
    $res = $mdb2->commit();
}

?>

PEAR MDB2 does not emulate transactions or savepoints. This means that it depends on the underlying RDBMS (and in the case of MySQL the storage engines used) if transactions will be available in MDB2. Also note that some RDBMS implicitly commit transactions when executing DDL statements - notable exceptions are Oracle and PostgreSQL.

MDB2 also supports "nested" transactions using the beginNestedTransaction() method. Actually these are not true nested transactions as they are natively supported in Interbase for example. MDB2 maintains a counter of opened nested transactions. The transaction is finished once that counter is decremented back to 1 with completeNestedTransaction() calls. If the RDBMS supports savepoints then MDB2 will automatically set a savepoint on every call of the beginNestedTransaction() method after the initial call and will return the name. These savepoints are automatically released by the completeNestedTransaction() method. The name of these automatic savepoints are determined by the "savepoint_format" option and the nested transaction counter. The "savepoint_format" defaults to 'MDB2_SAVEPOINT_%s'.

If, after initially opening a nested transaction, an unexpected PEAR error is raised on the MDB2 instance the transaction is rolled back, otherwise it is commited at this point. Using the getNestedTransactionError() method it is possible to check if there has been an error inside the transaction. Alternatively a rollback can be forced using the failNestedTransaction(). This method optionally accepts a mixed parameter which will set the error to return if the getNestedTransactionError() method is called, as well as a second boolean parameter that optionally forces an immidiate rollback.

Using emulated nested transactions

<?php

$mdb2->beginNestedTransaction(); # open transaction

$query = "INSERT INTO autoinc (id) VALUES (?)";
$stmt = $mdb2->prepare($query);

$stmt->execute(array(1));

$savepoint = $mdb2->beginNestedTransaction(); # ignored / sets savepoint

..

// never true for this example
if (false) {
    // raise an error
    $error = $mdb2->raiseError(MDB2_ERROR, null, null, 'kaboom');
    $mdb2->failNestedTransaction();
}

if(($error = $mdb2->getNestedTransactionError())) {
    die($error->getUserinfo());
}

$mdb2->completeNestedTransaction(); # ignored / releases savepoint

$stmt->execute(array(2));

$mdb2->completeNestedTransaction(); # commit

?>

Finally MDB2 supports setting the transaction isolation level as per the SQL 92 standard using the setTransactionIsolation() method. If a given RDBMS does not support a given isolation level but supports a higher more strict isolation level, then MDB2 silently uses that higher transaction level. Some RDBMS systems support additional options which are silently ignored if they are not supported.

Setting the transaction isolation level

<?php

$options = array('wait' => 'WAIT', 'rw' => 'READ WRITE');
$options = array('wait' => 'NO WAIT', 'rw' => 'READ ONLY');

$isolation_level = READ UNCOMMITTED # (allows dirty reads)
$isolation_level = READ COMMITTED # (prevents dirty reads)
$isolation_level = REPEATABLE READ # (prevents nonrepeatable reads)
$isolation_level = SERIALIZABLE # (prevents phantom reads)
$mdb2->setTransactionIsolation($isolation_level, $options);

?>

Modules

Modules – Loading and calling modules

Description

MDB2 follows a modular concept to provide functionality beyond the basic ability to send queries to the database and fetch result sets. Currently the following modules are available:

• Datatype module (API) - handles datatype abstraction via the MDB2_Datatype_Common class
• Extended module (API) - provides numerous high-level methods via the MDB2_Extended class
• Function module (API) - handles SQL function abstraction via the MDB2_Function_Common class
• Manager module (API) - handles data definition language (DDL) abstraction and schema listing via the MDB2_Manager_Common class
• Native module (API) - handles RDBMS specific functions via the MDB2_Native_Common class
• Reverse module (API) - handles schema reverse engineering abstraction via the MDB2_Reverse_Common class

A module is loaded using the loadModule() method. This method returns the module instance, but also stores the instance in a property. The name of the property is either the lowercased name of the module passed in as the first parameter, or optionally the non null value of the second parameter. The optional third parameter is used to differentiate modules that depend on a specific RDBMS (like the Datatype module) and those that do not (like the Extended module). The method can also be used to load custom modules that are installed.

The third parameter is automatically detected if it is not set. On hosts that have 'safe_mode' enabled automatic detection does however require silenced falls to fopen(). Error handling and error handlers should be configured accordingly.

Loading a module

<?php
require_once 'MDB2.php';

$dsn = 'pgsql://someuser:apasswd@localhost/thedb';
$options = array(
    'debug' => 2,
    'result_buffering' => false,
);

$mdb2 =& MDB2::connect($dsn, $options);
if (PEAR::isError($mdb2)) {
    die($mdb2->getMessage());
}

// ...

$mdb2->loadModule('Manager');
// specifically stating that the module that is being loaded is RDBMS independent
// this works around some needless internal calls
$mdb2->loadModule('Extended', null, false);
?>

Loading a custom module that is RDBMS independent

<?php
// ...

// file must reside in [peardir]/MDB2/MyModule.php
class MDB2_MyModule extends MDB2_Module_Common
{
  function myMethod()
  {
    $db =& $this->getDBInstance();
    if (PEAR::isError($db)) {
        return $db;
    }
    ...
  }
} 
?>

<?php
// ...

// file must reside in [peardir]/MDB2/MyModule.php
$mdb2->loadModule('MyModule');

?>

Loading a custom module that is RDBMS dependent

<?php
// ...

// file must reside in [peardir]/MDB2/Driver/MyRDBMSModule/pgsql.php
// this is the class that would get loaded for an MDB2 PostgreSQL instance
// equivalent classes for other backends would need to implemented,
// potentially making use of a common base class
class MDB2_Driver_MyRDBMSModule_pgsql extends MDB2_Module_Common
{
  function myRDBMSMethod()
  {
    $db =& $this->getDBInstance();
    if (PEAR::isError($db)) {
        return $db;
    }
    ...
  }
} 
?>

<?php
// ...

// file must reside in [peardir]/MDB2/Driver/MyRDBMSModule/[phptype].php
$mdb2->loadModule('MyRDBMSModule');

?>

Using a loaded module

<?php
// ...

// loading into default name
$mdb2->loadModule('Manager');
$tables = $mdb2->manager->listTables();

// loading into non standard property $foo
$mdb2->loadModule('Function', 'foo');
$tables = $mdb2->foo->concat($str1, $str2);

?>

On PHP5 users can also rely on overloading to load and call modules.

Using the 'modules' option with PHP5 overloading

<?php
require_once 'MDB2.php';

$dsn = 'pgsql://someuser:apasswd@localhost/thedb';
$options = array(
    'debug' => 2,
    'result_buffering' => false,
);

$mdb2 =& MDB2::connect($dsn, $options);
if (PEAR::isError($mdb2)) {
    die($mdb2->getMessage());
}

// ...

$module_shorthands = $mdb2->getOptions('modules');

// use the shorthand key for the given module as a prefix for the method name
// where the first letter of the original method name is uppercased
$tables = $mdb2->mgListTables();
?>

Calling a method on a loaded module with PHP5 overloading

<?php
require_once 'MDB2.php';

$dsn = 'pgsql://someuser:apasswd@localhost/thedb';
$options = array(
    'debug' => 2,
    'result_buffering' => false,
);

$mdb2 =& MDB2::connect($dsn, $options);
if (PEAR::isError($mdb2)) {
    die($mdb2->getMessage());
}

// ...

$mdb2->loadModule('Manager');

// since the manager module is already loaded we can call the listTable() method
$tables = $mdb2->manager->listTables();

// NB: on PHP5, where __autoload() is available,
// the above line can be rewritten as:
$tables = $mdb2->listTables();
?>

Function Module

Function Module – Module to handle SQL function abstraction

Description

The Function module provides methods for executing non-standard SQL database functions in a consistent way. The following document lists the available methods, providing examples of their use. To include the Function module functionality, you need to load it first.

Loading the Function module

<?php
require_once 'MDB2.php';
$dsn = 'pgsql://someuser:apasswd@somehost';
$mdb2 =& MDB2::factory($dsn);
if (PEAR::isError($mdb2)) {
    die($mdb2->getMessage());
}

// loading the Function module
$mdb2->loadModule('Function');
?>

After including the module, you can access its methods like this:

Get the length of a string expression

<?php
// PHP5
$mdb2->length('expression');
// PHP4 and PHP5
$mdb2->function->length('expression');
?>

Further in the document the PHP5-compatible way will be used.

Concatenate strings

<?php
$mdb2->concat('string1', 'string2');
?>

Execute a Stored Procedure

Supposing we have the following Stored Procedure (MySQL syntax):

DELIMITER //
CREATE PROCEDURE procedure1 (IN parameter1 INTEGER)
BEGIN
  DECLARE variable1 CHAR(10);
  IF parameter1 = 17 THEN
    SET variable1 = 'birds';
  ELSE
    SET variable1 = 'beasts';
  END IF;
  INSERT INTO table1 VALUES (variable1);
END 
//
DELIMITER ;

we can call it this way:

Execute a Stored Procedure

<?php
$params = array(17);
$mdb2->executeStoredProc('procedure1', $params);
?>

Manager Module

Manager Module – Module for managing database structure

Description

The Manager module provides methods for managing database structure. The methods can be grouped based on their responsibility: create, edit (alter or update), list or delete (drop) database elements. The following document lists the available methods, providing examples of their use. The following tables will be created, altered and finally dropped, in a database named "events_db":

To include the Manager module functionality, you need to load it first.

Loading the Manager module

<?php
require_once 'MDB2.php';
$dsn = 'pgsql://someuser:apasswd@somehost';
$mdb2 =& MDB2::factory($dsn);
if (PEAR::isError($mdb2)) {
    die($mdb2->getMessage());
}

// loading the Manager module
$mdb2->loadModule('Manager');
?>

After including the module, you can access its methods like this:

Creating a database

<?php
// PHP5
$mdb2->createDatabase('events_db');
// PHP4 and PHP5
$mdb2->manager->createDatabase('events_db');
?>

Further in the document the PHP5-compatible way will be used.

Creating database elements

These are methods to create new databases, tables, indices, constraints and sequences.

Creating a database

Creating and setting an events_db database

<?php
// MDB2 setup
require_once 'MDB2.php';
$dsn = 'mysql://root:test@localhost'; // note that DB name is omitted
$mdb2 =& MDB2::factory($dsn);

// loading the Manager module
$mdb2->loadModule('Manager');

// create the database
$mdb2->createDatabase('events_db');

// set the new database to work with it
$mdb2->setDatabase('events_db');

// the next time the DSN could be like
// mysql://root:test@localhost/events_db
?>

Creating tables

Now that the database is created, we can proceed with adding some tables. The method createTable() takes three parameters: the table name, an array of field definition and some extra options (optional and RDBMS-specific).

Creating the events table

<?php
$definition = array (
    'id' => array (
        'type' => 'integer',
        'unsigned' => 1,
        'notnull' => 1,
        'default' => 0,
    ),
    'name' => array (
        'type' => 'text',
        'length' => 255
    ),
    'datetime' => array (
        'type' => 'timestamp'
    )
);

$mdb2->createTable('events', $definition);
?>

The keys of the definition array are the names of the fields in the table. The values are arrays containing the required key 'type' as well as other keys, depending on the value of 'type'. The values for the 'type' key are the same as the possible MDB2 datatypes. Depending on the datatype, the other options may vary.

The third parameter to createTable() contains extra options for the table, such as the charset, collation, and other DBMS-specific properties, like MySQL's table engine. Here's an example for MySQL.

Creating the people table

<?php
$table_options = array(
    'comment' => 'Repository of people',
    'charset' => 'utf8',
    'collate' => 'utf8_unicode_ci',
    'type'    => 'innodb',
);
$definition = array (
    'id' => array (
        'type' => 'integer',
        'unsigned' => 1,
        'notnull' => 1,
        'default' => 0,
    ),
    'name' => array (
        'type' => 'text',
        'length' => 255
    )
);
$mdb2->createTable('people', $definition, $table_options);
?>

To round up the example database, here's the event_participants table creation code.

Creating the event_participants table

<?php
$definition = array (
    'event_id' => array (
        'type' => 'integer',
        'unsigned' => 1,
        'notnull' => 1,
        'default' => 0,
    ),
    'person_id' => array (
        'type' => 'integer',
        'unsigned' => 1,
        'notnull' => 1,
        'default' => 0,
    ),
);
$mdb2->createTable('event_participants', $definition, $table_options);
?>

Creating constraints

In the example events table, the id should be a primary key. Creating a primary key is a task done by the createConstraint() method. It takes three parameters: the table name, the constraint name and the definition array.

The full structure of the definition array looks like this (in this case, it's representing a FOREIGN KEY constraint):

<?php
$definition = array (
    'primary' => false,
    'unique'  => false,
    'foreign' => true,
    'check'   => false,
    'fields' => array (
        'field1name' => array(), // one entry per each field covered
        'field2name' => array(), // by the index
        'field3name' => array(
            'sorting'  => ascending|descending,
            'position' => 3,
        ),
    )
    'references' => array(
        'table' => name,
        'fields' => array(
            'field1name' => array( //one entry per each referenced field
                'position' => 1,
        ),
    )
    'deferrable' => false,
    'initiallydeferred' => false,
    'onupdate' => CASCADE|RESTRICT|SET NULL|SET DEFAULT|NO ACTION,
    'ondelete' => CASCADE|RESTRICT|SET NULL|SET DEFAULT|NO ACTION,
    'match' => SIMPLE|PARTIAL|FULL,
);
?>

Creating primary keys in the events and people tables

<?php
$definition = array (
    'primary' => true,
    'fields' => array (
        'id' => array()
    )
);
$mdb2->createConstraint('events', 'keyname', $definition);
$mdb2->createConstraint('people', 'keyname', $definition);
?>

Note: Some RDBMS may choose to ignore the name of the constraint, for example MySQL will not use the value keyname provided in the method call, but will use PRIMARY when a primary key is created, or [tablename]_ibfk_[n] when a foreign key is created.

In the definition array, you specify which fields will be included in the constraint, using the fields key. The other possible keys in the definition array are primary and unique, which have boolean values.

Let's create another key in the event_participants, where each row has to be unique, meaning that one person can appear only once for a specific event. The definitions array will have both fields in the fields key and the unique key will be set to true.

Creating a unique constraint

<?php
$definition = array (
    'unique' => true,
    'fields' => array (
        'event_id' => array(),
        'person_id' => array(),
    )
);
$mdb2->createConstraint('event_participants', 'unique_participant', $definition);
?>

Creating indices

To create an index, the method createIndex() is used, which has similar signature as createConstraint(), so it takes table name, index name and a definition array. The definition array has one key fields with a value which is another associative array containing fields that will be a part of the index. The fields are defined as arrays with possible keys:

• sorting, with values ascending and descending
• length, integer value

Not all RDBMS will support index sorting or length, in these cases the drivers will ignore them. In the test events database, we can assume that our application will show events occuring in a specific timeframe, so the selects will use the datetime field in WHERE conditions. It will help if there is an index on this field.

Creating an index

<?php
$definition = array(
    'fields' => array(
        'datetime' => array()
    )
);
$mdb2->createIndex('events', 'event_timestamp', $definition);
?>

Creating sequences

The way MDB2 handles sequences is described here. For the events table in our example database, we'll need to have the 'id' auto-incrementing. For this purpose the method nextId() is used to give the next value. nextId() will create the sequence table if it doesn't exist, but we can create if beforehand with createSequence(). It takes a sequence name and an optional start value for the sequence.

Creating sequences

<?php
$mdb2->createSequence('events');
$mdb2->createSequence('people', 1);
?>

In the default MDB2 setup the example above will create two tables: events_seq and people_seq, each with one field called 'sequence', but the field name and the '_seq' postfix are configurable via the MDB2 options seqname_format and seqcol_name.

Altering database tables

Once a database table is created you can rename it or add, remove, rename and alter fields, using the alterTable() method. alterTable() takes three parameters: the table name, the definition of changes and a boolean "check-only" flag. If true, no changes will be made, but only a check if the proposed changes are feasible for the specific table and RDBMS. The second parameter (definition of changes) is an array with these keys:

• name: new name for the table. This is the only key related to the table itself, the other keys contain field definitions
• add: fields to be added
• remove: fields to be removed
• change: fields to be changed
• rename: fields to be renamed

The values for add/remove/change/rename keys have slightly different structures, but they all contain field definitions. You can check the API docs for more information and an examples.

Listing database elements

To see what's in the database, you can use the list*() family of functions, namely:

• listDatabases()
• listFunctions()
• listSequences(): takes optional database name as a parameter. If not supplied, the currently selected database is assumed.
• listTableConstraints(): takes a table name
• listTableFields(): takes a table name
• listTableIndexes(): takes a table name
• listTables(): takes an optional database name
• listTableTriggers(): takes a table name
• listTableViews(): takes a table name
• listUsers()
• listViews(): takes an optional database name

Listing database elements

<?php
$dbs = $mdb2->listDatabases();
print_r($dbs);
/*
prints:
Array
(
    [0] => information_schema
    [1] => events_db
    [2] => mysql
    [3] => test
    [4] => test_db
    [5] => test_db_explain
    [6] => test_mdb2
)
*/

$seqs = $mdb2->listSequences('events_db');
print_r($seqs);
/*
prints:
Array
(
    [0] => events
    [1] => people
)
*/

$cons = $mdb2->listTableConstraints('event_participants');
print_r($cons);
/*
prints:
Array
(
    [0] => unique_participant
)
*/

$fields = $mdb2->listTableFields('events');
print_r($fields);
/*
prints:
Array
(
    [0] => id
    [1] => name
    [2] => datetime
)
*/

$idx = $mdb2->listTableIndexes('events');
print_r($idx);
/*
prints:
Array
(
    [0] => event_timestamp
)
*/

$tables = $mdb2->listTables();
print_r($tables);
/*
prints:
Array
(
    [0] => event_participants
    [1] => events
    [2] => people
)
*/

// currently there is no method to create a view,
// so let's do it "manually"
$sql = "CREATE VIEW names_only AS SELECT name FROM people";
$mdb2->exec($sql);
$sql = "CREATE VIEW last_ten_events AS SELECT * FROM events ORDER BY id DESC LIMIT 0,10";
$mdb2->exec($sql);
// list views
$views = $mdb2->listViews();
print_r($views);
/*
prints:
Array
(
    [0] => last_ten_events
    [1] => names_only
)
*/

/*
Not implemented in the MySQL driver
listTableTriggers()
listTableViews()
listFunctions()
*/
?>

Deleting database elements

For every create*() method as shown above, there is a corresponding drop*() method to delete a database, a table, field, index or constraint. The drop*() methods do not check if the item to be deleted exists, so it's the developer's responsibility to check for PEAR errors.

Drop database elements

<?php
// let's say our normal setup is to die on PEAR errors
PEAR::setErrorHandling(PEAR_ERROR_DIE);

// for the next statements we'll temporarily
// change the error handling
PEAR::pushErrorHandling(PEAR_ERROR_RETURN);

// drop a sequence
$result = $mdb2->dropSequence('nonexisting');
if (PEAR::isError($result)) {
    echo 'The sequence does not exist';
} else {
    echo 'Sequence dropped';
}
// another sequence
$result = $mdb2->dropSequence('people');

// drop a constraint
$mdb2->dropConstraint('events', 'PRIMARY', true);
// note: the third parameter gives a hint
//       that this is a primary key constraint
$mdb2->dropConstraint('event_participants', 'unique_participant');

// drop an index
$mdb2->dropIndex('events', 'event_timestamp');

// drop a table
$mdb2->dropTable('events');

// drop the database already!
$mdb2->dropDatabase('events_db');

// revert to the usual error handling
PEAR::popErrorHandling();
?>

Reverse Module

Reverse Module – Module for managing database structure

Description

The Reverse module is an extension of the Common Module driver, and is made up in the following structure:

• Common driver file that encapsulates an API that can be used to execute the reverse driver queries.
• A set of DBMS specific drivers that are specifically written against the supported database back end (e.g. mysql.php for MySQL, and pgsql.php for PostgreSQL)

Usage

In order to use the MDB2 Reverse drivers, it is necessary to first load the Reverse driver into your MDB2 instance. Let's start a MDB2 instance and set up our connection:

Loading the Reverse module

<?php
// Load up the MDB2 Base class
require 'MDB2.php';

// define a DSN to connect to our database
$dsn = array(
    'phptype' => 'mysql',
    'username' => 'someuser',
    'password' => 'somepass',
    'hostspec' => 'somehost',
    'database' => 'somedb',
);

// create MDB2 instance
// MDB2 has a number of connection options - singleton(), factory() and connect()
$mdb2 = &MDB2::connect($dsn);
if(PEAR::isError($mdb2)) {
    // Die with the user info message on failure for any reason
    die($mdb2->getuserinfo());
}
// We have a valid connection, so let's do some magic!
// Load the Reverse Module using MDB2's loadModule method
$mdb2->loadModule('Reverse', null, true);
?>

In the above example, we have created a valid connection to a database that does exist, using a valid user and password for our database server. We now have access to all of the MDB2 Reverse functionality within our example application. For the purposes of this document, we will set up an example table as such:

Create a table

<?php
// Define a table name that we will be working with
$fields = array(
    'id' => array(
        'type' => 'integer',
        'unsigned' => true,
        'autoincrement' => true,
    ),
    'somename' => array(
        'type' => 'text',
        'length' => 12,
    ),
    'somedate' => array(
        'type' => 'date',
    ),
);
$table = 'sometable';
// Create the table
//Load the Manager Module
$mdb2->loadModule('Manager', null, true);
//create the table with the manager module
$mdb2->manager->createTable($table, $fields);
?>

getTableFieldDefinition() Method

The getTableFieldDefinition() Method exists primarily to get an array that defines a table field. This array can then be used to re-create the table elsewhere, or for any other purpose that may be necessary. Using the MDB2 instance defined above, we will connect to a database, define a table that we want to work with, and reverse engineer a specific field that we are interested in. First, we need to define the table and field that we want to work with; then, it is as easy as a single line of code to get the table definition back as an array of mixed values, then using var_dump to view the results:

Get the table definition

<?php
// Set the field that we would like to reverse engineer (get definition of)
$field = 'somedate';

// Here we get the definition of the table field and return it to a variable
// The return will either be a mixed array on success, or an MDB2 error on failure,
//so we don't need additional checks
$def = $mdb2->getTableFieldDefinition($table, $field);
// finally dumping the variable to screen
var_dump($def);
?>

The return will look something like the following, depending on your field definition:

<?php
array(1) {
  [0] => array(5) {
    ['notnull'] => bool(false)
    ['nativetype'] => string(4) "date"
    ['default'] => NULL
    ['type'] => string(4) "date"
    ['mdb2type'] => string(4) "date"
  }
}
?>

Additional table information

A number of other methods exist to gain information about a selected table. You may use any of the following methods according to the information needed:

• getTableIndexDefinition(): - requires a table name and an index name to return information about the table index as an array
• getTableConstraintDefinition(): requires a table name and a constraint to query against. This will return any constraint definitions that exist on the table. A constraint as is defined here is usually a Primary Key, an Unique Key or a Foreign Key. The returned array has the same structure as the one used in the Manager module with createConstraint().
• getSequenceDefinition(): requires a sequence name. It will return information about the existing table sequence. The method will return an array on success or an MDB2 error on failure.
• getTriggerDefinition(): takes a trigger name as an argument, and will return information as an array on the trigger queried.

tableInfo() Method

This method will return a lot of information regarding a table, and can be used in a number of different ways. The information that it returns will differ slightly across different RDBM's and may give some variance in results. This method can be used to query either a table definition, or a resultset, which makes it great for creating optimized tables. The tableInfo() method allows you to pass a parameter for the mode that you would like to see the results presented as. In order to demonstrate the results more effectively, we will use a series of examples to do some queries and return the results in different modes. NOTE: Either a table OR a resultset can be passed as the first parameter to get information on the table. In these examples, we will be using the table that we defined above.

tableinfo() usage 1

<?php
// Default Mode - NULL
$tableInfo = $mdb2->tableInfo($table, NULL);
var_dump($tableInfo);

//will produce:
array(3) {
  [0] => array(11) {
    ['notnull'] => bool(true)
    ['nativetype'] => string(3) "int"
    ['length'] => int(4)
    ['unsigned'] => int(1)
    ['default'] => string(0) ""
    ['autoincrement'] => bool(true)
    ['type'] => string(3) "int"
    ['mdb2type'] => string(7) "integer"
    ['name'] => string(2) "id"
    ['table'] => string(9) "sometable"
    ['flags'] => string(29) "primary_key not_null unsigned"
  }
  [1] => array(10) {
    ['notnull'] => bool(false)
    ['nativetype'] => string(7) "varchar"
    ['length'] => string(2) "12"
    ['fixed'] => bool(false)
    ['default'] => NULL
    ['type'] => string(7) "varchar"
    ['mdb2type'] => string(4) "text"
    ['name'] => string(8) "somename"
    ['table'] => string(9) "sometable"
    ['flags'] => string(0) ""
  }
  [2] => array(8) {
    ['notnull'] => bool(false)
    ['nativetype'] => string(4) "date"
    ['default'] => NULL
    ['type'] => string(4) "date"
    ['mdb2type'] => string(4) "date"
    ['name'] => string(8) "somedate"
    ['table'] => string(9) "sometable"
    ['flags'] => string(0) ""
  }
}
?>

In the subsequent examples, we will only include the first table field definition, as it effectively illustrates the differences in modes. Now, let's change the mode to MDB2_TABLEINFO_ORDER. In addition to the information found in the default output, a notation of the number of columns is provided by the num_fields element while the order element provides an array with the column names as the keys and their location index number (corresponding to the keys in the default output) as the values.

tableinfo() usage 2

<?php
$tableInfo = $mdb2->tableInfo($table, MDB2_TABLEINFO_ORDER);
var_dump($tableInfo);

array(5) {
  ['num_fields'] => int(3)
  [0] => array(11) {
    ['notnull'] => bool(true)
    ['nativetype'] => string(3) "int"
    ['length'] => int(4)
    ['unsigned'] => int(1)
    ['default'] => string(0) ""
    ['autoincrement'] => bool(true)
    ['type'] => string(3) "int"
    ['mdb2type'] => string(7) "integer"
    ['name'] => string(2) "id"
    ['table'] => string(9) "sometable"
    ['flags'] => string(29) "primary_key not_null unsigned"
  }
  [1] => array(10) {
    ['notnull'] => bool(false)
    ['nativetype'] => string(7) "varchar"
    ['length'] => string(2) "12"
    ['fixed'] => bool(false)
    ['default'] => NULL
    ['type'] => string(7) "varchar"
    ['mdb2type'] => string(4) "text"
    ['name'] => string(8) "somename"
    ['table'] => string(9) "sometable"
    ['flags'] => string(0) ""
  }
  [2] => array(8) {
    ['notnull'] => bool(false)
    ['nativetype'] => string(4) "date"
    ['default'] => NULL
    ['type'] => string(4) "date"
    ['mdb2type'] => string(4) "date"
    ['name'] => string(8) "somedate"
    ['table'] => string(9) "sometable"
    ['flags'] => string(0) ""
  }
  ['order'] => array(3) {
    ['id'] => int(0)
    ['somename'] => int(1)
    ['somedate'] => int(2)
  }
}
?>

Changing the mode to MDB2_TABLEINFO_ORDERTABLE will give us some additional information by adding additional dimensions to the array in which the table names are keys, and the field names are sub keys. This type of query can be useful when querying complex joins, where some field names may be the same.

NOTE: The flags element will contain a space-separated list of additional information about the field. This data is inconsistent between RDBMS's due to the way each RDBMS works:

• primary_key
• unique_key
• multiple_key
• not_null
• unsigned

Most RDBMS's only provide the table and flags elements if the result is a table name. If the portability option is set to MDB2_PORTABILITY_FIX_CASE, the names of tables and fields will be lower- or upper-cased. If the case is set to CASE_UPPER all tables and fields will be uppercased, as is seen in Oracle and Firebird/Interbase, while CASE_LOWER will lower case all field and table names; this is the default setting.

autoPrepare & autoExecute

autoPrepare & autoExecute – Automatically prepare and execute SQL statements

Description

Purpose

autoPrepare() and autoExecute() reduce the need to write boring INSERT, UPDATE, DELETE or SELECT SQL queries which are difficult to maintain when you add a field for instance. It requires the use of the Extended module

Imagine you have a 'user' table with 3 fields (id, name and country). You have to write sql queries like:

INSERT INTO table (id, name, country) VALUES (?, ?, ?)
UPDATE table SET id=?, name=?, country=? WHERE ...

If you add a field ('birthYear' for example), you have to rewrite your queries which is boring and can lead to bugs (if you forget one query for instance).

autoPrepare

With autoPrepare(), you don't have to write your insert, update, delete or select queries. For example:

<?php
// Once you have a valid MDB2 object named $mdb2...
$table_name   = 'user';
$table_fields = array('id', 'name', 'country');
$types = array('integer', 'text', 'text');

$mdb2->loadModule('Extended');
$sth = $mdb2->extended->autoPrepare($table_name, $table_fields,
                        MDB2_AUTOQUERY_INSERT, null, $types);

if (PEAR::isError($sth)) {
    die($sth->getMessage());
}
?>

In this example, autoPrepare() will build the following SQL query:

INSERT INTO user (id, name, country) VALUES (?, ?, ?)

And then, it will call prepare() with it.

To add records, you have to use execute() or executeMultiple() like:

<?php
// ... continuing from the example above...
$table_values = array(1, 'Fabien', 'France');

$res =& $sth->execute($table_values);

if (PEAR::isError($res)) {
    die($res->getMessage());
}
?>

So, you don't have to write any SQL INSERT queries! And it works with UPDATE and DELETE queries too. For flexibility reasons, you have only to write the WHERE clause of the query. For instance:

<?php
// Once you have a valid MDB2 object named $mdb2...
$table_name   = 'user';

$mdb2->loadModule('Extended');
$sth = $mdb2->extended->autoPrepare($table_name, null,
                        MDB2_AUTOQUERY_DELETE, 'id = '.$mdb2->quote(1, 'integer'));

if (PEAR::isError($sth)) {
    die($sth->getMessage());
}

$res =& $sth->execute($table_values);

if (PEAR::isError($res)) {
    die($res->getMessage());
}
?>

autoPrepare() will build the following query:

UPDATE user SET name=?, country=? WHERE id=1

Then, it will call prepare() with it.

Be careful, if you don't specify any WHERE clause, all the records of the table will be updated.

autoExecute

Using autoExecute() is the easiest way to do insert, update, delete or select queries. It is a mix of autoPrepare() and execute().

You only need an associative array (key => value) where keys are fields names and values are corresponding values of these fields. This is only relevant for insert and update queries. For instance:

<?php
// Once you have a valid MDB2 object named $mdb2...
$table_name = 'user';

$fields_values = array(
    'id'      => 1,
    'name'    => 'Fabien',
    'country' => 'France'
);
$types = array('integer', 'text', 'text');

$mdb2->loadModule('Extended');
$affectedRows = $mdb2->extended->autoExecute($table_name, $fields_values,
                        MDB2_AUTOQUERY_INSERT, null, $types);

if (PEAR::isError($affectedRows)) {
    die($affectedRows->getMessage());
}
?>

And that's all! The following query is built and executed:

INSERT INTO user (id, name, country)
  VALUES (1, 'Fabien', 'France')

And it's the same thing for UPDATE queries:

<?php
// Once you have a valid MDB2 object named $mdb2...
$table_name = 'user';

$fields_values = array(
    'name'    => 'Fabien',
    'country' => 'France'
);
$types = array('text', 'text');

$mdb2->loadModule('Extended');
$affectedRows = $mdb2->extended->autoExecute($table_name, $fields_values,
                        MDB2_AUTOQUERY_UPDATE, 'id = '.$mdb2->quote(1, 'integer'), $types);

if (PEAR::isError($affectedRows)) {
    die($affectedRows->getMessage());
}
?>

which prepares and executes the following query:

UPDATE user SET name='Fabien', country='France'
  WHERE id = 1

Be careful, if you don't specify any WHERE statement, all the records of the table will be updated.

Here is an example for a DELETE queries:

<?php
// Once you have a valid MDB2 object named $mdb2...
$table_name = 'user';

$mdb2->loadModule('Extended');
$affectedRows = $mdb2->extended->autoExecute($table_name, null,
                        MDB2_AUTOQUERY_DELETE, 'id = '.$mdb2->quote(1, 'integer'));

if (PEAR::isError($affectedRows)) {
    die($affectedRows->getMessage());
}

?>

which prepares and executes the following query:

DELETE FROM user WHERE id = 1

Finally an example for a SELECT queries:

<?php
// Once you have a valid MDB2 object named $mdb2...
$table_name = 'user';

// if left as a non array all fields of the table will be fetched using '*'
// in that case this variable can be set to true, to autodiscover the types
$result_types = array(
    'name'    => 'text',
    'country' => 'text'
);

$mdb2->loadModule('Extended');
$res = $mdb2->extended->autoExecute($table_name, null,
                        MDB2_AUTOQUERY_SELECT, 'id = '.$mdb2->quote(1, 'integer'),
                        null, true, $result_types);

if (PEAR::isError($res)) {
    die($res->getMessage());
}

$row = $res->fetchRow();

?>

which prepares and executes the following query:

SELECT name, country FROM user WHERE id = 1

You can also use placeholders in the WHERE clause and pass the values like this:

<?php
$id = 1;
$mdb2->autoExecute('table_name', array($id), MDB2_AUTOQUERY_DELETE, 'id = ?');
?>

which prepares the following query and passes the $id parameter on execute:

DELETE FROM table_name WHERE id = ?

The values passed in $data must be literals. Do not submit SQL functions (for example CURDATE()). SQL functions that should be performed at execution time need to be put in the prepared statement.

See

"Intro - Modules"

Portability

Portability – Database portability features

Description

Each database management system (DBMS) has its own behaviour. For example, some databases capitalize field names in their output, some lowercase them, while others leave them alone. These quirks make it difficult to port your scripts over to another server type. PEAR MDB2 strives to overcome these differences so your program can switch between DBMSs without any changes.

You control which portability modes are enabled by using the portability configuration option. Configuration options are set via factory() and setOption().

The portability modes are bitwised, so they can be combined using | and removed using ^. See the examples section below on how to do this.

NB: MDB2 portability modes are meant to change the behaviour of the returned values only, not that of the query itself. For instance, if you created your tables quoting the identifiers, remember to use the quoteIdentifier() method in all your queries or you'll get "not found" or "not exists" errors. Also check for the quote_identifier option, if it's false then quoting won't be applied if the check_option is used.

Portability Mode Constants

• MDB2_PORTABILITY_ALL (default) turn on all portability features. this is the default setting.
• MDB2_PORTABILITY_DELETE_COUNT Force reporting the number of rows deleted. Some DBMSs don't count the number of rows deleted when performing simple DELETE FROM tablename queries. This mode tricks such DBMSs into telling the count by adding WHERE 1=1 to the end of DELETE queries.
• MDB2_PORTABILITY_EMPTY_TO_NULL Convert empty strings values to null in data in and output. Needed because Oracle considers empty strings to be null, while most other DBMSs know the difference between empty and null.
• MDB2_PORTABILITY_ERRORS Makes certain error messages in certain drivers compatible with those from other DBMSs

  Error Code Re-mappings

  Driver	Description	Old Constant	New Constant
  mysql, mysqli	unique and primary key constraints	MDB2_ERROR_ALREADY_EXISTS	MDB2_ERROR_CONSTRAINT
  mysql, mysqli	not-null constraints	MDB2_ERROR_CONSTRAINT	MDB2_ERROR_CONSTRAINT_NOT_NULL

• MDB2_PORTABILITY_FIX_ASSOC_FIELD_NAMES This removes any qualifiers from keys in associative fetches. Some RDBMS, for example SQLite, will default to use the fully qualified name for a column in assoc fetches if it is qualified in a query.
• MDB2_PORTABILITY_FIX_CASE Convert names of tables and fields to lower or upper case in all methods. The case depends on the field_case option that may be set to either CASE_LOWER (default) or CASE_UPPER. NB: the case change is applied to the returned values only, not to the field and table names in the query.
• MDB2_PORTABILITY_NONE Turn off all portability features
• MDB2_PORTABILITY_NUMROWS Enable hack that makes numRows() work in Oracle
• MDB2_PORTABILITY_RTRIM Right trim the data output for all data fetches. This does not apply to drivers for RDBMS that automatically right trim values of fixed length character values, even if they do not right trim value of variable length character values.

Example

Disabling all portability options while connecting

<?php
require_once 'MDB2.php';

$dsn = 'mysql://user:password@host/database'
$options = array(
    'debug'       => 2,
    'portability' => MDB2_PORTABILITY_NONE,
);

$mdb2 =& MDB2::connect($dsn, $options);
if (PEAR::isError($mdb2)) {
    die($mdb2->getMessage());
}
?>

Using setOption() to enable portability for lowercasing and trimming

<?php
// Once you have a valid MDB2 object named $mdb2...
$mdb2->setOption('field_case', CASE_LOWER);
$mdb2->setOption('portability',
        MDB2_PORTABILITY_FIX_CASE | MDB2_PORTABILITY_RTRIM);
?>

Using setOption() to enable all portability options except trimming

<?php
// Once you have a valid MDB2 object named $mdb2...
$mdb2->setOption('portability',
        MDB2_PORTABILITY_ALL ^ MDB2_PORTABILITY_RTRIM);
?>

Sequences

Sequences – Sequences and auto-incrementing

Description

Sequences are a way of offering unique IDs for data rows. If you do most of your work with e.g. MySQL, think of sequences as another way of doing AUTO_INCREMENT.

It's quite simple, first you request an ID, and then you insert that value in the ID field of the new row you're creating. You can have more than one sequence for all your tables, just be sure that you always use the same sequence for any particular table. To get the value of this unique ID use nextID(), if a sequence doesn't exists, it will be created automatically.

The sequence is automatically incremented each time nextID() is called.

Using a sequence

<?php
// Once you have a valid MDB2 object named $mdb2...
$id = $mdb2->nextID('mySequence');
if (PEAR::isError($id)) {
    die($id->getMessage());
}

// Use the ID in your INSERT query
$res =& $mdb2->query("INSERT INTO myTable (id, text) VALUES ($id, 'foo')");
?>

Note

When using PEAR MDB2's sequence methods, we strongly advise using these methods for all procedures, including the creation of the sequences. Do not use PEAR MDB2's methods to access sequences that were created directly in the DBMS.

If you have a compelling reason to ignore this advice, be aware that the $seq_name argument given to all of PEAR MDB2's sequence methods are modified before MDB2 calls the underlying DBMS.

$seq_name is passed through PHP's sprintf() function using the value from the seqname_format option as sprintf()'s format argument. The default seqname_format is %s_seq. So, for example, if you use person_id_sequence as the $seq_name, PEAR MDB2 will change that name to person_id_sequence_seq when querying the DBMS about creating/accessing/updating the sequence.

Also note that the default table layout for sequences emulated in PEAR DB is slightly different in PEAR MDB2. Where PEAR DB calls the column "id" PEAR MDB2 instead calls it "sequence" to make its purpose more clear. For backward compatibility this can be controlled via the seqcol_name option.

The seqname_format and seqcol_name can be modified when connecting or via setOption().

Getting the last inserted ID

If you prefer using AUTO_INCREMENT you can alternatively use the lastInsertID() method to retrieve the last generated value. This method alternatively also supports getting the current ID from a sequence using the format defined in PostgreSQL's SERIAL data type. MDB2 can emulate this behaviour for RDBMS that do not support autoincrement at table creation time when using the createTable() method.

Using lastInsertID()

<?php
// Once you have a valid MDB2 object named $mdb2...
$res =& $mdb2->query("INSERT INTO myTable (id, text) VALUES (NULL, 'foo')");

// optionally pass in a table and fieldname in order to call nextID()
// when autoincrement is not supported
$id = $mdb2->lastInsertID('myTable', 'id');
if (PEAR::isError($id)) {
    die($id->getMessage());
}

?>

Getting the current ID

If you can get the current global value of a sequence using the currID() method.

Using currID()

<?php

// getting the current value of a sequence
$id = $mdb2->currID('myseq');
if (PEAR::isError($id)) {
    die($id->getMessage());
}

?>

Getting around emulation

Finally if you prefer using whatever native feature the RDBMS supports you can use the getBeforeID() and getAfterID() methods from the Extended module. This way MDB2 will automatically use AUTO_INCREMENT if it is natively supported. If not MDB2 will instead use a sequence to get the next id value.

Using getBeforeID()/getAfterID()

<?php
// Once you have a valid MDB2 object named $mdb2...
// $id may either be a quoted integer or php null
$id = $mdb2->getBeforeID('myTable', 'id', true, true);
if (PEAR::isError($id)) {
    die($id->getMessage());
}

$res =& $mdb2->query("INSERT INTO myTable (id, text) VALUES ($id, 'foo')");

// $id is now equivalent to the value in the id field that was inserted
$id = $mdb2->getAfterID($id, 'myTable', 'id');
if (PEAR::isError($id)) {
    die($id->getMessage());
}

?>

See

createSequence(), dropSequence(), listSequences()

FAQ

FAQ – Frequently asked questions

I get an error!

If you get a PEAR_Error (or a MDB2_Error object), try using getMessage() and getUserInfo(). They do often give you more information about the cause of the error.

"MDB2 Error: not found"

If you get this error after creating the MDB2 instance it means that you don't have any MDB2 database driver installed. Since most people use only one database system, it is unnecessary to install 15 driver files.

If SQLite is the database of your choice, do a pear install MDB2_Driver_sqlite or simply pear install MDB2#sqlite and it should work.

Also search for MDB2 drivers in the PEAR package list.

"pear/MDB2_Driver_XXX requires php extension XXX"

If you get this error when trying to install a driver it means that the php.ini loaded in the given installer does not see the "XXX" extension. Either you forgot to install the extension all together or you need to make sure that the extension is actived in all relevant php.ini files. Note that there are usually separate php.ini files for the CLI and your other SAPIs.

If all else failes do pear install -nodeps MDB2_Driver_XXX and it should work.

Two instances fail to keep different databases selected

If you work with more than one database at the same time, you may notice that as soon you connect to the second db the connection to the first one is lost, since the DBMS reuses the last open connection link. To solve the problem, just set the new_link DSN option to TRUE.