RdbAdmin Module

Db
in package

Application

Database class that is working on PDO.

Usage:

$Pdo = $this->Db->PDO();
$sth = $Pdo->prepare('SELECT * FROM `table`');
$sth->execute();
$result = $sth->fetchAll();
print_r($result);

// close connection
$sth = null;
$this->Db->disconnect();

The advantage of PDO is many but one of them is it is support more drivers.

Properties

$Config : Config
$Container : Container
$currentConnectionKey : mixed
$PDO : array<string|int, mixed>
$Sth : PDOStatement

Methods

__construct() : mixed: DB class constructor.
__destruct() : mixed: Class de-constructor.
alterStructure() : array<string|int, mixed>: Alter DB table structure.
buildPlaceholdersAndValues() : array<string|int, mixed>: Build placeholders and its values.
connect() : PDO|null: Create a connection with connection key in the `db` configuration file.
convertCharsetAndCollation() : bool: Convert character set and collation for table and columns.
currentConnectionKey() : mixed: Get current connection key.
delete() : bool: Delete data from DB table.
disconnect() : mixed: Disconnect from DB on specific connection key.
disconnectAll() : mixed: Disconnect from DB on all connections.
exec() : int: Execute an SQL statement and return the number of affected rows.
insert() : bool: Insert data into DB table.
PDO() : PDO|null: Get PDO object instance on specific connection key.
PDOStatement() : PDOStatement|null: Get PDO statement after called `insert()`, `update()`, `delete()`.
query() : PDOStatement|bool: Executes an SQL statement, returning a result set as a PDOStatement object.
removeSQLComments() : string: Remove any comments from statement.
setCurrentConnectionKey() : bool: Set current connection key to the new key.
tableName() : string: Get table name with prefix based on configuration for specific connection key.
update() : bool: Update data into DB table.

$Config


    protected
        Config
    $Config

$Container


    protected
        Container
    $Container

$currentConnectionKey


    protected
        mixed
    $currentConnectionKey

Store current connection key that is in use.

$PDO


    protected
        array<string|int, mixed>
    $PDO
     = []

The array of PDO connections.

$Sth


    protected
        PDOStatement
    $Sth

__construct()

DB class constructor.


    public
                    __construct(Container $Container) : mixed

You can load this class via framework's Container object named Db. Example: $Db = $Container->get('Db');.

Parameters

$Container : Container: The DI container class.

__destruct()

Class de-constructor.


    public
                    __destruct() : mixed

alterStructure()

Alter DB table structure.


    public
                    alterStructure(string $query) : array<string|int, mixed>

It can be create a table if not exists.
Do not use this method for insertion or update.

Parameters

$query : string

A single SQL statement that should contain CREATE TABLE. Example:

CREATE TABLE my_table (
id bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Identity',
name varchar(100) DEFAULT NULL COMMENT 'The name.',
PRIMARY KEY (id)
) DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Demo table';

Return values

array<string|int, mixed> —

Return associative array where values are strings containing the results of the various update queries.

buildPlaceholdersAndValues()

Build placeholders and its values.


    public
                    buildPlaceholdersAndValues(array<string|int, mixed> $data[, bool $replaceNull = true ][, string $placeholderType = 'named' ]) : array<string|int, mixed>

Example:

$sql = 'SELECT * FROM `table` WHERE 1';
$values = [];
$placeholders = [];

$genWhereValues = $this->Db->buildPlaceholdersAndValues(['field1' => 'value1', 'field2' => 'value2']);
if (isset($genWhereValues['values'])) {
$values = array_merge($values, $genWhereValues['values']);
}
if (isset($genWhereValues['placeholders'])) {
$placeholders = array_merge($placeholders, $genWhereValues['placeholders']);
}
unset($genWhereValues);

$sql .= ' AND ' . implode(' AND ', $placeholders);
unset($placeholders);

$Sth = $this->Db->PDO()->prepare($sql);
foreach ($values as $placeholder => $value) {
$Sth->bindValue($placeholder, $value);
}// endforeach;
unset($placeholder, $sql, $value, $values);

$Sth->execute();

Parameters

$data : array<string|int, mixed>: The associative array where key is field - value pairs.
To make condition IS NULL, set value to null. (depend on $replaceNull argument.)
To make condition IS NOT NULL, set value to IS NOT NULL. (depend on $replaceNull argument.)
If value is \IS NOT NULL then it will be escape as a string. (depend on $replaceNull argument.)
Value can contain custom comparison operator such as '>= 900' will be field >= 900.
Available custom comparison operators <=>, <>, !=, >=, >, <=, <
$replaceNull : bool = true: If set to true then it will replace condition described above that is about IS NULL, IS NOT NULL. Set it to false to use normal placeholders that is better for UPDATE query.
$placeholderType : string = 'named': The type of placeholder. Accepted: 'named', 'positional' (? mark). Default is 'named'.

Return values

array<string|int, mixed> —

Return generated placeholders with values as associative array.

connect()

Create a connection with connection key in the `db` configuration file.


    public
                    connect([mixed $connectionKey = 0 ]) : PDO|null

Parameters

$connectionKey : mixed = 0: The DB config array key (connection key).

Return values

PDO|null —

Return \PDO object if create new instance successfully. Return null if default connection key is not configured.

convertCharsetAndCollation()

Convert character set and collation for table and columns.


    public
                    convertCharsetAndCollation(string $table[, mixed $connectionKey = 0 ][, string|array<string|int, mixed> $convertFrom = ['utf8', 'utf8mb3'] ][, string $tableCharset = 'utf8mb4' ][, string $tableCollate = 'utf8mb4_unicode_ci' ][, array<string|int, mixed> $columns = [] ]) : bool

Parameters

$table : string: The table name to work with.
$connectionKey : mixed = 0: The DB config array key (connection key). Leave it to 0 to use default, set to null to use current connection key.
$convertFrom : string|array<string|int, mixed> = ['utf8', 'utf8mb3']: Current character set(s) to convert from this to new one. Default is `['utf8', 'utf8mb3'].
This will lookup character set based on this and convert to the new one.
If its value is array, it will be looking that if current charset found matched one of them.
$tableCharset : string = 'utf8mb4': The MySQL table CHARSET to be convert to. Default is "utf8mb4".
Do not change this as it might affect with the whole application.
$tableCollate : string = 'utf8mb4_unicode_ci': The MySQL table COLLATE to be convert to. Default is "utf8mb4_unicode_ci".
Do not change this as it might affect with the whole application.
$columns : array<string|int, mixed> = []: The associative array of table columns to convert. Default is empty array.
If this is empty array then it will use the same character set and collation from the table.
The example of array structure: array( 'columnName' => array('convertFrom' => 'utf8', 'collate' => utf8mb4_unicode_ci'), 'anotherColumn' => ... );
The convertFrom sub array key is match first string in column collation.
For example: collation is latin1_general_ci and convertFrom is latin1 then it is matched.

Return values

bool —

Return true if converted, false if there is nothing to convert.

currentConnectionKey()

Get current connection key.


    public
                    currentConnectionKey() : mixed

Return values

mixed —

Return current connection key. It maybe integer, string or it can be null if there is no current connection.

delete()

Delete data from DB table.


    public
                    delete(string $tableName, array<string|int, mixed> $identifier) : bool

Parameters

$tableName : string: The table name. This table name will NOT auto add prefix. The table name will be auto wrap with back-tick (...).
$identifier : array<string|int, mixed>: The identifier for use in WHERE statement. It is associative array where column name is the key and its value is the value pairs.

Return values

bool —

Return PDOStatement::execute(). Return true on success, false for otherwise.

disconnect()

Disconnect from DB on specific connection key.


    public
                    disconnect([mixed $connectionKey = 0 ]) : mixed

Parameters

$connectionKey : mixed = 0: The DB config array key (connection key).

disconnectAll()

Disconnect from DB on all connections.


    public
                    disconnectAll() : mixed

exec()

Execute an SQL statement and return the number of affected rows.


    public
                    exec(string $statement) : int

This is overridden \PDO method to be able to log the query.

Parameters

$statement : string: The SQL statement to prepare and execute.

Return values

int —

Returns the number of rows that were modified or deleted by the SQL statement you issued. If no rows were affected, \PDO::exec() returns 0. This function may return Boolean false, but may also return a non-Boolean value which evaluates to false. Please read the section on Booleans for more information. Use the === operator for testing the return value of this function.

insert()

Insert data into DB table.


    public
                    insert(string $tableName, array<string|int, mixed> $data) : bool

This is just build the INSERT command, prepare, and then execute it.
To get insert ID, you must call $this->Db->PDO()->lastInsertId() manually and must call before commit().
Example:

$data = [
     'name' => 'Sarah',
     'lastname' => 'Connor',
];
$PDO = $this->Db->PDO();
$PDO->beginTransaction();
$insertResult = $this->Db->insert('the_terminator', $data);
if ($insertResult === true) {
     $insertId = $PDO->lastInsertId();
     $PDO->commit();
} else {
     $PDO->rollBack();
     echo 'Insert failed.';
}

Parameters

$tableName : string: The table name. This table name will NOT auto add prefix. The table name will be auto wrap with back-tick (...).
$data : array<string|int, mixed>: The associative array where column name is the key and its value is the value pairs. The column name will be auto wrap with back-tick (...).

Return values

bool —

Return PDOStatement::execute(). Return true on success, false for otherwise.

PDO()

Get PDO object instance on specific connection key.


    public
                    PDO([mixed $connectionKey = null ]) : PDO|null

Parameters

$connectionKey : mixed = null: The DB config array key (connection key). Leave it to null to use current connection key.

Return values

PDO|null —

Return \PDO object if there is already connection. Return null for otherwise.

PDOStatement()

Get PDO statement after called `insert()`, `update()`, `delete()`.


    public
                    PDOStatement() : PDOStatement|null

Return values

PDOStatement|null —

Return \PDOStatement object if exists, null if not exists.

query()

Executes an SQL statement, returning a result set as a PDOStatement object.


    public
                    query(string $statement) : PDOStatement|bool

This is overridden \PDO method to be able to log the query.

Parameters

$statement : string: The SQL statement to prepare and execute. Data inside the query should be properly escaped.

Return values

PDOStatement|bool —

Returns a \PDOStatement object, or false on failure

removeSQLComments()

Remove any comments from statement.


    public
                    removeSQLComments(string $statement) : string

Parameters

$statement : string: The SQL statement. Can be multiple that separate with ;. Example SELECT 1; SELECT 2;.

Return values

string —

Return removed comments from SQL statement.

setCurrentConnectionKey()

Set current connection key to the new key.


    public
                    setCurrentConnectionKey([mixed $connectionKey = 0 ]) : bool

This will not connect to specific key but change the current connection to specific key.
The specific connection must be already connected via connect() method otherwise it will be return false.

Parameters

$connectionKey : mixed = 0: The DB config array key (connection key).

Return values

bool —

Return true if it is already connected and found this connection key in connected data. Return false for otherwise.

tableName()

Get table name with prefix based on configuration for specific connection key.


    public
                    tableName(string $tableName[, mixed $connectionKey = 0 ]) : string

For example: table name is users and prefix is rdb_ then it will be return rdb_users.

Parameters

$tableName : string: The table name without prefix.
$connectionKey : mixed = 0: The DB config array key (connection key).

Return values

string —

Return table name with prefix.

update()

Update data into DB table.


    public
                    update(string $tableName, array<string|int, mixed> $data, array<string|int, mixed> $identifier) : bool

Parameters

$tableName : string: The table name. This table name will NOT auto add prefix. The table name will be auto wrap with back-tick (...).
$data : array<string|int, mixed>: The associative array where column name is the key and its value is the value pairs. The column name will be auto wrap with back-tick (...).
$identifier : array<string|int, mixed>: The identifier for use in WHERE statement. It is associative array where column name is the key and its value is the value pairs.

Return values

bool —

Return PDOStatement::execute(). Return true on success, false for otherwise.

Db in package Application

Tags

Table of Contents

Properties

Methods

Properties

$Config

$Container

$currentConnectionKey

$PDO

$Sth

Methods

__construct()

Parameters

__destruct()

alterStructure()

Parameters

Tags

Return values

buildPlaceholdersAndValues()

Parameters

Tags

Return values

connect()

Parameters

Tags

Return values

convertCharsetAndCollation()

Parameters

Tags

Return values

currentConnectionKey()

Return values

delete()

Parameters

Tags

Return values

disconnect()

Parameters

disconnectAll()

exec()

Parameters

Tags

Return values

insert()

Parameters

Tags

Return values

PDO()

Parameters

Return values

PDOStatement()

Return values

query()

Parameters

Tags

Return values

removeSQLComments()

Parameters

Tags

Return values

setCurrentConnectionKey()

Parameters

Return values

tableName()

Parameters

Return values

update()

Parameters

Tags

Return values

Db
in package

Application