Database layer

Queries

To be completely extensible, queries are constructed using special objects.

Query objects can be generated by using the shortcut accessor functions of the adapter object. So, for example, to generate a SELECT query, you can use the adapter's select() function, which returns an instance of Flux_Database_Query_Select.

Once the queries are completely assembled (required attributes are usually parameters of the shortcut function), they can be run using the run() function, which optionally accepts an array of parameters as argument.

These parameters should be used for all user-supplied inputs that are passed to the database - either in conditions (like WHERE, HAVING or ON) or for statements that INSERT or UPDATE data in the database. More on parameters and escaping/security: Prepared statements.

Common properties

All query objects share the following properties and methods.

Properties

Name	Description
`adapter`	The adapter that manages the connection to the database.
`usePrefix`	Whether or not the global table prefix should automatically be applied. Defaults to `true`.

Methods

Name	Parameters	Description
`__construct()`	`Flux_Database_Adapter $adapter`	Constructor. Used to assign a database adapter.
`setTable()`	`string $table`	Set the table that is affected by the query.
`getTable()`		Get the table that is affected by the query. If `usePrefix` is set to true, the global prefix will be prepended.
`run()`	`array $params`	Execute the function with the given parameters, if specified. If this method is called twice on the same object, it will throw an exception (except on query types that support multiple executions, e.g. INSERT). For multi-queries only: If this method is called multiple times, changes to attributes in the meantime will be ignored, as the query has already been compiled.

There are two groups of query objects: those that can be run multiple times with different sets of parameters (e.g. an INSERT query) and those that cannot (like a DROP TABLE statement).

Here is an overview of all the available query types and their required/optional properties:

Name	Example	Description
`fields`	`array('count' => 'COUNT(p.id) AS count', 'subject' => 't.subject')`	An array of columns to be fetched. The key should not be omitted (to allow extensibility) and should be the alias of the column.
`distinct`	`true`	Whether or not duplicate rows should be filtered. Defaults to `false`.
`where`	`'u.id = :id AND o.time < :time'`	A SQL condition string for use in the WHERE clause of the query.
`order`	`array('time' => 'o.time ASC', 'name' => 'u.name ASC')`	An array of columns to sort by. Every element should consist of the column name (including table alias) and a sort order (ASC/DESC). The key should be the column name.
`joins`	Use the shortcut methods.	An array of tables to join. Every element should be an instance of `Flux_Database_Query_Join`. For convenience, use the `innerJoin()` and `leftJoin()` methods for adding new tables to the array.
`group`	`array('g_id' => 'g.g_id', 'g_title' => 'g.g_title')`	An array of columns to group by. Every element should consist of the column name (including table alias), in the correct order. The key should be the column name.
`having`	`'COUNT(word_id) = 1'`	A SQL condition string for use in the HAVING clause of the query. Usually used with aggregate functions (like COUNT). Only applicable if columns to group by have been specified.
`limit`	`25`	The maximum number of rows to be fetched.
`offset`	`50`	The offset at which rows of the result set should start to be returned. This is ignored if no limit is provided.

Shortcut methods

Name	Parameters	Description
`innerJoin()`	`string $key, string $table, string $on = ''`	Add an inner join with the given table, providing a key that can be used to later edit the join. If a join with that key already exists, it will be replaced.
`leftJoin()`	`string $key, string $table, string $on = ''`	Add a left join with the given table, providing a key that can be used to later edit the join. If a join with that key already exists, it will be replaced.

Return value

An array of arrays, that each represent a row of the result set. The keys of those inner arrays are taken from the columns that were selected in the query.

Example

$query = $db->select(array('id' => 'f.id', 'forum_name' => 'f.forum_name'), 'forums AS f');
$query->where = 'f.last_post > :yesterday';
$params = array(':yesterday' => time() - 24*60*60);
 
$active_forums = $query->run($params);

INSERT

INSERT queries allow for creating rows in tables.

Fields

Name	Example	Description
`values`	`array('id' => 1, 'name' => 'Test')`	An array of values to be stored in the database table. The key of each element should be the name of the corresponding column.

Return value

The number of rows that were inserted.

Example

$query = $db->insert(array('username' => 'test', 'registered' => ':now'), 'users');
$params = array(':now' => time());
 
$num_inserted = $query->run($params);

= is not case-sensitive
a = ä etc.

PostgreSQL

SQLite

When setting a column type to SERIAL, multi-column primary keys are not allowed (and simply ignored).
LIKE and = comparisons are only case-insensitive for ASCII fields.