Documentation

Database layer

Queries

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

Executing queries

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:

CRUD queries

These are the most tommon queries for doing the most frequent tasks like creating, reading, updating and deleting (CRUD).

SELECT

SELECT queries are used to fetch rows of data from tables in the database.

Fields
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);

UPDATE

FIXME starting here…

DELETE

REPLACE

CREATE TABLE

RENAME TABLE

TRUNCATE

DROP TABLE

TABLE EXISTS

TABLE INFO

ADD FIELD

ALTER FIELD

DROP FIELD

FIELD EXISTS

ADD INDEX

DROP INDEX

INDEX EXISTS

Prepared statements

FIXME

Known compatibility issues

MySQL

  • = 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.