MySqliAbstract

MySQL abstract class. To create instances of IDbAbstract for connecting and accessing MySQL databases.
This class is not included in the autoload as it is recommended to create a database connection and grab a database access instance from the "arta\Database" object factory.
Source
MySqliAbstract.php
Depends on
mysqli , DatabaseError , Inspect , IDbResult , MySqliResult
Extends
DbAbstract

Constants

DBMS = 'mysql'

Database system name

ENGINE = 'MySQLi'

PHP extension name

MAX_IDENTIFIER_NAME = 63

Max number of chars allowed for identifier names by this database system

Properties

__activeQue__ = 'default'

$obj->__activeQue__ = 'default'

Identifies the active queue index. The queue related methods (queue add, commit, etc. SQLs) will perform their action on the active queue.

__que__ = array

$obj->__que__ = array()

Holds SQL queues and their SQLs, SQLs inside each queue can to executed in a transaction or at once. Thought this property is for managed by the methods related to queues, it is made public to be hackable.

mysqli

$obj->mysqli

MySQL connection instance

Methods

__construct

MySqliAbstract $obj = new MySqliAbstract(array configs)

Connect to a database.

Arguments

    configs (array)
    Connection configs.
    {
        string server   : localhost database server address,
        string port     : depends on the engine database server port,
        string dbname   : database name,
        string user     : database user-name,
        string password : database password,
        string advanced : false
            false=create an IDbAbstract instance
            true=create an IDbAdvanced instance,
    }
    

Returns

void

__get

mixed __get(string col)

Get data of a column. You can get the data immediately after a query, if no row has been fetched yet, this will fetch a row to get the column data.

Arguments

    col (string)
    Column name

Returns

mixed
Data

__tostring

string $obj->__tostring()

Get info about the connection and database engine.

Returns

string
Info about the connection and database engine

add

IDbAbstract $obj->add(string sql, array | [mixed] params=null)

Add an SQL to the active queue.

Arguments

    sql (string)
    SQL string (may continue params)
    Sub-strings starting with $ such as $1 or $name are considered as parameters
    and will be replaced with parameter values which must be provided as next
    argument(s). e.g.
    
    Params from a list:
    "SELECT * FROM x WHERE id = $1 AND (name = $2 OR name IN ($3))"
    
    Params from a dictionary:
    "SELECT * FROM x WHERE id = $id AND (name = $name OR name IN ($names))"
    
    params=null (array | [mixed])
    Params values
    When params are used inside the condition string, values for the params must
    be passed to this method as shown below. Values will be sanitized before being
    inserted into the condition string.
    
    List, passed as individual method arguments:
    5, 'Ronnie', ['James', 'Dio',]
    
    Dictionary, passed as one array argument:
    {'id': 5, 'name': 'Ronnie', 'names': ['James', 'Dio',],}
    

Returns

IDbAbstract
For method chaining

arrayWhere

string $obj->arrayWhere(array array, string op, string prefix=null)

Creates an SQL condition from a map. This method is used by the Model class. Turns {column1: value1, column2: value2} into column1=value1 AND column2=value2

Arguments

    array (array)
    {column1: value1,}
    op (string)
    Operator to be used between each part
    prefix=null (string)
    Prefix to be added to each column name

Returns

string
SQL condition to be used in WHERE

begin

IDbAbstract $obj->begin(string q='default')

Create a new SQL queue and set it as the active queue. You can have one or more queues, you can make a queue active by method "toggle()" and add SQL statements to the active queue using the ("add()", "update(), insert()" and "delete()") methods and finally rollback or commit a queue as a database transaction or one by one. If you do not need more than one queues you do not have to use this method and toggle, simply use the action methods and when it's call "commit()" or "rollback()".

Arguments

    q='default' (string)
    Queue ID. You can use this ID to address a specific queue, if you never created new queues then there is only one queue named "default"

Returns

IDbAbstract
For method chaining

connection

array $obj->connection()

Get connection configs.

Returns

array
Connection configs.
{
    string server   : localhost database server address,
    string port     : depends on the engine database server port,
    string dbname   : database name,
    string user     : database user-name,
    string password : database password,
    string advanced : false
        false=create an IDbAbstract instance
        true=create an IDbAdvanced instance,
}

delete

IDbAbstract $obj->delete(
    string table,
    string where=null,
    array | [mixed] params=null
)

Add an DELETE statement to the active SQL queue.

Arguments

    table (string)
    Table name
    where=null (string)
    SQL condition string (may contain params)
    Sub-strings starting with $ such as $1 or $name are considered as parameters
    and will be replaced with parameter values which must be provided in the next
    argument(s). e.g.
    
    Params from a list:
    "SELECT * FROM x WHERE id = $1 AND (name = $2 OR name IN ($3))"
    
    Params from a dictionary:
    "SELECT * FROM x WHERE id = $id AND (name = $name OR name IN ($names))"
    
    An array can be passes instead of a string:
    {column: value,} e.g.
    {age: 24, id: [1, 2, 10,],} means "age = 24 AND id IN (1, 2, 10)"
    
    params=null (array | [mixed])
    Param values
    When params are used inside the condition string, values for the params must
    be passed to this method as shown below. Values will be sanitized before being
    inserted into the condition string.
    
    List, passed as individual method arguments:
    5, 'Ronnie', ['James', 'Dio',]
    
    Dictionary, passed as one array argument:
    {'id': 5, 'name': 'Ronnie', 'names': ['James', 'Dio',],}
    

Returns

IDbAbstract
For method chaining

insert

IDbAbstract $obj->insert(string table, array newVals, array newValsX=null)

Add an INSERT statement to the active SQL queue.

Arguments

    table (string)
    Table name
    newVals (array)
    Array of columns and their values. Values will be SQL escaped. To not escape a value put an ! at the beginning of the column name. {col-name: val-do-escaped, !col-name: val-do-not-escape,}
    newValsX=null (array)
    Array of columns and their values. Values will NOT be SQL escaped. An alternative way to adding ! to the col-name in newVals {col: val-do-not-escape,}

Returns

IDbAbstract
For method chaining

inspect

void|string|array $obj->inspect(bool printI=true)

Inspect/debug the query and the transaction queue.

Arguments

    printI=true (bool)
    true=print the debug info and die, false=return the debug info

Returns

void|string|array
Debug info or nothing based on arguments.

page

array $obj->page(string sql, array | [mixed] params=null, array configs)

To query a page of data. A page is a subset of the result consisting of a max_number or rows, each page is addressed by a number starting from 1.

Arguments

    sql (string)
    SQL string (may continue params)
    Sub-strings starting with $ such as $1 or $name are considered as parameters
    and will be replaced with parameter values which must be provided as next
    argument(s). e.g.
    
    Params from a list:
    "SELECT * FROM x WHERE id = $1 AND (name = $2 OR name IN ($3))"
    
    Params from a dictionary:
    "SELECT * FROM x WHERE id = $id AND (name = $name OR name IN ($names))"
    
    params=null (array | [mixed])
    Params values
    When params are used inside the condition string, values for the params must
    be passed to this method as shown below. Values will be sanitized before being
    inserted into the condition string.
    
    List, passed as individual method arguments:
    5, 'Ronnie', ['James', 'Dio',]
    
    Dictionary, passed as one array argument:
    {'id': 5, 'name': 'Ronnie', 'names': ['James', 'Dio',],}
    
    configs (array)
    Page configs.
    {
        int  page     :
            Page index to query,
        bool exec     : true
            true=execute the query
            false=return the query SQL string,
        int  max_rows : 30
            Max number of rows per page,
        int  count    : null
            Query's total row count (for all pages). If this value is not
            provided it will be calculated (by executing a second count query).
            To avoid this extra query provide this value when possible,
    }
    

Returns

array
This array can be fed to an instance of "Paging" to get the paging links
{
    string sqlstr   :
        The SQL query to get that page of data,
    int    page     :
        Page index which was queried,
    bool   exec     :
        true=the query was executed successfully
        false=the query was not executed,
    int    max_rows :
        Max number of rows per page,
    int    count    :
        Query's total row count (for all pages). To avoid extra count
        queries when querying next pages, try to keep this value and
        reuse it if possible.
}

Throws

que

array|string $obj->que(string | false q=null, int i=null)

Get queue(s) (SQL list(s)).

Arguments

    q=null (string | false)
    Queue ID to be returned, false= return all queues, null=return the active queue
    i=null (int)
    Return only the i'th SQL of the queue

Returns

array|string
An SQL string or a list of SQL strings

rollback

IDbAbstract $obj->rollback(string q=null)

Rollback, cancel and remove a queue.

Arguments

    q=null (string)
    Queue ID to be canceled, null=cancel the active queue

Returns

IDbAbstract
For method chaining

toggle

IDbAbstract $obj->toggle(string q='default')

Toggle queues (activate a queue).

Arguments

    q='default' (string)
    Queue ID to become active

Returns

IDbAbstract
For method chaining

transaction

IDbAbstract $obj->transaction(string sql, array | [mixed] params=null)

Add an SQL to the active queue. Alias for "add()".

Arguments

    sql (string)
    SQL string (may continue params)
    Sub-strings starting with $ such as $1 or $name are considered as parameters
    and will be replaced with parameter values which must be provided as next
    argument(s). e.g.
    
    Params from a list:
    "SELECT * FROM x WHERE id = $1 AND (name = $2 OR name IN ($3))"
    
    Params from a dictionary:
    "SELECT * FROM x WHERE id = $id AND (name = $name OR name IN ($names))"
    
    params=null (array | [mixed])
    Params values
    When params are used inside the condition string, values for the params must
    be passed to this method as shown below. Values will be sanitized before being
    inserted into the condition string.
    
    List, passed as individual method arguments:
    5, 'Ronnie', ['James', 'Dio',]
    
    Dictionary, passed as one array argument:
    {'id': 5, 'name': 'Ronnie', 'names': ['James', 'Dio',],}
    

Returns

IDbAbstract
For method chaining

update

IDbAbstract $obj->update(
    string table,
    array newVals,
    string where=null,
    array | [mixed] params=null
)

Add an UPDATE statement to the active SQL queue.

Arguments

    table (string)
    Table name
    newVals (array)
    Array of columns and their update values. Values will be SQL escaped. To not escape a value put an ! at the beginning of the column name. {col-name: val-do-escaped, !col-name: val-do-not-escape,}
    where=null (string)
    SQL condition string (may contain params)
    Sub-strings starting with $ such as $1 or $name are considered as parameters
    and will be replaced with parameter values which must be provided in the next
    argument(s). e.g.
    
    Params from a list:
    "SELECT * FROM x WHERE id = $1 AND (name = $2 OR name IN ($3))"
    
    Params from a dictionary:
    "SELECT * FROM x WHERE id = $id AND (name = $name OR name IN ($names))"
    
    An array can be passes instead of a string:
    {column: value,} e.g.
    {age: 24, id: [1, 2, 10,],} means "age = 24 AND id IN (1, 2, 10)"
    
    params=null (array | [mixed])
    Param values
    When params are used inside the condition string, values for the params must
    be passed to this method as shown below. Values will be sanitized before being
    inserted into the condition string.
    
    List, passed as individual method arguments:
    5, 'Ronnie', ['James', 'Dio',]
    
    Dictionary, passed as one array argument:
    {'id': 5, 'name': 'Ronnie', 'names': ['James', 'Dio',],}
    

Returns

IDbAbstract
For method chaining

affected

int $obj->affected()

Affected row count.

Returns

int
Row count

castTime

string $obj->castTime(string val, string type=TIMESTAMP, bool isCol=false)

Get cast date/time sentence.

Arguments

    val (string)
    A date/time string or a table column name
    type=TIMESTAMP (string)
    Cast: TIMESTAMP, DATETIME, TIME or DATE
    isCol=false (bool)
    Is "val" a string or a column name

Returns

string
Cast sentence

close

void $obj->close()

Close database connection.

Returns

void

commit

bool $obj->commit(bool transaction=true, string q=null)

Commit the active SQL queue. A queue ID can be passed explicitly to commit a specific queue.

Arguments

    transaction=true (bool)
    true=commit the queue SQLs in a database transaction, false=run queue SQLs one by one and not in a database transaction
    q=null (string)
    Queue ID. null=commit the active queue. Provide this argument to commit a specific queue by it's ID

Returns

bool
State of success

Throws

concat

string $obj->concat(array strs)

Make a database concat sentence out of the passed values.

Arguments

    strs (array)
    Strings to be concated

Returns

string
Concated values.

connect

bool $obj->connect(array configs=array())

Connect or reconnect to a database.

Arguments

    configs=array() (array)
    Connection configs, if empty then connect using the previous connection configs.
    {
        string server   : localhost database server address,
        string port     : 3306 database server port,
        string dbname   : database name,
        string user     : database user-name,
        string password : database password,
        string advanced : false
            false=create an IDbAbstract instance
            true=create an IDbAdvanced instance,
    }
    

Returns

bool
State of success

count

int $obj->count(string sql, array | [mixed] params=null)

Get row count of the last query (without arguments) or a specified a query.

Arguments

    sql (string)
    SQL string (may continue params)
    Sub-strings starting with $ such as $1 or $name are considered as parameters
    and will be replaced with parameter values which must be provided as next
    argument(s). e.g.
    
    Params from a list:
    "SELECT * FROM x WHERE id = $1 AND (name = $2 OR name IN ($3))"
    
    Params from a dictionary:
    "SELECT * FROM x WHERE id = $id AND (name = $name OR name IN ($names))"
    
    params=null (array | [mixed])
    Params values
    When params are used inside the condition string, values for the params must
    be passed to this method as shown below. Values will be sanitized before being
    inserted into the condition string.
    
    List, passed as individual method arguments:
    5, 'Ronnie', ['James', 'Dio',]
    
    Dictionary, passed as one array argument:
    {'id': 5, 'name': 'Ronnie', 'names': ['James', 'Dio',],}
    

Returns

int
Row count

Throws

esc

string $obj->esc(string val)

Escapes non SQL safe characters.

Arguments

    val (string)
    Value to be escaped

Returns

string
Escaped value

exists

bool $obj->exists(string table, string sql, array | [mixed] params=null)

Check if a table(when only the first argument is provided) exists or if a query has results.

Arguments

    table (string)
    Database table name or FROM clause
    sql (string)
    SQL string (may continue params)
    Sub-strings starting with $ such as $1 or $name are considered as parameters
    and will be replaced with parameter values which must be provided as next
    argument(s). e.g.
    
    Params from a list:
    "SELECT * FROM x WHERE id = $1 AND (name = $2 OR name IN ($3))"
    
    Params from a dictionary:
    "SELECT * FROM x WHERE id = $id AND (name = $name OR name IN ($names))"
    
    params=null (array | [mixed])
    Params values
    When params are used inside the condition string, values for the params must
    be passed to this method as shown below. Values will be sanitized before being
    inserted into the condition string.
    
    List, passed as individual method arguments:
    5, 'Ronnie', ['James', 'Dio',]
    
    Dictionary, passed as one array argument:
    {'id': 5, 'name': 'Ronnie', 'names': ['James', 'Dio',],}
    

Returns

bool
Table or result existence

Throws

fieldCount

int $obj->fieldCount()

Query field count.

Returns

int
Field count

fields

array|string $obj->fields(string glue=null, string esc="`")

Get query columns.

Arguments

    glue=null (string)
    null=return an array of column names, string=return a string of column names glued with the string
    esc="`" (string)
    Column name escape character

Returns

array|string
A list of column names or a string of glued column names

free

void $obj->free()

Free query result resource.

Returns

void

getResult

IDbResult $obj->getResult()

Get the query result as an IDbResult.

Returns

IDbResult
An MySqliResult instance

info

array $obj->info()

Get info about the dbms and class.

Since
Artaengine 1.1.0

Returns

array
Info array
{
    string server     : MySQL server version,
    string client     : MySQL client version,
    string class      : "Arta.MySqliAbstract",
    string version    : Class version,
    string extension  : "mysqli",
    string dbms       : "MySQL",
    array  connection : {
        string server   : database server address,
        string port     : database server port,
        string dbname   : database name,
        string user     : database user-name,
        string password : database password,
        string advanced :
            false=create an IDbAbstract instance
            true=create an IDbAdvanced instance,
    },
}

isSerial

bool $obj->isSerial(string table, string col=id)

Check if a column is serial(auto increment).

Arguments

    table (string)
    Database table name
    col=id (string)
    Serial(auto increment) column name

Returns

bool
true=the column is serial

isnull

string $obj->isnull(string col, string | array val)

Wrap values inside COALESCE.

Arguments

    col (string)
    Column name
    val (string | array)
    Value(s)

Returns

string
COALESCE(col, vals, ...)

limit

string $obj->limit(string sql, int limit=10, int offset=0)

Pass an SQL query and get a limited SQL.

Arguments

    sql (string)
    SQL query to put limit on
    limit=10 (int)
    Number of rows to limit query result to
    offset=0 (int)
    Row number to start from (zero based)

Returns

string
The SQL with LIMIT clause

next

object|array $obj->next(bool object=true)

Fetch row as object (default) or array.

Arguments

    object=true (bool)
    true=fetch object, false=fetch array

Returns

object|array
The fetched row

query

bool $obj->query(string sql, array | [mixed] params=null)

Execute an SQL query. Use parameters for for values which need to be SQL escaped/safe.

Arguments

    sql (string)
    SQL string (may continue params)
    Sub-strings starting with $ such as $1 or $name are considered as parameters
    and will be replaced with parameter values which must be provided as next
    argument(s). e.g.
    
    Params from a list:
    "SELECT * FROM x WHERE id = $1 AND (name = $2 OR name IN ($3))"
    
    Params from a dictionary:
    "SELECT * FROM x WHERE id = $id AND (name = $name OR name IN ($names))"
    
    params=null (array | [mixed])
    Params values
    When params are used inside the condition string, values for the params must
    be passed to this method as shown below. Values will be sanitized before being
    inserted into the condition string.
    
    List, passed as individual method arguments:
    5, 'Ronnie', ['James', 'Dio',]
    
    Dictionary, passed as one array argument:
    {'id': 5, 'name': 'Ronnie', 'names': ['James', 'Dio',],}
    

Returns

bool
State of success

Throws

row

array $obj->row()

Fetch row as array.

Returns

array
The fetched row.

rows

array $obj->rows()

Fetch all rows in an arrays.

Returns

array
List of array rows [{row-array},]

seek

void $obj->seek(int row=0)

Move to a row.

Arguments

    row=0 (int)
    Zero based row index

Returns

void

serial

mixed $obj->serial(string table, string col=id, string position='current')

Get value of auto increment(serial) column.

Arguments

    table (string)
    Database table name
    col=id (string)
    Serial(auto increment) column name
    position='current' (string)
    'current' or 'next'

Returns

mixed
Value of the next/current auto increment(serial) column

serialSequence

void $obj->serialSequence()

For library uses only

Returns

void

toBool

string $obj->toBool(bool | int val)

Convert a value to an acceptable boolean value for the database.

Arguments

    val (bool | int)
    Source value

Returns

string
Database false/true

toTimestamp

string $obj->toTimestamp(int | array | string val)

Convert a value to a valid database timestamp value.

Arguments

    val (int | array | string)
    int=timestamp, array={y:, m:, d:, h:, i:, s:} or a subset of it, string=date/time timestamp | array: {y:, m:, d:, h:, i:, s:} or a subset | string: date/time

Returns

string
Database timestamp value