Docs For Class DbQuery

Class DbQuery

Description

Database query builder

This class is a query builder that can be used to build SQL statements.

Note: this class does NOT untaint input data for you. It also does NOT automatically resolve foreign keys. This is mentioned here for security reasons.

Note that there are some special features that you may find usefull:

First, this class is able to detect and resolve table inheritance. Note that this feature is turned on by default. You may turn it of using the function useInheritance(), if you want. Usually this is not necessary.

Second, this class allows you to navigate from one table to another along definded foreign keys and also into columns of type array. This is done by using the function setKey(). These keys form something similar to an address within the database that consists of table.row.column. For example foo.2.bar means select bar from foo where id = 2. If the column is a foreign key, you may add another column inside the referenced table, which itself may also be another foreign key. If the column is an array, you may add an index inside that array, which you want to return. You will find more details on that inside the developer's cookbook in the manual.

since: 2.9 RC1
access: public

Object
   |
   --DbQuery

Method Summary

DbQuery DbQuery (object $database)
bool(false)|string getArrayAddress ()
string getColumn (int $i)
array getColumns ()
array getJoin ([string $table = null])
int getLimit ()
int getOffset ()
array getOrderBy ()
void getParent ([string $table = ""])
void getParentByColumn (string $column)
string getRow ()
bool(false)|string getTable ()
int getType ()
mixed &getValues ()
array getWhere ()
bool isDescending ()
void resetQuery ()
bool setColumn ([string $column = '*'], [string $arrayAddress = null])
bool setColumns ([array $columns = array()])
bool setJoin ([string $table = null], [string $key1 = null], [string $key2 = null])
bool setKey (string $key)
bool setLimit (int $limit)
bool setOffset (int $offset)
bool setOrderBy (string|array $orderBy, [bool $desc = false])
bool setRow (scalar $row)
bool setTable (string $table)
bool setType (int $type)
bool setValues (mixed &$values)
bool setWhere ([string|array $where = null])
string toString ()
bool useInheritance ([bool $state = null])

Direct descendents

Class	Description
DbQueryParser	Database query builder with SQL parser

Methods

create a new instance

DbQuery DbQuery (

object $database

)

List of parameters:

Name	Type	Description
$database	object

Description:

This creates and initializes a new instance of this class.

The argument $database can be an instance of class DbStream or any derived sub-class (e.g. FileDb).

get the currently selected array address

bool(false)|string getArrayAddress ()

Description:

Returns the currently address as a string, or bool(false) if none has been selected yet.

since: 2.9.3
access: public

get the currently selected column

string getColumn (

int $i

)

List of parameters:

Name	Type	Description
$i	int	index of column to get

Description:

Returns the lower-cased name of the currently selected column.

If none has been selected, '*' is returned.

Version info: the argument $i became available in 2.9.6. When multiple columns are selected, use this argument to choose the index of the column you want. Where 0 is the the first column, 1 is the second aso. If the argument $i is not provided, the function returns the first column.

See {link DbQuery::getColumns()} to get a list of all selected columns.

see: DbQuery::getColumns()
access: public
name: DbQuery::getColumn()

get the list of all selected columns

array getColumns ()

Description:

Returns the lower-cased names of the currently selected columns as a numeric array of strings.

If none has been selected, an empty array is returned.

see: DbQuery::getColumn()
since: 2.9.6
access: public
name: DbQuery::getColumns()

get a list of the tables, the query has joined together

array getJoin (

[string $table = null]

)

List of parameters:

Name	Type	Description
$table	string

Description:

If $table is not provided, all tables are returned.

access: public

get the currently selected limit

int getLimit ()

Description:

Note: This setting will not be part of the sql statement produced by {link DbQuery::toString()}. Use the API's $limit and $offset parameter instead when sending the query.

This restriction does not apply if you use {link DbQuery::sendQuery()}.

Note: For security reasons all delete queries will automatically be limited to 1 row at a time.

since: 2.9.3
access: public

get the currently selected offset

int getOffset ()

Description:

Note: This setting will not be part of the sql statement produced by {link DbQuery::toString()}. Use the API's $limit and $offset parameter instead when sending the query.

This restriction does not apply if you use {link DbQuery::sendQuery()}.

Note: For security reasons all delete queries will automatically have an offset of 0.

since: 2.9.3
access: public

get the list of columns the resultset is ordered by

array getOrderBy ()

Description:

Returns a lower-cased list of column names. If none has been set yet, then the list is empty.

access: public

get the parent of a table

void getParent (

[string $table = ""]

)

List of parameters:

Name	Type	Description
$table	string	name of a table

Description:

This function provides information on entity inheritance within the database's data structure.

If $table extends another table, then this will return the name of the parent table as a string.

It will return bool(false) if there is no such parent.

If the argument $table is empty, or not provided, the currently selected table (see {link DbQuery::setTable()}) is used instead.

since: 2.9.6
access: public

get parent table by column name

void getParentByColumn (

string $column

)

List of parameters:

Name	Type	Description
$column	string	name of a column

Description:

This function provides information on entity inheritance within the database's data structure.

If the table extends another table, and the column is inherited one of the parent tables, then this function will return the name of the parent table, where the column was defined or re-defined.

It will return bool(false) if there is no such parent.

since: 2.9.6
access: public

get the currently selected row

string getRow ()

Description:

Returns the lower-cased name of the currently selected column, or bool(false) if none has been selected yet.

If none has been selected, '*' is returned.

access: public

get the currently selected table

bool(false)|string getTable ()

Description:

Returns the lower-cased name of the currently selected table, or bool(false) if none has been selected yet.

access: public

get the currently selected type of statement

int getType ()

Description:

Returns currently selected constant.

access: public

get the list of values

mixed &getValues ()

Description:

If none are available, NULL (not bool(false)!) is returned.

access: public

get the currently set where clause

array getWhere ()

Description:

Returns the current where clause.

access: public

check if resultset is sorted in descending order

bool isDescending ()

Description:

Returns bool(true) if sorted descending and bool(false) otherwise.

access: public

reset query

void resetQuery ()

Description:

Resets all properties of the query object, except for the database connection and the properties "table", "type", "useInheritance".

This function allows you to "recycle" a query object and reuse it without creating another one. This can help to improve the performance of your application.

since: 2.9.4
access: public

set source column

bool setColumn (

[string $column = '*'], [string $arrayAddress = null]

)

List of parameters:

Name	Type	Description
$column	string
$arrayAddress	string

Description:

Returns bool(true) on success and bool(false) on error.

The second argument applies to columns of type 'array' only. In such case you may provide the array key inside the value of the column that you wish to get. If it is a multidimensional array, you may traverse deper dimensions by linking keys with a dot '.' - for example: "foo.bar" gets $result['foo']['bar'].

Note: this will not check if the key that you provided is a valid key or if it really points to a value. If it is not, the resultset will be empty.

An E_USER_WARNING is issued if the second argument is provided but the targeted column is not of type 'array'.

see: DbQuery::setColumns()
access: public
name: DbQuery::setColumn()

set source columns

bool setColumns (

[array $columns = array()]

)

List of parameters:

Name	Type	Description
$columns	array	list of columns

Description:

This sets the list of columns to retrieve, like in SELECT col1, col2, ... colN FROM ...

Note that this applies only to select statements, not insert, update or delete.

Returns bool(true) on success and bool(false) on error.

Note that, depending on the number of columns you wish to retrieve, the datatype of the result may differ.

Getting 1 column from 1 row will just return the value of that cell, e.g. int(1). Getting multiple columns from 1 row will return an array containing the values, e.g. array('col1'=>1, 'col2'=>2, 'col3'=>3).

Getting 1 column from multiple rows will return an one-dimensional array of these values. Getting multiple columns from multiple rows will return a two-dimensional array of rows, where each row is an associative array containing the values of the selected columns.

Examples:

// select 1 column
$dbq->setColumns(array('foo'));
// same as:
$dbq->setColumn('foo');
// select multiple columns
$dbq->setColumns(array('foo1', 'foo2'));
// select multiple columns from different tables
// 1) join with table2
$dbq->setJoin('table2');
// 2) select columns from current table and table2
$dbq->setColumns(array('foo1', 'table2.foo2'));

see: DbQuery::setColumn()
since: 2.9.6
access: public
name: DbQuery::setColumns()

join the resultsets for two tables

bool setJoin (

[string $table = null], [string $key1 = null], [string $key2 = null]

)

List of parameters:

Name	Type	Description
$table	string	name of another table to join the current table with (when omitted will remove all previously set joins)
$key1	string	name of the foreign key in current table (when omitted the API will look up the key in the structure file)
$key2	string	name of the key in foreign table that is referenced (may be omitted if it is the primary key)

Description:

This will join the currently selected table with another.

If $table is not provided, this will reset the list of joined tables. If $key1 is not provided, the function will automatically search for a suitable foreign key, that refers to $table. If $key2 is not provided, the function will automatically look up the primary of $table and use it instead.

Returns bool(true) on success and bool(false) on error.

access: public

resolve key address to determine table, column and row

bool setKey (

string $key

)

List of parameters:

Name	Type	Description
$key	string

Description:

Returns bool(true) on success and bool(false) on error.

access: public

set a limit for this query

bool setLimit (

int $limit

)

List of parameters:

Name	Type	Description
$limit	int

Description:

Note: This setting will not be part of the sql statement produced by {link DbQuery::toString()}. Use the API's $limit and $offset parameter instead when sending the query.

This restriction does not apply if you use {link DbQuery::sendQuery()}.

Note: For security reasons all delete queries will automatically be limited to 1 row at a time.

since: 2.9.3
access: public

set an offset for this query

bool setOffset (

int $offset

)

List of parameters:

Name	Type	Description
$offset	int

Description:

Note: This setting will not be part of the sql statement produced by {link DbQuery::toString()}. Use the API's $limit and $offset parameter instead when sending the query.

This restriction does not apply if you use {link DbQuery::sendQuery()}.

Note: For security reasons all delete queries will automatically have an offset of 0.

since: 2.9.3
access: public

set column to sort the resultset by

bool setOrderBy (

string|array $orderBy, [bool $desc = false]

)

List of parameters:

Name	Type	Description
$orderBy	string\|array	column name / list of column names
$desc	bool	sort descending (true=yes, false=no)

Description:

Returns bool(true) on success and bool(false) on error.

access: public

set source row

bool setRow (

scalar $row

)

List of parameters:

Name	Type	Description
$row	scalar

Description:

Returns bool(true) on success and bool(false) on error.

Note: does not check if row exists.

Currently you may only request 1 row or all. To search for all rows, use the wildcard '*'.

access: public

set source table

bool setTable (

string $table

)

List of parameters:

Name	Type	Description
$table	string

Description:

Returns bool(true) on success and bool(false) on error.

access: public

select the kind of statement

bool setType (

int $type

)

List of parameters:

Name	Type	Description
$type	int

Description:

The argument type can be one of the following:

YANA_DB_UNKNOWN = to reset type
YANA_DB_SELECT = Select column from table ...
YANA_DB_UPDATE = Update table ...
YANA_DB_INSERT = Insert into table ...
YANA_DB_DELETE = Delete from table where ...
YANA_DB_EXISTS = Select 1 from ... where ...
YANA_DB_LENGTH = Select count(*) from ...

Returns bool(true) on success and bool(false) on error.

access: public

set value(s) for current query

bool setValues (

mixed &$values

)

List of parameters:

Name	Type	Description
&$values	mixed

Description:

Returns bool(true) on success and bool(false) on error.

access: public

set where clause (filter)

bool setWhere (

[string|array $where = null]

)

List of parameters:

Name	Type	Description
$where	string\|array

Description:

The syntax is as follows: column1=value1,column2=value2,...,columnN=valueN

The alternative is as follows: array(array(0=>column1,1=>value1,2=>operator1),...) Where "operator" can be one of the following: '=', 'REGEXP', 'LIKE', '<', '>', '!=', '<=', '>='

To unset the where clause, call this function without providing a parameter.

Returns bool(true) on success and bool(false) on error.

access: public

build a SQL-query

string toString ()

Description:

access: public

Redefinition of: Object::toString()

activate / deactivate automatic handling of inheritance

bool useInheritance (

[bool $state = null]

)

List of parameters:

Name	Type	Description
$state	bool	true = on, false = off

Description:

The query builder is able to detect if one table inherits from another and if so, it will auto-join both tables. In this case, selecting a row from the offspring table will also return all entries of the corresponding row in the parent table.

However: while this usually comes in handy, there are some rare situations where you won't want this to be done. E.g. when copying rows from one table to another.

This function allows you to enable or disable this feature. It is enabled by default.

Note: you have to set this before you set the table property. Otherwise it will have no effect.

The function will return the previous state. If no new state is provided, it will just return the current setting without changing anything.

since: 2.9.3
access: public

inherited from base classes

Inherited From Object

Documentation generated on Sat, 03 Jan 2009 22:22:19 +0100 by phpDocumentor 1.4.0

yana author: Thomas Meyer Homepage: www.yanaframework.net