Simple database wrapper. More...

Inheritance diagram for Db:

Public Member Functions
	__construct ($dataSource=null)
	Establish database connection. More...

	connect ($dataSource)
	Establish database connection. More...

	close ()
	Close database connection.

	codePage ($cp)
	Set connection character set.

	seek ($res, $rowno)
	Seek resource $res to $rowno.

	setLimit ($numrows, $offset=0)
	The portable way to perform limit. More...

	query ($_sql, $param=null)
	Perform database query with parameters - return result resource. More...

	field ($dsstr)
	Perform SELECT query and return value of one database field. More...

	select ($dsstr)
	Perform SELECT query and return one row of result as assoc-array. More...

	selectAll ($dsstr)
	Perform SELECT query and return ALL rows of result as assoc-array. More...

	selectOne ($dsstr)
	Perform SELECT query, return first column of result as indexed array. More...

	selectPair ($dsstr)
	Perform SELECT query, return first and second column as associative array (lookup query). More...

	insert ($tab, $data)
	Perform INSERT query, return inserted ID. More...

	insertAll ($tab, array $data)
	Insert multiple rows.

	insertUpdate ($tab, array $data, array $key=['ID'])
	Update or insert when key does not exists. More...

	runDump ($fileName, $skipErrors=false)
	Run database dump file $fileName. More...

	replace ($tab, $data)
	Perform REPLACE query, return inserted ID. More...

	update ($tab, $data, $cond)
	Perform UPDATE query. More...

	delete ($tab, $cond)
	Perform DELETE query. More...

	count ($dsstr=null)
	Return number of rows in query result / table. More...

	exists ($dsstr)
	Return true if some row which is satisfying condition exists. More...

	fetch ($res=null, $fmt='a')
	Fetch one row from query result. More...

	fetchAll ($res=null, $fmt='a')
	Fetch ALL rows from query result. More...

	beginTransaction ()
	Begins a transaction.

	commit ()
	Commits statements in a transaction.

	rollback ()
	Rollback changes in a transaction.

	dbName ()
	Return current database name.

	columns ($table)
	Return columns metadata (name,size,type,nullable,default) of table $table as associative array. More...

	indexes ($table)
	Return indexes for table $table. More...

	export ($dba, $fmt='html')
	Export query result to format $fmt. More...

	getLookup ($lkpName)
	Return lookup table as array. More...

	escape ($str, $type='string')
	Escapes string for use in database query. More...

	setParams ($sql, $params)
	Place $params into $sql and return result string. More...

Public Member Functions inherited from BaseObject
	loadDefaults ($className=null)
	Load default parameters of class $className into object instance.

	setProperties (array $defaults)
	Set public properties of object from the array. More...

	toArray ()
	Convert object to array. More...

Data Fields
	$disabled = false
	If enabled, no query is executed.

	$lastQuery
	SQL of last executed query.

	$forceReconnect = true
	Create new connection even if connection with same params exists.

	$slowQueryLog = 1.0
	Log queries slower than 1s - 0: do not log anything.

	$drv
	var AbstractDriver Database driver

Data Fields inherited from BaseObject
	$serviceLocator
	var function() Return service object when requested with service().

Protected Member Functions
	getSelectSql ($dsstr, $args)
	Helper for building SELECT sql. More...

	getWhereSql ($cond, $args)
	Helper for building WHERE clausule of sql query. More...

	isSql ($dsstr, $type='')
	True if $dsstr is SQL string (of type $type)

Protected Member Functions inherited from BaseObject
	service ($service, $default=null)
	Try acquire $service and load it into property $this->$service. More...

Protected Attributes
	$app
	var App

Additional Inherited Members
Static Public Member Functions inherited from BaseObject
static	defaults ()
	Set or retireve default parameters of the object. More...

Detailed Description

Simple database wrapper.

Features:

Simplify common queries with select(), insert(), update(), count() etc.
Access to metadata: index(), columns()
Mass functions: selectAll(), insertAll(), runDump()
Dynamic SQL for building parametrized queries. See Dynamic SQL
SQL injection protection
Drivers for different database engines (mysql, pgsql, sqlite, pdo_mysql and pdo_pgsql)

Constructor & Destructor Documentation

◆ __construct()

__construct ( $dataSource = null )

Establish database connection.

Parameters

string | array $dataSource Format: 'driver://user:passw@host/database'

See also: connect()

Member Function Documentation

◆ columns()

columns ( $table )

Return columns metadata (name,size,type,nullable,default) of table $table as associative array.

Returns: array $columns

◆ connect()

connect ( $dataSource )

Establish database connection.

Format of $datasource can be:

'driver://user:passw@host/database'
'driver://user:passw@host/database?parameters'
array with keys: driver, host, dbname, user, passw

Parameters

string | array $dataSource

◆ count()

count ( $dsstr = null )

Return number of rows in query result / table.

Without parameters it returns number of rows in last query. For using with parameters see examples.

Ex:

count('TABLE') //return number of rows in table
count($res) //return no. of rows in query result $res
count() //return no. of rows in last query
count('PERSONS', 'MONEY>100') //perform "select count(*) from PERSONS where MONEY>100"
count('select ID from T where F>10') //return no. of rows of the query

Parameters

string	$dsstr	Datasource string
string	$cond	where condition

Returns: int $num Number of rows

◆ delete()

delete	(	$tab,
		$cond
	)

Perform DELETE query.

See Examples of using database functions parameters.

Parameters

string	$tab	Table name
string	$cond	where condition (required)

Returns: bool $success

See also: insert()

◆ escape()

escape	(	$str,
		$type = `'string'`
	)

Escapes string for use in database query.

Note: If you use {param} parameters in sql, it will be escaped automatically.

Parameters

string $str String (parameter) used in sql query

Returns: string $str escaped string

◆ exists()

exists ( $dsstr )

Return true if some row which is satisfying condition exists.

See select() for possible parameters. See Examples of using database functions parameters.

Ex: if($db->exists('PERSONS', 'MONEY>1000')) ...

Parameters

string	$dsstr	Datasource string
string	$cond	where condition

Returns: bool $found

See also: select()

◆ export()

export	(	$dba,
		$fmt = `'html'`
	)

Export query result to format $fmt.

Only html is supported now.

Parameters

string \| resource	$dba	Resource or $dsstr string - see select() for details
string	$fmt	export format ('html' or 'html/vertical')

Returns: string Query result in format $fmt

See also: select()

◆ fetch()

fetch	(	$res = `null`,
		$fmt = `'a'`
	)

Fetch one row from query result.

If result is ommited, it uses last query.

Parameters

resource	$res	Query result resource
string	$fmt	format of result a - assoc, o - object, r - row, ar - array, f - field

Returns: array|object $row result row

◆ fetchAll()

fetchAll	(	$res = `null`,
		$fmt = `'a'`
	)

Fetch ALL rows from query result.

Parameters

resource	$res	Query result resource
string	$fmt	format of result

Returns: array $rows = array($row_0, $row_1, $row_2, ...)

See also: fetch()

◆ field()

field ( $dsstr )

Perform SELECT query and return value of one database field.

This function is shortcut for SQL SELECT command. Parameters consist from two parts: datasource string and condition. Datasource can be:

tablename
tablename:fieldlist
sql-query

You can use Dynamic SQL. See Examples of using database functions parameters

Parameters

string	$dsstr	datasource string.
string	$cond	WHERE condition.

Returns: value $field

◆ getLookup()

getLookup ( $lkpName )

Return lookup table as array.

Parameters

string $lkpname Lookup table name

Returns: array $items Lookup table items

◆ getSelectSql()

getSelectSql	(	$dsstr,
		$args
	)

protected

Helper for building SELECT sql.

See also: select()

◆ getWhereSql()

getWhereSql	(	$cond,
		$args
	)

protected

Helper for building WHERE clausule of sql query.

See also: update()

◆ indexes()

indexes ( $table )

Return indexes for table $table.

Returns: array $indexes

◆ insert()

insert	(	$tab,
		$data
	)

Perform INSERT query, return inserted ID.

See Examples of using database functions parameters.

Parameters

string	$tab	Table name
array \| string	$data	assoc-array of 'FIELDNAME' => 'FIELDVALUE' pairs.

Returns: int $inserted_id

◆ insertUpdate()

insertUpdate	(		$tab,
		array	$data,
		array	$key = `['ID']`
	)

Update or insert when key does not exists.

Parameters

string	$tab	Table name
array	$data	assoc-array of 'FIELDNAME' => 'FIELDVALUE' pairs.
array	$key	Found record for update with $key fields

Returns: int $inserted_id|false

◆ query()

query	(	$_sql,
		$param = `null`
	)

Perform database query with parameters - return result resource.

You can use Dynamic SQL. See Examples of using database functions parameters

Parameters

string	$_sql	sql-query
array	$param	query parameters.

Returns: resource $result

◆ replace()

replace	(	$tab,
		$data
	)

Perform REPLACE query, return inserted ID.

See Examples of using database functions parameters.

Parameters

string	$tab	Table name
array \| string	$data	assoc-array of 'FIELDNAME' => 'FIELDVALUE' pairs.

Returns: int $inserted_id

◆ runDump()

runDump	(	$fileName,
		$skipErrors = `false`
	)

Run database dump file $fileName.

Returns: int Number of executed queries.

◆ select()

select ( $dsstr )

Perform SELECT query and return one row of result as assoc-array.

This function is shortcut for SQL SELECT command. Parameters consist from two parts: datasource string and condition. Datasource can be:

tablename
tablename:fieldlist
sql-query

You can use Dynamic SQL. See Examples of using database functions parameters

Parameters

string	$dsstr	datasource string.
string	$cond	WHERE condition.

Returns: array $row

◆ selectAll()

selectAll ( $dsstr )

Perform SELECT query and return ALL rows of result as assoc-array.

This function is shortcut for SQL SELECT command. Parameters consist from two parts: datasource string and condition. Datasource can be:

tablename
tablename:fieldlist
sql-query

You can use Dynamic SQL. See Examples of using database functions parameters

Parameters

string	$dsstr	datasource string.
string	$cond	WHERE condition.

Returns: array $result = array ($row0_array, $row1_array, $row2_array, ...)

◆ selectOne()

selectOne ( $dsstr )

Perform SELECT query, return first column of result as indexed array.

This function is shortcut for SQL SELECT command. Parameters consist from two parts: datasource string and condition. Datasource can be:

tablename
tablename:fieldlist
sql-query

You can use Dynamic SQL. See Examples of using database functions parameters

Parameters

string	$dsstr	datasource string.
string	$cond	WHERE condition.

Returns: array $column. Ex: selectOne('PERSONS:NAME') will return array('John', 'Jack', ...)

◆ selectPair()

selectPair ( $dsstr )

Perform SELECT query, return first and second column as associative array (lookup query).

This function is shortcut for SQL SELECT command. Parameters consist from two parts: datasource string and condition. Datasource can be:

tablename
tablename:fieldlist
sql-query

You can use Dynamic SQL. See Examples of using database functions parameters

Parameters

string	$dsstr	datasource string.
string	$cond	WHERE condition.

Returns: array $lookup. Ex: selectPair('PERSONS:NAME,MONEY') will return array('John' => 12000, 'Jack' => 200, ...)

◆ setLimit()

setLimit	(	$numrows,
		$offset = `0`
	)

The portable way to perform limit.

Next query result will be limited according setlimit parameters.

Parameters

int	$numrows	number of rows in result
int	$offset	offset from which result started

◆ setParams()

setParams	(	$sql,
		$params
	)

Place $params into $sql and return result string.

See query() for details about $sql and $param format.

Parameters

string	$sql	SQL-query string
array	$params	Parameters

Returns: string $sql

See also: query()

◆ update()

update	(	$tab,
		$data,
		$cond
	)

Perform UPDATE query.

See Examples of using database functions parameters.

Parameters

string	$tab	Table name
array \| string	$data	array of values.
string	$cond	where condition (required).

Returns: bool $success.

See also: insert()

The documentation for this class was generated from the following file:

Db.php

Public Member Functions

Data Fields

Protected Member Functions

Protected Attributes

Additional Inherited Members

Detailed Description

Constructor & Destructor Documentation

◆ __construct()

Member Function Documentation

◆ columns()

◆ connect()

◆ count()

◆ delete()

◆ escape()

◆ exists()

◆ export()

◆ fetch()

◆ fetchAll()

◆ field()

◆ getLookup()

◆ getSelectSql()

◆ getWhereSql()

◆ indexes()

◆ insert()

◆ insertUpdate()

◆ query()

◆ replace()

◆ runDump()

◆ select()

◆ selectAll()

◆ selectOne()

◆ selectPair()

◆ setLimit()

◆ setParams()

◆ update()