DLDatabase

Provides a wrapper around the MySQLi functions.

Summary

DLDatabase	Provides a wrapper around the MySQLi functions.
Private Static Data
$instances	(array) An array containing different instances of this class.
Private Data
$mysqli	(mysqli) An instance of the MySQLi object.
$sqls	(array) An array containing SQLs that will be executed when the method Execute is called.
Class Construction
__construct	Create a new instance of DLDatabase.
ObjectPool	Retrieve an instance of this class based on the provided login information.
Singleton	Retrieve an instance of this class using the database information stored in the configuration file.
Data Retrieval
GetAllRows	Retrieve all the rows in the database that match the given criteria.
GetAllRowsAssoc	Same as the method GetAllRows except that the resulting array will be different depending on the number of columns the result set has.
GetFirstValue	Retrieve a value of the first column of the first row.
GetLastInsertID	Returns the auto generated id used in the last query.
GetSingleRow	Retrieve a single row of the result set from the database.
RowCount	See how many rows we would retrieve from the given table if the given constraints are set.
Miscellaneous
BuildGroupByClause	Build the GROUP BY clause.
BuildJoinOnClause	Build the JOIN clause.
BuildOrderByClause	Build the ORDER BY clause from an array.
BuildSelectClause	Build the SELECT clause from an array.
BuildTableClause	Build the table clause.
BuildWhereClause	Build the WHERE clause from an array.
GetSQLs	Retrieve the currently stored SQLs.
Row Manipulation
DeleteData	Delete data from the database.
InsertData	Insert data into the database.
UpdateData	Update existing data in the database.
SQL Execution
Execute	Execute all of the currently stored SQLs.
Query	Query the database with the given SQL.
Table Manipulation
CreateTable	Create a new table in the database.
DropTable	Drop the given table from the database.

Private Static Data

$instances

private static $instances

(array) An array containing different instances of this class.

Private Data

$mysqli

private $mysqli

(mysqli) An instance of the MySQLi object.

$sqls

private $sqls

(array) An array containing SQLs that will be executed when the method Execute is called.

Class Construction

__construct

private function __construct( $host,
$username,
$password,
$database )

Create a new instance of DLDatabase.

Parameters

$host	(string) The host to connect to the MySQL server.
$username	(string) The username to connect to the MySQL server.
$password	(string) The password to connect to the MySQL server.
$database	(string) The name of the database to connect to.

Returns

(DLDatabase) An instance of DLDatabase.

ObjectPool

public static function &ObjectPool( $host,
$username,
$password,
$database )

Retrieve an instance of this class based on the provided login information.

Parameters

$host	(string) The host to connect to the MySQL server.
$username	(string) The username to connect to the MySQL server.
$password	(string) The password to connect to the MySQL server.
$database	(string) The name of the database to connect to.

Returns

(mixed) Either an instance of this class if the provided information is correct or FALSE if a connection could not be made.

Singleton

public static function &Singleton()

Retrieve an instance of this class using the database information stored in the configuration file.

Returns

(DLDatabase) An instance of DLDatabase.

Data Retrieval

GetAllRows

public function GetAllRows( $table,
$select,
$where = '',
$options = array() )

Retrieve all the rows in the database that match the given criteria.

Note that this method will call the method Execute before it does any retrieval.

Parameters

$table	(mixed) Either a string of the table or an array containing all the tables to retrieve from. If it is an array, refer to the method BuildTableClause.
$select	(mixed) Either a string of the SELECT clause or an array containing all the columns to retrieve. If it is an array, refer to the method BuildSelectClause.
$where	(mixed) Either a string of the WHERE clause or an array containing all the constraints. If it is an array, refer to the method BuildWhereClause. Defaults to an empty string.
$options	(array) An array containing data used in this method. Refer to the methods BuildJoinOnClause and BuildGroupByClause, BuildOrderByClause for more information regarding some of the attributes. Defaults to an empty array.

The structure of the $options parameter, if given, should look like the following:

array(
    and_or => {
        (string) The joining string of the first level in the WHERE
        clause. Defaults to "AND".
    },
    and_or_join => {
        (string) The joining string of the first level in the JOIN
        ON clause. Defaults to "AND".
    },
    distinct => {
        (boolean) Whether or not the result should be distinct.
        Defaults to FALSE.
    },
    freeform => {
        (string) Anything else to be attached at the end of the SQL
        right before the semicolon. Defaults to an empty string.
    },
    group_by => {
        (mixed) Either a strinf of the GROUP BY clause or an array
        containing the grouping. Refer to the method
        BuildGroupByClause. Defaults to an empty string.
    }
    join => {
        (mixed) Either a string of the JOIN ON clause or an array
        containing the joining condition. Refer to the method
        BuildJoinOnClause. Defaults to an empty string.
    },
    order => {
        (mixed) Either a string of the ORDER BY clause or an array
        containing the sorting preference. Refer to the method
        BuildOrderByClause. Defaults to an empty string.
    }
)

Returns

(array) An array containing all the rows in the database that match the search criteria. The result array will look something similar to the following:

array(
    array(
        {The name of the column.} => {
            (string) The value of the column.
        },
        ...
    ),
    ...
)

GetAllRowsAssoc

public function GetAllRowsAssoc( $table,
$select,
$where = '',
$options = array() )

Same as the method GetAllRows except that the resulting array will be different depending on the number of columns the result set has.

Parameters

$table	(mixed) Refer to the parameter $table from the method GetAllRows.
$select	(mixed) Refer to the parameter $select from the method GetAllRows.
$where	(mixed) Refer to the parameter $where from the method GetAllRows.
$options	(array) Refer to the parameter $options from the method GetAllRows.

Returns

(array) An array containing all the rows in the database that match the search criteria. If the result set has only one column, then the result will just be a single dimension array like the following:

array(
    {(string) The value of the column.},
    ...
)

If it has two columns, then the result set will look like the following array:

array(
    {The value of the first column.} => {
        (string) The value of the second column.
    },
    ...
)

Note that if a value exist more than once for the first column, then the later value will overwrite the previous values. If there are three or more columns, then the result will be like the following:

array(
    {The value of the first column.} => array(
        {The name of the second column} => {
            (string) The value of the second column.
        },
        {The name of the third column} => {
            (string) The value of the third column.
        },
        ...
    ),
    ...
)

Again, the later value of the first column will overwrite the previous value so only use this if you are sure the first column is unique throughout.

See

GetAllRows

GetFirstValue

public function GetFirstValue( $table,
$select,
$where = '',
$options = array() )

Retrieve a value of the first column of the first row.

Parameters

$table	(mixed) Refer to the parameter $table from the method GetAllRows.
$select	(mixed) Refer to the parameter $select from the method GetAllRows.
$where	(mixed) Refer to the parameter $where from the method GetAllRows.
$options	(array) Refer to the parameter $options from the method GetAllRows.

Returns

(mixed) The value of the first column of the first row of the result set. This returns NULL if it doesn’t exist.

See

GetAllRows

GetLastInsertID

public function GetLastInsertID()

Returns the auto generated id used in the last query.

Note that this will call the method Execute before it gets the last inserted ID.

Returns

(int) The ID of the last inserted ID.

GetSingleRow

public function GetSingleRow( $table,
$select,
$where = '',
$options = array() )

Retrieve a single row of the result set from the database.

Parameters

$table	(mixed) Refer to the parameter $table from the method GetAllRows.
$select	(mixed) Refer to the parameter $select from the method GetAllRows.
$where	(mixed) Refer to the parameter $where from the method GetAllRows.
$options	(array) Refer to the parameter $options from the method GetAllRows.

The structure of the $options parameter, if given, should look like the following:

array(
    row_to_get => {
        (int) The row to get. Note that the row counts begins with 0
        so 0 is the first row, 1 is the second row, etc. Note that
        if this is larger than the number rows that was retrieved,
        then this will return NULL. Defaults to 0.
    }
)

Returns

(mixed) The array containing the requested row or NULL if the given row doesn’t exist. The resulting array would look something similar to the following:

array(
    {The name of the first column.} => {
        (string) The value of the first column.
    },
    ...
)

See

GetAllRows

RowCount

public function RowCount( $table,
$where,
$options = array() )

See how many rows we would retrieve from the given table if the given constraints are set.

Note that this method will call the method Execute before it does any retrieval.

Parameters

$table	(mixed) Either a string of the table or an array containing all the tables to retrieve from. If it is an array, refer to the method BuildTableClause.
$where	(mixed) Either a string of the WHERE clause or an array containing all the constraints. If it is an array, refer to the method BuildWhereClause.
$options	(array) An array containing data used in this method. Refer to the method BuildJoinOnClause for more information regarding some of the attributes. Defaults to an empty array.

The structure of the $options parameter, if given, should look like the following:

array(
    and_or => {
        (string) The joining string of the first level in the WHERE
        clause. Defaults to "AND".
    },
    and_or_join => {
        (string) The joining string of the first level in the JOIN
        ON clause. Defaults to "AND".
    },
    freeform => {
        (string) Anything else to be attached at the end of the SQL
        right before the semicolon. Defaults to an empty string.
    },
    join => {
        (mixed) Either a string of the JOIN ON clause or an array
        containing the joining condition. Refer to the method
        BuildJoinOnClause. Defaults to an empty string.
    },
)

Returns

(int) The number of rows that the query would have returned.

Miscellaneous

BuildGroupByClause

public static function BuildGroupByClause( $data )

Build the GROUP BY clause.

Parameters

$data

(mixed) An array of data for the grouping condition or the string for it.

The structure of the $data parameter, if given, should look like the following:

array(
    {(string) The name of the column.},
    array(
        table => {
            (string) The alias of the table.
        },
        column => {
            (string) The name of the column.
        }
    ),
    ...
)

Returns

(string) The GROUP BY clause.

BuildJoinOnClause

public static function BuildJoinOnClause( $data,
$join = 'AND' )

Build the JOIN clause.

Parameters

$data	(mixed) An array of data for the joining condition or the string for it.
$join	(string) Optional and defaults to “AND”. What to join the constraints with. Note that this will alternate for each inner array. Values for this should be either “AND” or “OR”.

The structure of the $data parameter, if given, should look like the following:

array(
    array(
        left => array(
            table => {
                (string) The alias of the table.
            },
            column => {
                (string) The name of the column.
            }
        ),
        op => {
            (string) The operator to use. Defaults to "=".
        },
        right => array(
            table => {
                (string) The alias of the table.
            },
            column => {
                (string) The name of the column.
            }
        )
    ),
    array(
        array(
            left => array(
                table => {
                    (string) The alias of the table.
                },
                column => {
                    (string) The name of the column.
                }
            ),
            op => {
                (string) The operator to use. Defaults to "=".
            },
            right => array(
                table => {
                    (string) The alias of the table.
                },
                column => {
                    (string) The name of the column.
                }
            )
        ),
        ...
    ),
    ...
)

Returns

(string) The JOIN clause.

BuildOrderByClause

public static function BuildOrderByClause( $data )

Build the ORDER BY clause from an array.

Parameters

$data

(mixed) Either the ORDER BY clause itself or an array that the ORDER BY clause will be built from.

The structure of the $data parameter, if given, should look like the following:

array(
    array(
        column => {
            (string) The name of the column.
        },
        order => {
            (string) The order in which the column should be sorted.
            Possible values are "ASC" and "DESC".
        }
    ),
    array(
        column => array(
            table => {
                (string) The alias of the table.
            },
            column => {
                (string) The name of the column.
            }
        ),
        order => {
            (string) The order in which the column should be sorted.
            Possible values are "ASC" and "DESC".
        }
    )
    ...
)

Returns

(string) The ORDER BY clause.

BuildSelectClause

public static function BuildSelectClause( $data )

Build the SELECT clause from an array.

Parameters

$data

(mixed) The array that the SELECT clause will be built from or a string for it.

The structure of the $data parameter, if given, should look like the following:

array(
    {(string) The name of the column.},
    array(
        type => 'freeform',
        value => {
            (string) The value for the select.
        }
    ),
    array(
        type => 'column',
        column => {
            (string) The name of the column. This is the default
            type if the type is not given. Note that this is the
            same as just specifying the column name as a string.
        }
    ),
    array(
        type => 'alias',
        column => {
            (string) The name of the column.
        },
        alias => {
            (string) The alias for the column.
        }
    ),
    array(
        type => 'table',
        table => {
            (string) The alias of the table.
        },
        column => {
            (string) The name of the column.
        }
    ),
    array(
        type => 'table_alias',
        table => {
            (string) The alias of the table.
        },
        column => {
            (string) The name of the column.
        },
        alias => {
            (string) The alias for the column.
        }
    ),
    array(
        type => 'function'
        function => {
            (string) The MySQL function.
        },
        column => {
            (string) The name of the column.
        },
        alias => {
            (string) The alias for the result of the function.
        }
    ),
    array(
        type => 'function_table'
        function => {
            (string) The MySQL function.
        },
        table => {
            (string) The alias of the table.
        },
        column => {
            (string) The name of the column.
        },
        alias => {
            (string) The alias for the result of the function.
        }
    ),
    ...
)

Returns

(string) The SELECT clause.

BuildTableClause

public static function BuildTableClause( $tables )

Build the table clause.

Parameters

$tables

(mixed) An array of tables or the string for it.

The structure of the $tables parameter, if given, should look like the following:

array(
    {(string) The name of the table.},
    array(
        table => {
            (string) The name of the table.
        },
        alias => {
            (string) The alias for the table.
        }
    ),
    ...
)

Returns

(string) The table clause.

BuildWhereClause

public static function BuildWhereClause( $data,
$join = 'AND' )

Build the WHERE clause from an array. This will alternate between OR and AND as it goes further in the array.

Parameters

$data	(mixed) The array that the WHERE clause will be built from or a string for it.
$join	(string) Optional and defaults to “AND”. What to join the constraints with. Note that this will alternate for each inner array. Values for this should be either “AND” or “OR”.

The structure of the $data parameter, if given, should look like the following:

array(
    array(
        column => {
            (string) The name of the column.
        },
        op => {
            (string) The operator to use. Defaults to "=".
        },
        value => {
            (string) The value to compare against.
        }
    ),
    array(
        column => array(
            table => {
                (string) The alias of the table.
            },
            column => {
                (string) The name of the column.
            }
        ),
        op => {
            (string) The operator to use. Defaults to "=".
        },
        value => {
            (string) The value to compare against.
        }
    ),
    array(
        array(
            column => {
                (string) The name of the column.
            },
            op => {
                (string) The operator to use. Defaults to "=".
            },
            value => {
                (string) The value to compare against.
            }
        ),
        ...
    ),
    ...
)

Returns

(string) The body of the WHERE clause.

GetSQLs

public function GetSQLs()

Retrieve the currently stored SQLs.

Returns

(array) An array of the SQLs to be executed.

Row Manipulation

DeleteData

public function DeleteData( $table,
$where = '',
$options = array() )

Delete data from the database.

Parameters

$table	(string) The table where the data should be updated.
$where	(mixed) Either a string of the WHERE clause or an array containing all the constraints. If it is an array, refer to the method BuildWhereClause. Defaults to an empty string.
$options	(array) An array containing data used in this method. Defaults to an empty array.

The structure of the $options parameter, if given, should look like the following:

array(
    and_or => {
        (string) The joining string of the first level in the WHERE
        clause. Defaults to "AND".
    },
    freeform => {
        (string) Anything else to be attached at the end of the SQL
        right before the semicolon. Defaults to an empty string.
    }
)

InsertData

public function InsertData( $table,
$data )

Insert data into the database.

Note that this method will not actually execute until the method Execute is called.

Parameters

$table	(string) The table where the data should be updated.
$data	(array) An associative array of the data to be inserted. The key should correspond to the column name.

The structure of the $data parameter, if given, should look like the following:

array(
    {The name of the column.} => {
        (string) The value for the column.
    },
    {The name of the column.} => array(
        value => {
            (string) The value for the column. The difference
            between this and the string only is that this will not
            be surrounded by quotes. Use this if you need to call
            a MySQL function.
        }
    ),
    ...
)

UpdateData

public function UpdateData( $table,
$data,
$where )

Update existing data in the database.

Note that this method will not actually execute until the method Execute is called.

Parameters

$table	(string) The table where the data should be updated.
$data	(array) An associative array of the data to be updated. The key should correspond to the column name.
$where	(mixed) Either a string of the WHERE clause or an array containing all the constraints. If it is an array, refer to the method BuildWhereClause.

The structure of the $data parameter, if given, should look like the following:

array(
    {The name of the column.} => {
        (string) The value for the column.
    },
    {The name of the column.} => array(
        {
            (string) The value for the column. The difference
            between this and the string only is that this will not
            be surrounded by quotes. Use this if you need to call
            a MySQL function.
        }
    ),
    ...
)

SQL Execution

Execute

public function Execute()

Execute all of the currently stored SQLs.

Query

public function &Query( $sql )

Query the database with the given SQL.

Parameters

$sql	(string) The SQL statement to query.

Returns

(mixed) The result of the query.

Table Manipulation

CreateTable

public function CreateTable( $table,
$columns,
$options = array() )

Create a new table in the database. Note that this method will not actually execute until the method Execute is called.

Parameters

$table	(string) The name of the table to create.
$columns	(array) An associative array containing columns for the given table.
$options	(array) An associative array containing various options used by this method. Defaults to an empty array.

The structure of the $columns parameter, if given, should look like the following:

array(
    {The name of the column.} => array(
        auto_increment => {
            (boolean) Whether or not the column should have the auto
            increment property. Defaults to FALSE.
        },
        default => {
            (string) The default value for the column if it is not
            given. Defaults to an empty string.
        },
        default_quotes => {
            (boolean) Whether or not the default value should be
            surrounded by double quotes. Defaults to TRUE.
        }
        index => {
            (boolean) Whether or not the column should be indexed.
            Defaults to FALSE.
        },
        null => {
            (boolean) Whether or not the column can be null.
            Defaults to FALSE.
        },
        on_update => {
            (string) If anything should be done on update. Defaults
            to an empty string.
        },
        primary => {
            (boolean) Whether or not the column should be the
            primary key. Defaults to FALSE.
        },
        type => {
            (string) The type of the column. Default to "VARCHAR".
        },
        unique => {
            (boolean) Whether or not the column's data should be
            unique. Defaults to FALSE.
        },
        value => {
            (string) The value or length for the column. Default to
            an empty string.
        }
    ),
    ...
)

The structure of the $options parameter, if given, should look like the following:

array(
    drop => {
        (boolean) Drop the table if it already exists. Defaults to
        FALSE.
    },
    if_not_exists => {
        (boolean) Only create the table if it does not already
        exists. Defaults to TRUE.
    }
)

Returns

(boolean) Returns TRUE if successful, FALSE, otherwise.

DropTable

public function DropTable( $table )

Drop the given table from the database. Note that this method will not actually execute until the method Execute is called.

Parameters

$table

(string) The name of the table to drop.