Database

Introduction

Skeleton provides a uniform interface to access most main-stream databases. The following is the current list of supported databases:

• ADOdb Lite
• MySQL (mysql and mysqli)
• Postgres
• Sqlite 3
• PDO database drivers

Usage is simple. Pass the configuration to the constructor of the particular adapter you wish to use. For example:

$config = array(
	'host' => 'localhost',
	'user' => 'john',
	'password' => 'Llf3uE42',
	'database' => 'foo'
);
$database = new A_Db_MySQL($config);

Alternatively, you can pass the configuration data to the adapter's config() method later.

Before executing a query, you must connect the database

$database->connect();

Now the database adapter is ready to execute queries. This is done with the query() method.

$result = $database->query('SELECT * FROM users');

The value of $result depends on what kind of query you execute. If it was a INSERT query or another type of query that does not insert data, the result will be an instance of A_Db_Result. This object provides general information about the query, error message, etc.

If instead it was an SELECT, or another command that retreives data, it will contain a database-specific Recordset object. In the case of the MySQL adapter, it will be an instance of A_Db_Recordset_MySQL.

Result Objects

Result objects tell you how the query went. isError() tells you whether or not an error occured, and getErrorMsg() gives you the textual error. numRows() gives the number of rows affected by the query.

Recordset Objects

A Recordset class, as could be expected, is an implementation of the Record Set [martinfowler.com] pattern. It provides an extensive array of interfaces with which to access the row data.

Because Recordset classes implement the Iterator [php.net] interface, the easiest method to get row data is simply to traverse it with a foreach [php.net] construct.

$recordset = $database->query('SELECT * FROM users');
foreach ($recordset as $row) {
	echo $row['username'] . '<br />';
	echo date('r', $row['joined_date']) . '<br />';
}

Recordset also provides the same methods as Result: numRows(), isError(), and getErrorMsg().

If you only wish to get one row, or you wish to loop over the rows manually with a while construct, that can be done with fetchRow().

$recordset = $database->query('SELECT * FROM users LIMIT 1');
$row = $recordset->fetchRow();

Recordset allows you to specify what form you want the row in with setClassName(). The default setting is an associative array. Calling setClassName() without an argument sets it to the default. To get an object, set it to A_Db_Recordset_Base::OBJECT. Or, pass a class name, and the row (as an associative array) will be passed to the constructor of a new instance.

$recordset = $database->query('SELECT * FROM users');

// either this:
$recordset->setClassName();

// or this:
$recordset->setClassName(A_Db_Recordset_Base::OBJECT);

// or this:
$recordset->setClassName('MyUserObject');

// then fetch row(s)
$recordset->fetchRow();

Another convenient feature provided is "gather" mode. When set to on, the Recordset will preserve all rows internally as they are fetched. This internal set is read from (instead of the database) in subsequent traversals. This means that you can scan through the Recordset multiple times without keeping the rows youself.

$recordset = $database->query('SELECT * FROM users');

foreach ($recordset as $row) {
	// loop over rows
}

foreach ($recordset as $row) {
	// loop over rows again
}

$recordset->rewind();
while ($row = $recordset->fetchRow()) {
	// loop over it a different way
	// we can do this all day long
}

$allRows = $recordset->toArray();

The last line uses toArray() to get all gathered rows as a normal array.

More Advanced Database Use

Information about master/slave, config, other methods of adapters, Schema.

Database access patterns

About Datamapper, Tabledatagateway, and Activerecord