Editor PHP 2.3.2

Database
in package

DataTables Database connection object.

Create a database connection which may then have queries performed upon it.

This is a database abstraction class that can be used on multiple different databases. As a result of this, it might not be suitable to perform complex queries through this interface or vendor specific queries, but everything required for basic database interaction is provided through the abstracted methods.

Table of Contents

__construct()  : mixed
Database instance constructor.
any()  : bool
Determine if there is any data in the table that matches the query condition.
commit()  : $this
Commit a database transaction.
count()  : Number
Get a count from a table.
debug()  : mixed
Get / set debug mode.
delete()  : Result
Perform a delete query on a table.
insert()  : Result
Insert data into a table.
push()  : Result
Update or Insert data. When doing an insert, the where condition is added as a set field.
query()  : Query
Create a query object to build a database query.
quote()  : string
Quote a string for a quote. Note you should generally use a bind!
raw()  : Query
Create a `Query` object that will execute a custom SQL query. This is similar to the `sql` method, but in this case you must call the `exec()` method of the returned `Query` object manually. This can be useful if you wish to bind parameters using the query `bind` method to ensure data is properly escaped.
resource()  : PDO
Get the database resource connector. This is typically a PDO object.
rollback()  : $this
Rollback the database state to the start of the transaction.
select()  : Result
Select data from a table.
selectDistinct()  : Result
Select distinct data from a table.
sql()  : Result
Execute an raw SQL query - i.e. give the method your own SQL, rather than having the Database classes building it for you.
transaction()  : $this
Start a new database transaction.
type()  : mixed
Get the database type (e.g. Postgres, Mysql, etc).
update()  : Result
Update data.

Methods

__construct()

Database instance constructor.

public __construct(array<string, string|\PDO> $opts) : mixed
Parameters
$opts : array<string, string|\PDO>

Array of connection parameters for the database:

[
"user" => "", // User name
"pass" => "", // Password
"host" => "", // Host name
"port" => "", // Port
"db"   => "", // Database name
"type" => ""  // Datable type: "Mysql", "Postgres" or "Sqlite"
]
Return values
mixed —

any()

Determine if there is any data in the table that matches the query condition.

public any(string|array<string|int, string> $table[, array<string|int, mixed> $where = null ]) : bool
Parameters
$table : string|array<string|int, string>

Table name(s) to act upon.

$where : array<string|int, mixed> = null

Where condition for what to select - see Query->where().

Return values
bool —

Boolean flag - true if there were rows

commit()

Commit a database transaction.

public commit() : $this

Use with Database->transaction() and Database->rollback().

Return values
$this —

count()

Get a count from a table.

public count(string|array<string|int, string> $table[, string $field = 'id' ][, array<string|int, mixed> $where = null ]) : Number
Parameters
$table : string|array<string|int, string>

Table name(s) to act upon.

$field : string = 'id'

Primary key field name

$where : array<string|int, mixed> = null

Where condition for what to select - see Query->where().

Return values
Number —

debug()

Get / set debug mode.

public debug([bool $set = null ]) : mixed
Parameters
$set : bool = null

Debug mode state. If not given, then used as a getter.

Return values
mixed —

delete()

Perform a delete query on a table.

public delete(string|array<string|int, string> $table[, array<string|int, mixed> $where = null ]) : Result

This is a short cut method that creates an update query and then uses the query('delete'), table, where and exec methods of the query.

Parameters
$table : string|array<string|int, string>

Table name(s) to act upon.

$where : array<string|int, mixed> = null

Where condition for what to delete - see Query->where().

Return values
Result —

insert()

Insert data into a table.

public insert(string|array<string|int, string> $table, array<string|int, mixed> $set[, string|array<string|int, mixed> $pkey = '' ]) : Result

This is a short cut method that creates an update query and then uses the query('insert'), table, set and exec methods of the query.

Parameters
$table : string|array<string|int, string>

Table name(s) to act upon.

$set : array<string|int, mixed>

Field names and values to set - see Query->set().

$pkey : string|array<string|int, mixed> = ''

Primary key column names (this is an array for forwards compt, although only the first item in the array is actually used). This doesn't need to be set, but it must be if you want to use the Result->insertId() method.

Return values
Result —

push()

Update or Insert data. When doing an insert, the where condition is added as a set field.

public push(string|array<string|int, string> $table, array<string|int, mixed> $set[, array<string|int, mixed> $where = null ][, string|array<string|int, mixed> $pkey = '' ]) : Result
Parameters
$table : string|array<string|int, string>

Table name(s) to act upon.

$set : array<string|int, mixed>

Field names and values to set - see Query->set().

$where : array<string|int, mixed> = null

Where condition for what to update - see Query->where().

$pkey : string|array<string|int, mixed> = ''

Primary key column names (this is an array for forwards compt, although only the first item in the array is actually used). This doesn't need to be set, but it must be if you want to use the Result->insertId() method. Only used if an insert is performed.

Return values
Result —

query()

Create a query object to build a database query.

public query(string $type[, string|array<string|int, string> $table = null ]) : Query
Parameters
$type : string

Query type - select, insert, update or delete.

$table : string|array<string|int, string> = null

Table name(s) to act upon.

Return values
Query —

quote()

Quote a string for a quote. Note you should generally use a bind!

public quote(string $val[, int $type = PDO::PARAM_STR ]) : string
Parameters
$val : string

Value to quote

$type : int = PDO::PARAM_STR

Value type

Return values
string —

raw()

Create a `Query` object that will execute a custom SQL query. This is similar to the `sql` method, but in this case you must call the `exec()` method of the returned `Query` object manually. This can be useful if you wish to bind parameters using the query `bind` method to ensure data is properly escaped.

public raw() : Query
Tags
example

Safely escape user input

$db
  ->raw()
  ->bind( ':date', $_POST['date'] )
  ->exec( 'SELECT * FROM staff where date < :date' );
Return values
Query —

resource()

Get the database resource connector. This is typically a PDO object.

public resource() : PDO
Return values
PDO —

PDO connection resource (driver dependent)

rollback()

Rollback the database state to the start of the transaction.

public rollback() : $this

Use with Database->transaction() and Database->commit().

Return values
$this —

select()

Select data from a table.

public select(string|array<string|int, string> $table[, string|array<string|int, mixed> $field = '*' ][, array<string|int, mixed> $where = null ][, array<string|int, mixed> $orderBy = null ]) : Result

This is a short cut method that creates an update query and then uses the query('select'), table, get, where and exec methods of the query.

Parameters
$table : string|array<string|int, string>

Table name(s) to act upon.

$field : string|array<string|int, mixed> = '*'

Fields to get from the table(s) - see Query->get().

$where : array<string|int, mixed> = null

Where condition for what to select - see Query->where().

$orderBy : array<string|int, mixed> = null

Order condition - see Query->order().

Return values
Result —

selectDistinct()

Select distinct data from a table.

public selectDistinct(string|array<string|int, string> $table[, string|array<string|int, mixed> $field = '*' ][, array<string|int, mixed> $where = null ][, array<string|int, mixed> $orderBy = null ]) : Result

This is a short cut method that creates an update query and then uses the query('select'), distinct ,table, get, where and exec methods of the query.

Parameters
$table : string|array<string|int, string>

Table name(s) to act upon.

$field : string|array<string|int, mixed> = '*'

Fields to get from the table(s) - see Query->get().

$where : array<string|int, mixed> = null

Where condition for what to select - see Query->where().

$orderBy : array<string|int, mixed> = null

Order condition - see Query->order().

Return values
Result —

sql()

Execute an raw SQL query - i.e. give the method your own SQL, rather than having the Database classes building it for you.

public sql(string $sql) : Result

This method will execute the given SQL immediately. Use the raw() method if you need the ability to add bound parameters.

Parameters
$sql : string

SQL string to execute (only if _type is 'raw').

Tags
example

Basic select

$result = $db->sql( 'SELECT * FROM myTable;' );
example

Set the character set of the connection

$db->sql("SET character_set_client=utf8");
$db->sql("SET character_set_connection=utf8");
$db->sql("SET character_set_results=utf8");
Return values
Result —

transaction()

Start a new database transaction.

public transaction() : $this

Use with Database->commit() and Database->rollback().

Return values
$this —

type()

Get the database type (e.g. Postgres, Mysql, etc).

public type() : mixed
Return values
mixed —

update()

Update data.

public update(string|array<string|int, string> $table[, array<string|int, mixed> $set = null ][, array<string|int, mixed> $where = null ]) : Result

This is a short cut method that creates an update query and then uses the query('update'), table, set, where and exec methods of the query.

Parameters
$table : string|array<string|int, string>

Table name(s) to act upon.

$set : array<string|int, mixed> = null

Field names and values to set - see Query->set().

$where : array<string|int, mixed> = null

Where condition for what to update - see Query->where().

Return values
Result — 

        

        
    




    

    

                

    

        

            
Search results