\Anax\DatabaseDatabaseQueryBuilder

Build SQL queries by method calling.

Summary

Methods

Properties

Constants

getSQL()
setSQLDialect()
setTablePrefix()
createTable()
dropTable()
dropTableIfExists()
insert()
update()
deleteFrom()
select()
from()
join()
rightJoin()
leftJoin()
where()
andWhere()
groupBy()
orderBy()
limit()
offset()
mapColumnsWithValues()
configure()
getConfig()
__construct()
setOptions()
setOption()
connect()
executeFetchAll()
executeFetch()
executeFetchClass()
executeFetchInto()
fetchAll()
fetch()
fetchClass()
fetchAllClass()
fetchInto()
execute()
lastInsertId()
rowCount()
fetchObject()
fetchOne()

No public properties found

No constants found

build()
clear()
createException()
pdoException()
statementException()

$config
$options

N/A

createJoin()
isAssoc()
expandParamArray()
setDefaultsFromConfiguration()

$dialect
$sql
$prefix
$start
$from
$join
$set
$where
$groupby
$orderby
$limit
$offset
$pdo
$stmt

N/A

Properties

$config

$config : array<mixed,>

Type

array<mixed,> — store the configuration in this array.

$options

$options : array

Type

array — used when creating the PDO object

$dialect

$dialect :

Type

— current database dialect used

$sql

$sql :

Type

$prefix

$prefix :

Type

$start

$start :

Type

— first line of the sql query

$from

$from :

Type

$join

$join :

Type

$set

$set :

Type

$where

$where :

Type

$groupby

$groupby :

Type

$orderby

$orderby :

Type

$limit

$limit :

Type

$offset

$offset :

Type

$pdo

$pdo :

Type

$stmt

$stmt :

Type

Methods

getSQL()

getSQL() : string

Get SQL.

Returns

string —

with the built sql-query

setSQLDialect()

setSQLDialect(string  $dialect) : self

Set database type/dialect to consider when generating SQL.

Parameters

string

$dialect

representing database type.

Returns

self

setTablePrefix()

setTablePrefix(string  $prefix) : self

Set a table prefix.

Parameters

string

$prefix

to use in front of all tables.

Returns

self

createTable()

createTable(string  $name, array  $columns) : $this

Create a table.

Parameters

string	$name	the table name.
array	$columns	the columns in the table.

Returns

$this

dropTable()

dropTable(string  $name) : $this

Drop a table.

Parameters

string

$name

the table name.

Returns

$this

dropTableIfExists()

dropTableIfExists(string  $name) : $this

Drop a table if it exists.

Parameters

string

$name

the table name.

Returns

$this

insert()

insert(string  $table, array  $columns, array  $values = null) : self

Build a insert-query.

Parameters

string	$table	the table name.
array	$columns	to insert och key=>value with columns and values.
array	$values	to insert or empty if $columns has both columns and values.

Throws

\Anax\Database\BuildException

Returns

self —

for chaining

update()

update(string  $table, array  $columns, array  $values = null) : void

Build an update-query.

Parameters

string	$table	the table name.
array	$columns	to update or key=>value with columns and values.
array	$values	to update or empty if $columns has bot columns and values.

Throws

\Anax\Database\BuildException

deleteFrom()

deleteFrom(string  $table, array  $where = null) : self

Build a delete-query.

Parameters

string	$table	the table name.
array	$where	limit which rows are updated.

Returns

self

select()

select(string  $columns = '*') : $this

Build a select-query.

Parameters

string

$columns

which columns to select.

Returns

$this

from()

from(string  $table) : $this

Build the from part.

Parameters

string

$table

name of table.

Returns

$this

join()

join(string  $table, string  $condition) : $this

Build the inner join part.

Parameters

string	$table	name of table.
string	$condition	to join.

Returns

$this

rightJoin()

rightJoin(string  $table, string  $condition) : $this

Build the right join part.

Parameters

string	$table	name of table.
string	$condition	to join.

Throws

\Anax\Database\BuildException: when dialect does not support.

Returns

$this

leftJoin()

leftJoin(string  $table, string  $condition) : $this

Build the left join part.

Parameters

string	$table	name of table.
string	$condition	to join.

Returns

$this

where()

where(string  $condition) : $this

Build the where part.

Parameters

string

$condition

for building the where part of the query.

Returns

$this

andWhere()

andWhere(string  $condition) : $this

Build the where part with conditions.

Parameters

string

$condition

for building the where part of the query.

Returns

$this

groupBy()

groupBy(string  $condition) : $this

Build the group by part.

Parameters

string

$condition

for building the group by part of the query.

Returns

$this

orderBy()

orderBy(string  $condition) : $this

Build the order by part.

Parameters

string

$condition

for building the where part of the query.

Returns

$this

limit()

limit(string  $condition) : $this

Build the LIMIT by part.

Parameters

string

$condition

for building the LIMIT part of the query.

Returns

$this

offset()

offset(string  $condition) : $this

Build the OFFSET by part.

Parameters

string

$condition

for building the OFFSET part of the query.

Returns

$this

mapColumnsWithValues()

mapColumnsWithValues(array  $columns, array|null  $values) : array

Create a proper column value arrays from incoming $columns and $values.

Parameters

array	$columns
array\|null	$values

Returns

array —

that can be parsed with list($columns, $values)

configure()

configure(array|string  $what) : self

Load and apply configurations.

Parameters

array|string

$what

is an array with key/value config options or a file to be included which returns such an array.

Returns

self

getConfig()

getConfig(string  $key, string  $default = null) : mixed

Helper function for reading values from the configuration and appy default values where configuration item is missing.

Parameters

string	$key	matching a key in the config array.
string	$default	value returned when config item is not found.

Returns

mixed —

or null if key does not exists.

__construct()

__construct(array  $options = array())

Constructor creating a PDO object connecting to a choosen database.

Parameters

array

$options

containing details for connecting to the database.

setOptions()

setOptions(array  $options = array()) : void

Set options and connection details.

Parameters

array

$options

containing details for connecting to the database.

setOption()

setOption(string  $option, mixed  $value) : self

Set a single option.

Parameters

string	$option	which to set.
mixed	$value	to set.

Returns

self

connect()

connect() : self

Connect to the database, allow being called multiple times but ignore when connection is already made.

Throws

\Anax\Database\Exception

Returns

self

executeFetchAll()

executeFetchAll(string  $query, array  $params = array()) : mixed

Execute a select-query with arguments and return the resultset.

Parameters

string	$query	the SQL statement
array	$params	the params array

Returns

mixed —

with resultset

executeFetch()

executeFetch(string  $query, array  $params = array()) : mixed

Execute a select-query with arguments and return the first row in the resultset.

Parameters

string	$query	the SQL statement
array	$params	the params array

Returns

mixed —

with resultset

executeFetchClass()

executeFetchClass(string  $query, array  $params, string  $class) : object

Execute a select-query with arguments and insert the results into a new object of the class.

Parameters

string	$query	the SQL statement
array	$params	the params array
string	$class	the class to create an object of and insert into

Returns

object —

with resultset

executeFetchInto()

executeFetchInto(string  $query, array  $params, string  $object = null) : object

Execute a select-query with arguments and insert the results into an existing object.

Parameters

string	$query	the SQL statement
array	$params	the params array
string	$object	the existing object to insert into

Returns

object —

with resultset

fetchAll()

fetchAll() : array

Fetch all resultset.

Returns

array —

with resultset.

fetch()

fetch() : mixed

Fetch one resultset.

Returns

mixed —

with resultset.

fetchClass()

fetchClass(string  $classname) : object

Fetch one resultset as a new object from this class.

Parameters

string

$classname

which type of object to instantiate.

Returns

object —

with resultset.

fetchAllClass()

fetchAllClass(string  $classname) : array

Fetch full resultset as new objects from this class.

Parameters

string

$classname

which type of object to instantiate.

Returns

array —

with resultset.

fetchInto()

fetchInto(object  $object) : array

Fetch one resultset into an object.

Parameters

object

$object

to insert values into.

Returns

array —

with resultset.

execute()

execute(string|null|array  $query = null, array  $params = array()) : boolean

Execute a SQL-query and ignore the resultset.

Parameters

string\|null\|array	$query	the SQL statement (or $params)
array	$params	the params array

Returns

boolean —

returns TRUE on success or FALSE on failure.

lastInsertId()

lastInsertId() : integer

Return last insert id from autoincremented key on INSERT.

Returns

integer —

as last insert id.

rowCount()

rowCount() : integer

Return rows affected of last INSERT, UPDATE, DELETE

Returns

integer —

as rows affected on last statement

fetchObject()

fetchObject(string  $classname) : object

Fetch one resultset as an object from this class.

OBSOLETE replaced by fetchClass

Parameters

string

$classname

which type of object to instantiate.

Returns

object —

with resultset.

fetchOne()

fetchOne() : mixed

Fetch one resultset. OBSOLETE replace by fetch()

Returns

mixed —

with resultset.

build()

build() : string

Build the SQL query from its parts.

Returns

string —

as SQL query

clear()

clear() : void

Clear all previous sql-code.

createException()

createException(string  $msg, string  $query, array  $param) : void

Throw exception using detailed message.

Parameters

string	$msg	detailed error message from PDO
string	$query	query to execute
array	$param	to match ? in statement

Throws

\Anax\Database\Exception

pdoException()

pdoException(string  $query, array  $param) : void

Throw exception when pdo failed using detailed message.

Parameters

string	$query	query to execute
array	$param	to match ? in statement

Throws

\Anax\Database\Exception

statementException()

statementException(string  $query, array  $param) : void

Throw exception when statement failed using detailed message.

Parameters

string	$query	query to execute
array	$param	to match ? in statement

Throws

\Anax\Database\Exception

createJoin()

createJoin(string  $table, string  $condition, string  $type) : void

Create a inner or outer join.

Parameters

string	$table	name of table.
string	$condition	to join.
string	$type	what type of join to create.

isAssoc()

isAssoc(array  $array) : boolean

Utility to check if array is associative array.

http://stackoverflow.com/questions/173400/php-arrays-a-good-way-to-check-if-an-array-is-associative-or-sequential/4254008#4254008

Parameters

array

$array

input array to check.

Returns

boolean —

true if array is associative array with at least one key, else false.

expandParamArray()

expandParamArray(string  $query, array  $params) : array

Support arrays in params, extract array items and add to $params and insert ? for each entry in the array.

Parameters

string	$query	as the query to prepare.
array	$params	the parameters that may contain arrays.

Returns

array —

with query and params.

setDefaultsFromConfiguration()

setDefaultsFromConfiguration() : void

Update builder settings from active configuration.