Documentation

CMySqlDB extends CDBMS
in package

dressapi

core

dbms

Extends the abstract CDBMS class to perform all specific MySQL-related basic operations.

It is a complete class and could be used directly but if you want to change the DBMS you will have to modify all the classes that use it instead of in one place (CSqlCompser). Use the CDB derived class instead.

ALERT


    public
        mixed
    ALERT
    = 1

CRITICAL


    public
        mixed
    CRITICAL
    = 2

DB_ASSOC


    public
        mixed
    DB_ASSOC
    = MYSQLI_ASSOC

DB_BOTH


    public
        mixed
    DB_BOTH
    = MYSQLI_BOTH

DB_NUM


    public
        mixed
    DB_NUM
    = MYSQLI_NUM

DEBUG


    public
        mixed
    DEBUG
    = 7

EMERGENCY


    public
        mixed
    EMERGENCY
    = 0

ERROR


    public
        mixed
    ERROR
    = 3

INFO


    public
        mixed
    INFO
    = 6

NOTICE


    public
        mixed
    NOTICE
    = 5

WARNING


    public
        mixed
    WARNING
    = 4

$autocommit


    protected
    static    bool
    $autocommit
     = true

$database_name


    protected
    static    string
    $database_name

$dbkey


    protected
    static    string|null
    $dbkey
     = null

$dbname


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

$dbpassword


    protected
    static    string
    $dbpassword

$dbusername


    protected
    static    string
    $dbusername

$handle


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

$hostname


    protected
    static    string
    $hostname

$last_id


    protected
        int
    $last_id
     = 0

$port


    protected
    static    int
    $port

$results


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

$stmt


    protected
    static    mixed
    $stmt
     = null

$table


    protected
        string
    $table
     = ''

__construct()

Constructor


    public
                    __construct([string $table = '' ]) : mixed

Parameters

$table : string = '': name of the default db table (it can be emtpy)

Return values

mixed —

__destruct()

Destroys any allocated resources


    public
                    __destruct() : mixed

Return values

mixed —

alert()

Action must be taken immediately.


    public
            static        alert(string $message[, array<string|int, mixed> $context = [] ]) : void

Example: Entire website down, database unavailable, etc. This should trigger the SMS alerts and wake you up.

Parameters

$message : string
$context : array<string|int, mixed> = []

Return values

void —

beginTransaction()

Start DB transaction It is recommended to use transitions in case of multiple SQL statements, which, if at least one fails, the entire set of statements can be canceled


    public
                    beginTransaction() : void

NOTE: before use benginTransaction() you must be sure that the AutoCommit is false with setAutoCommit(false)

Return values

void —

commit()

Validate SQL operations performed after calling beginTransaction()


    public
                    commit() : void

Return values

void —

connect()

Set a parameter for a DB connection


    public
            static        connect(string $dbname, string $username, string $password[, string $hostname = 'localhost' ][, int|string $port = DB_PORT ]) : void

Parameters

$dbname : string: name of the database inside the DBMS
$username : string: name of the user connecting to the DB
$password : string: DB password
$hostname : string = 'localhost': name or IP address of the server (if it is on the same computer it is usually in "localhost")
$port : int|string = DB_PORT: communication port: if left empty, it takes the default value (3306)

Return values

void —

critical()

Critical conditions.


    public
            static        critical(string $message[, array<string|int, mixed> $context = [] ]) : void

Example: Application component unavailable, unexpected exception.

Parameters

$message : string
$context : array<string|int, mixed> = []

Return values

void —

debug()

Detailed debug information.


    public
            static        debug(string $message[, array<string|int, mixed> $context = [] ]) : void

Parameters

$message : string
$context : array<string|int, mixed> = []

Return values

void —

decrypt()

Decrypt a value from DB


    public
                    decrypt(string $val[, string $name = '' ]) : string

NOTE: if $val==$name decrypts the DB field name

Parameters

$val : string: value or name of the field to decrypt
$name : string = '': name of the field that is returned

Return values

string —

DB instruction to decrypt $ val

deleteRecord()

Delete records from a DB table


    public
                    deleteRecord(string $table, string $conditions, array<string|int, string> $conditions_values, array<string|int, mixed> $types) : bool

Parameters

$table : string: name of the database table on which to delete one or more records
$conditions : string: conditions of the where with any "?" in the parameters
$conditions_values : array<string|int, string>: values present in the WHERE condition that will have to replace the "?"
$types : array<string|int, mixed>

Return values

bool —

result of the operation, true if successful

disconnect()

Releases the resource to connect to the DB


    public
            static        disconnect([string|null $dbkey = null ]) : void

Parameters

$dbkey : string|null = null: key of db to disconnect (optional), if not send or is null keep the current db. Normally you have only one DB, in this case you can ignore $dbkey parameter

Return values

void —

disconnectAll()

Releases all the resources of the previously opened DBs


    public
            static        disconnectAll() : void

Return values

void —

emergency()

System is unusable.


    public
            static        emergency(string $message[, array<string|int, mixed> $context = [] ]) : void

Parameters

$message : string
$context : array<string|int, mixed> = []

Return values

void —

encrypt()

Reversibly encrypts a value to DB


    public
                    encrypt(string $val[, string $name = '' ]) : string

NOTE: if $val==$name encrypts the name of the DB field

Parameters

$val : string: value or name of the field to be encrypted
$name : string = '': name of the field that is returned

Return values

string —

DB instruction to encrypt $val

error()

Runtime errors that do not require immediate action but should typically be logged and monitored.


    public
            static        error(string $message[, array<string|int, mixed> $context = [] ]) : void

Parameters

$message : string
$context : array<string|int, mixed> = []

Return values

void —

execute()

Execution of SQL with binding


    public
                    execute([array<string|int, mixed> $bind_param_values = null ][, array<string|int, mixed> $bind_param_types = null ]) : void

Parameters

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

Return values

void —

ExistsTable()

Check if the table passed as a parameter exists (including the prefix)


    public
                    ExistsTable(string $table) : bool

Parameters

$table : string: name of the table

Return values

bool —

true if it exists, false if it does not exist

getAffectedRows()

Returns how many rows were affected by the last operation performed


    public
                    getAffectedRows() : int

Return values

int —

total of rows affected by the last operation

getAllTables()

Returns an array containing the table name as an index and an array containing the name and type of the columns as a value


    public
                    getAllTables(array<string|int, string> &$db_tables) : void

Parameters

$db_tables : array<string|int, string>: list of tables to be returned

Return values

void —

getArrayByName()

After executing a query it returns an array whose row index has the column value "$ name" and the rest of the fields are the cell value:


    public
                    getArrayByName([string|int $name = 'name' ][, string|null $fieldname_value = null ]) : array<string|int, mixed>|null

Parameters

$name : string|int = 'name': name or position of the select column that must have as index (default is' 'name')
$fieldname_value : string|null = null: name of the field to fetch the value from, if null returns all data in an array

Return values

array<string|int, mixed>|null —

array containing the list name => value in which value can be a scalar or an associative array if it must contain more values or it returns null if there are no elements or an empty array if the field name does not exist among the results of the last query executed

getArrayItemOfElements()

Given an array containing a series of records, it returns an array containing only the values of a specific field


    public
                    getArrayItemOfElements(array<string|int, mixed> &$elements, string $fieldname) : array<string|int, mixed>

Parameters

$elements : array<string|int, mixed>: all records to be analyzed
$fieldname : string: name of the field / column containing the data to be extrapolated

Return values

array<string|int, mixed> —

array containing the list of values found or null if it found nothing

getColumnNames()

Returns an array containing the names of all columns for the last query executed


    public
                    getColumnNames() : array<string|int, mixed>|null

Return values

array<string|int, mixed>|null —

the associative having as index and value the column names of the queries or null if it fails or null if the table was not found

getCurrentDate()

Return the current date for this DB


    public
            static        getCurrentDate() : string

Return values

string —

the current date for this DB

getCurrentDateTime()

Return the current date time for this DB


    public
            static        getCurrentDateTime() : string

Return values

string —

the current date time for this DB

getCurrentDBKey()

Returns the key of the current DB


    public
                    getCurrentDBKey() : string|null

Return values

string|null —

the key of the current DB

getDataTable()

Given a previously executed query, it returns the result in a two-dimensional array


    public
                    getDataTable(array<string|int, mixed> &$data[, int $type = null ][, string|null $with_index = null ]) : int|null

Parameters

$data : array<string|int, mixed>

array containing the records found

$type : int = null

it can assume the values: DB_ASSOC, DB_NUM and DB_BOTH equivalent to the type of array it must return, respectively:

numeric index (returned from getDBResultNum())
associative index (returned from getDBResultAssoc())
both indices (returned from getDBResultBoth()) by default is value returned from getDBResultAssoc(), that is, it returns an associative array

$with_index : string|null = null

indexes of the output array (for example it could be "id"), if specified the numeric value of the row is replaced with the value indicated in the index

Return values

int|null —

the total of records found or null if the query is not valid or return empty results

getDataValue()

Returns a query value relative to a row and a column


    public
                    getDataValue([int|string $name_col = '0' ][, int|null $pos_row = null ]) : string|null

Parameters

$name_col : int|string = '0': column name or numeric index (default 0)
$pos_row : int|null = null: position of the row starting from 0, if it is null or not indicated it reads the current value

Return values

string|null —

value of the indicated row and column relative to the previously executed query, null if it does not exist

getDBHandle()

Returns the current handle of the DB


    public
            static        getDBHandle() : mixed

Return values

mixed —

current DB handle or null if not available

getDBResultAssoc()

Returns the constant DB_ASSOC which establishes that the query must return an associative array


    public
                    getDBResultAssoc() : int

Return values

int —

getDBResultBoth()

Returns the constant DB_ASSOC which establishes that the query must return both an associative array and a numeric array


    public
                    getDBResultBoth() : int

Return values

int —

getDBResultNum()

Returns the constant DB_ASSOC which establishes that the query must return a numeric array


    public
                    getDBResultNum() : int

Return values

int —

getDBTable()

Returns the name of the current main table


    public
                    getDBTable() : string

Return values

string —

name of the main reference table

getFetchArray()

Reads a row of the position database $pos_row of the table of the previously executed query and returns it as an array with both numeric and associative numbers


    public
                    getFetchArray([int|null $pos_row = null ]) : array<string|int, mixed>|null

Parameters

$pos_row : int|null = null: position of the line to read, if omitted is the current element

Return values

array<string|int, mixed>|null —

the array of the row indicated by $ pos_row (having both a numeric index and containing the column name) or null where the result is not valid e.g. if the index is outside the range of the table

getFetchAssoc()

Gets a row of the position database $pos_row of the table of the previously executed query and returns it as an associative array in an associative array whose index is given by the name of the column


    public
                    getFetchAssoc([int|null $pos_row = null ]) : array<string|int, mixed>|null

Parameters

$pos_row : int|null = null: position of the line to read, the current element if it is omitted

Return values

array<string|int, mixed>|null —

associative array of the row indicated by $pos_row or null in which the result is not valid e.g.: if the index is outside the range of the table

getFetchRow()

Gets a row of the $pos_row position db of the previously executed query table and returns it as an array with a numeric index


    public
                    getFetchRow([int|null $pos_row = null ]) : array<string|int, mixed>|null

Parameters

$pos_row : int|null = null: posizione della riga da leggere, se omesso e' l'elemento corrente

Return values

array<string|int, mixed>|null —

the array of the indicated row (having a numeric index) from $pos_row or null in which the result is not valid (e.g. if the index is outside the range of the table)

getIdsArray()

Creates an array containing all elements found based on a DB table name or index


    public
                    getIdsArray([int|string $item = 0 ][, int|string|null $idx = null ]) : array<string|int, mixed>

Parameters

$item : int|string = 0: index (int or string) of array to be returned
$idx : int|string|null = null: name or index of the field (by default it is 0, which is the first element)

Return values

array<string|int, mixed> —

containing all values indicated by $idx

getInfoAllTables()

Returns an array containing the list of all the tables of the current DB


    public
                    getInfoAllTables([string|null $table = null ][, bool $with_sizes = false ]) : array<string|int, mixed>

Parameters

$table : string|null = null: base table name to get all those that use it
$with_sizes : bool = false: if "false" it does not calculate the total of the records (by default it is true)

Return values

array<string|int, mixed> —

information relating to the tables in the DB

getLastDBErrorNumber()

Reads the error number issued by the DB


    public
            static        getLastDBErrorNumber() : int

Return values

int —

integer value containing the error issued by the DB

getLastDBErrorString()

Gets any error issued by the DB


    public
            static        getLastDBErrorString() : string

Return values

string —

containing the error issued by the DB

getLastID()

Returns the ID of the last inserted item


    public
                    getLastID() : int|null

Return values

int|null —

if the operation was successful, it returns the id of the last element inserted

getNextID()

Returns the from value of the next id to use in the insert NOTE: in Mysql you can set the primary key as autoincrement, than is not needed to set the table and it returns always null


    public
                    getNextID([string $table = '' ]) : int|null

Parameters

$table : string = '': the name of counter associated to SEQUENCE

Return values

int|null —

the next ID of the table

getQueryDataTable()

Given a previously executed query, it returns the result in a two-dimensional array


    public
                    getQueryDataTable(array<string|int, mixed> &$data, string $sql[, array<string|int, mixed> $bind_param_values = null ][, array<string|int, string> $bind_param_types = null ][, int|null $type = null ][, string|null $with_index = null ]) : int|null

Parameters

$data : array<string|int, mixed>

array containing the records found

$sql : string

string containing the query to execute

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

deferred values of the query

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

DB data types

$type : int|null = null

it can assume the values: CDB::DB_ASSOC, CDB::DB_NUM and CDB::DB_BOTH equivalent to the type of array it must return, respectively:

numeric index (returned from getDBResultNum())
associative index (returned from getDBResultAssoc())
both indices (returned from getDBResultBoth()) by default is value returned from getDBResultAssoc(), that is, it returns an associative array

$with_index : string|null = null

indexes of the output array (for example it could be "id"), if specified the numeric value of the row is replaced with the value indicated in the index

Return values

int|null —

the total of records found or null if the query is not valid or return empty results

getQueryDataValue()

Queries the DB and returns a specific value


    public
                    getQueryDataValue(string $sql[, string $name_col = '0' ][, int|null $pos_row = null ]) : string|null

Parameters

$sql : string: string containing the query to execute
$name_col : string = '0': column name or numeric index (default 0)
$pos_row : int|null = null: position of the row starting from 0, if it is null or not indicated it reads the current value

Return values

string|null —

indicated value of the row and column (by default it is the first column of current element)

getQueryFetchAssoc()

Queries the DB and returns an associative array containing the result


    public
                    getQueryFetchAssoc(string $sql[, int|null $pos_row = null ]) : array<string|int, mixed>|null

Parameters

$sql : string: string containing the query to execute
$pos_row : int|null = null: position of the row starting from 0, if it is null or not indicated it reads the current value

Return values

array<string|int, mixed>|null —

the associative array of the row indicated by $ pos_row or null if the result is not valid (e.g. if the index is outside the range of the table)

getQueryFetchRow()

Queries the DB and returns a row from the DB as an array having a numeric index


    public
                    getQueryFetchRow(string $sql[, int|null $pos_row = null ]) : array<string|int, mixed>|null

Parameters

$sql : string: string containing the query to execute
$pos_row : int|null = null: position of the row starting from 0, if it is null or not indicated it reads the current value

Return values

array<string|int, mixed>|null —

the array of the indicated row (having a numeric index) from $pos_row or null in which the result is not valid (e.g. if the index is outside the range of the table)

getTableColumnNames()

Returns an array containing the names of all columns of $tablename


    public
                    getTableColumnNames(string $tablename) : array<string|int, mixed>

Parameters

$tablename : string: name of table

Return values

array<string|int, mixed> —

an array with all fields or null if the table was not found

getTotalColumns()

Returns the number of columns of a previously executed query


    public
                    getTotalColumns() : int|bool

Return values

int|bool —

total of the columns that make up a table generated by the last query executed or boolean "false" in case of error

getTotalRows()

Reads the total of rows of a previously executed query


    public
                    getTotalRows() : int|bool

Return values

int|bool —

the total number of rows which make up a table generated by the last query executed or return false in case of error

info()

Interesting events.


    public
            static        info(string $message[, array<string|int, mixed> $context = [] ]) : void

Example: User logs in, SQL logs.

Parameters

$message : string
$context : array<string|int, mixed> = []

Return values

void —

insertRecord()

Make an entry in the DB


    public
                    insertRecord(string|null $table, array<string, mixed> $items, array<string|int, mixed> $types) : true

Parameters

$table : string|null: string containing the insert to execute
$items : array<string, mixed>: associative array containing FIELD_NAME => FIELD_VALUE of the table
$types : array<string|int, mixed>

Return values

true —

if the insertion has been made

log()

Logs with an arbitrary level.


    public
            static        log(mixed $level, string $message[, array<string|int, mixed> $context = [] ]) : void

Parameters

$level : mixed
$message : string
$context : array<string|int, mixed> = []

Return values

void —

notice()

Normal but significant events.


    public
            static        notice(string $message[, array<string|int, mixed> $context = [] ]) : void

Parameters

$message : string
$context : array<string|int, mixed> = []

Return values

void —

prepare()

Prepares an SQL statement for execution


    public
                    prepare(string $sql) : void

Parameters

$sql : string: string containing the query to execute

Return values

void —

query()

Query the DB with SQL


    public
                    query(string $sql[, array<string|int, mixed>|null $bind_param_values = null ][, array<string|int, mixed>|null $bind_param_types = null ]) : bool

Parameters

$sql : string: string containing the query to execute
$bind_param_values : array<string|int, mixed>|null = null: deferred values of the query
$bind_param_types : array<string|int, mixed>|null = null: DB data types

Return values

bool —

true if the query was successful

rollback()

Cancel all SQL operations performed after calling beginTransaction ()


    public
                    rollback() : void

Return values

void —

setAutoCommit()

Set autocommit, as opposed to transitions, if true each operation is self-consistent and validated


    public
                    setAutoCommit([bool $bool = true ]) : void

Parameters

$bool : bool = true: if is true the autocommit is running Note: if you use the transitions with you must be set to "false"

Return values

void —

setCurrentDBKey()

Set the current DB key NOTE: generally it is not necessary to use a DB key but it can be useful when there are multiple databases to manage


    public
                    setCurrentDBKey(string $dbkey) : bool

Parameters

$dbkey : string: DB key value to set

Return values

bool —

true if the db key is valid

setDBTable()

Sets the name of the reference table


    public
                    setDBTable(string $table) : void

Parameters

$table : string: name of the main table to set

Return values

void —

updateRecord()

Make an update in the DB


    public
                    updateRecord(string|null $table, string $conditions, array<string, mixed> $items, array<string|int, string> $conditions_values, array<string|int, mixed> $types) : bool

Parameters

$table : string|null: string containing the update to be performed
$conditions : string: string containing the conditions of the update whose values are replaced by a "?"
$items : array<string, mixed>: associative array containing FIELD_NAME => FIELD_VALUE of the table
$conditions_values : array<string|int, string>: array containing the values of the conditions declared in $conditions
$types : array<string|int, mixed>

Return values

bool —

true if the insertion has been made

warning()

Exceptional occurrences that are not errors.


    public
            static        warning(string $message[, array<string|int, mixed> $context = [] ]) : void

Example: Use of deprecated APIs, poor use of an API, undesirable things that are not necessarily wrong.

Parameters

$message : string
$context : array<string|int, mixed> = []

Return values

void —

_realConnection()

Check the connection to the DB, if not there, try to connect


    protected
            static        _realConnection() : mixed

Return values

mixed —

changeCurrentConnectionKey()

Change the current db key (if you have more than on DB)


    protected
            static        changeCurrentConnectionKey(string|null $dbkey) : void

Parameters

$dbkey : string|null: the new dbkey for identify the DB connection if exists

Return values

void —

convertDBType2BindType()

Converts from the type of the DB (INT, VARCHAR, FLOAT, BLOB) to the type of Bind (i, s, d, b)


    protected
                    convertDBType2BindType(string|null $db_type) : string|null

Parameters

$db_type : string|null: field db type

Return values

string|null —

corresponding variable type for binding:

'i' corresponding variable has type integer 'd' corresponding variable has type double 's' corresponding variable has type string 'b' corresponding variable is a blob and will be sent in packets

existsTableColumns()

Check if a named list of fields exists in a db table


    protected
                    existsTableColumns(string $table, array<string|int, mixed> $names) : bool

Parameters

$table : string: name of table
$names : array<string|int, mixed>: list of field to check

Return values

bool —

true if exists all fields

getConnectionError()

Set the current db key (if you have more than on DB)


    protected
            static        getConnectionError([string|null $dbkey = null ]) : int

Parameters

$dbkey : string|null = null: the new dbkey for identify the DB connection if exists

Return values

int —

getCurrentConnectionKey()

Get the current db key


    protected
            static        getCurrentConnectionKey() : string|null

Return values

string|null —

key for identify the DB connection

isConnected()

Check the connection is active without error


    protected
            static        isConnected([string|null $dbkey = null ]) : bool

Parameters

$dbkey : string|null = null: valid key for identify the DB connection (if null or not declared the key is of current db) Is useful when you have more than one DB, in other case you don't need to use

Return values

bool —

true if is connected

setDBColumns()

Returns an array containing the list of columns of a table each columns contains: - 'field' the name of column, - 'type' the type of column, - 'null' if accept the NULL DB value,


    protected
                    setDBColumns(array<string|int, mixed> &$db_cols[, string|null $table = null ]) : void

Parameters

$db_cols : array<string|int, mixed>: the results to be returned
$table : string|null = null: the table to be sampled, if not specified or null, uses the previously set table

Return values

void —

CMySqlDB extends CDBMS in package dressapi core dbms

Tags

Table of Contents

Constants

ALERT

Tags

CRITICAL

Tags

DB_ASSOC

Tags

DB_BOTH

Tags

DB_NUM

Tags

DEBUG

Tags

EMERGENCY

Tags

ERROR

Tags

INFO

Tags

NOTICE

Tags

WARNING

Tags

Properties

$autocommit

Tags

$database_name

Tags

$dbkey

Tags

$dbname

Tags

$dbpassword

Tags

$dbusername

Tags

$handle

Tags

$hostname

Tags

$last_id

Tags

$port

Tags

$results

Tags

$stmt

Tags

$table

Tags

Methods

__construct()

Parameters

Tags

Return values

__destruct()

Tags

Return values

alert()

Parameters

Tags

Return values

beginTransaction()

Tags

Return values

commit()

Tags

Return values

connect()

Parameters

Tags

Return values

critical()

Parameters

Tags

Return values

debug()

CMySqlDB extends CDBMS
in package

dressapi

core

dbms