yana

phpDocumentor v 1.4.0

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

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.
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.
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'.
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:
  1.  // select 1 column
  2.  $dbq->setColumns(array('foo'));
  3.  // same as:
  4.  $dbq->setColumn('foo');
  5.  
  6.  // select multiple columns
  7.  $dbq->setColumns(array('foo1''foo2'));
  8.  
  9.  // select multiple columns from different tables
  10.  // 1) join with table2
  11.  $dbq->setJoin('table2');
  12.  // 2) select columns from current table and table2
  13.  $dbq->setColumns(array('foo1''table2.foo2'));
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 MeyerHomepage: www.yanaframework.net