1. class
  2. Core
  3. \
  4. DB

DB

class DB

Support database operations.

Properties

private int $_count

Number of items returned.
private string $_dbDriver

The database driver.
private bool $_error

Error status.
private int $_fetchStyle

The fetch style.
static private DB $_instance

Instance of this class.
private int $_lastInsertID

Id of last item inserted into database.
private PDO $_pdo

The PDO object.
private mixed $_query

The query.
private mixed $_result

The result.

Methods

__construct()

This constructor creates a new PDO object as an instance variable. If there are any failures the application quits with an error message.

bool
beginTransaction()

Begins a database transaction.

string
_buildJoin(array $join = [])

Constructs join statements for SQL queries.

bool
commit()

Commits the current database transaction.

static void
connect(array $override)

Establishes a new database connection using the provided configuration array.

int
count()

Number of rows affected or returned by the last query.

bool
delete(string $table, int $id)

Performs delete operation against SQL database.

bool
error()

Getter function for the $_error variable.

bool|array
find(string $table, array $params = [], bool|string $class = false)

Performs find operation against the database. The user can use parameters such as conditions, bind, order, limit, and sort.

array|object|bool
findFirst(string $table, array $params = [], bool|string $class = false)

Returns only the first row of the last query.

int
findTotal(string $table, array $params = [])

Returns number of records in a table.

array|object
first()

Returns first result in the _result array.

array
getColumns(string $table)

Returns columns for a table.

static DB
getInstance()

An instance of this class set as a variable. To be used in other class because we can't use $this.

PDO
getPDO()

Returns instance of PDO class.

static string|null
groupByColumn(string $column)

Appropriately formats column for query with GROUP BY operations. A call to the ANY_VALUE function is added if the DB driver is MySQL or MariaDB.

bool
insert(string $table, array $fields = [])

Perform insert operations against the database.

bool
inTransaction()

Checks if a transaction is currently active.

int|string|null
lastID()

The primary key ID of the last insert operation.

DB
query(string $sql, array $params = [], bool|string $class = false)

Performs database query operations that includes prepare, binding, execute, and fetch.

bool
_read(string $table, array $params = [], bool|string $class = false, bool $count = false)

Supports SELECT operations that maybe ran against a SQL database. It supports the ability to order and limit the number of results returned from a database query. The user can use parameters such as conditions, bind, order, limit, and sort.

array
results()

Returns all rows from the last query

bool
rollBack()

Rolls back the current database transaction.

bool
tableExists(string $table)

Checks whether a given table exists in the currently connected database.

bool
update(string $table, int $id, array $fields = [])

Performs update operation on a SQL database record.

int
updateWhere(string $table, array $fields, array $params = [])

Updates records in a table using params-style conditions.

bool
valueExistsInColumn(string $table, string $column, mixed $value)

Check if a value exists in a JSON or text-based column

Details

at line 76
private __construct()

This constructor creates a new PDO object as an instance variable. If there are any failures the application quits with an error message.

Exceptions

Exception

at line 112
bool beginTransaction()

Begins a database transaction.

Return Value

bool

True on success, false on failure

at line 123
protected string _buildJoin(array $join = [])

Constructs join statements for SQL queries.

Parameters

array $join

Data such as table, conditions, and aliases needed to construct join query. Default value is an empty array.

Return Value

string

The join component of a query.

at line 137
bool commit()

Commits the current database transaction.

Return Value

bool

True on success, false on failure

at line 169
static void connect(array $override)

Establishes a new database connection using the provided configuration array.

This method creates a new PDO instance based on the given connection details and sets it as the singleton instance for the DB class. It can be used to override the default database connection at runtime (e.g., for testing or connecting to a different database).

Supported drivers:

  • sqlite: Connects to a SQLite database file or an in-memory database. Foreign key constraints are enabled by default.
  • mysql: Connects to a MySQL/MariaDB database using the provided host, port, database name, charset, username, and password.

Parameters

array $override

An associative array containing connection parameters:

  • driver (string) The database driver ('sqlite' or 'mysql').
  • database (string) Path to SQLite file or database name for MySQL.
  • host (string) (MySQL) The database host.
  • port (int) (MySQL) The database port.
  • charset (string) (MySQL) The character set.
  • username (string) (MySQL) The username for authentication.
  • password (string) (MySQL) The password for authentication.

Return Value

void

Exceptions

Exception

at line 198
int count()

Number of rows affected or returned by the last query.

Return Value

int

The number of results found in an SQL query.

at line 215
bool delete(string $table, int $id)

Performs delete operation against SQL database.

Example setup: $contacts = $db->delete('contacts', 3);

Parameters

string $table

The name of the table that contains the record we want to delete.
int $id

The primary key for the record we want to remove from a database table.

Return Value

bool

True if delete operation is successful. Otherwise, we return false.

at line 225
bool error()

Getter function for the $_error variable.

Return Value

bool

The value for the $_error flag.

at line 251
bool|array find(string $table, array $params = [], bool|string $class = false)

Performs find operation against the database. The user can use parameters such as conditions, bind, order, limit, and sort.

Example setup: $contacts = $db->find('users', [ 'conditions' => ["email = ?"], 'bind' => ['chad.chapman@email.com'], 'order' => "username", 'limit' => 5, 'sort' => 'DESC' ]);

Parameters

string $table

The name or the table we want to perform our query against
array $params

An associative array that contains key value pair parameters for our query such as conditions, bind, limit, offset, join, order, and sort. The default value is an empty array.
bool|string $class

A default value of false, it contains the name of the class we will build based on the name of a model.

Return Value

bool|array

An array of object returned from an SQL query.

at line 271
array|object|bool findFirst(string $table, array $params = [], bool|string $class = false)

Returns only the first row of the last query.

Parameters

string $table

The name or the table we want to perform our query against.
array $params

An associative array that contains key value pair parameters for our query such as conditions, bind, limit, offset, join, order, and sort. The default value is an empty array.
bool|string $class

A default value of false, it contains the name of the class we will build based on the name of a model.

Return Value

array|object|bool

An associative array of results returned from an SQL query.

at line 288
int findTotal(string $table, array $params = [])

Returns number of records in a table.

Parameters

string $table

The name or the table we want to perform our query against.
array $params

An associative array that contains key value pair parameters for our query such as conditions, bind, limit, offset, join, order, and sort. The default value is an empty array.

Return Value

int

The number of records in a table.

at line 302
array|object first()

Returns first result in the _result array.

Return Value

array|object

An associative array that is the first object in a _result.

at line 314
array getColumns(string $table)

Returns columns for a table.

Parameters

string $table

The name of the table we want to retrieve the column names.

Return Value

array

An array of objects where each one represents a column from a database table.

at line 330
static DB getInstance()

An instance of this class set as a variable. To be used in other class because we can't use $this.

Return Value

DB

The instance of this class.

at line 342
PDO getPDO()

Returns instance of PDO class.

Return Value

PDO

The PDO object.

at line 355
static string|null groupByColumn(string $column)

Appropriately formats column for query with GROUP BY operations. A call to the ANY_VALUE function is added if the DB driver is MySQL or MariaDB.

Parameters

string $column

Name of the column to format.

Return Value

string|null

The properly formatted column if DB driver is properly set or detected. Otherwise, we return null.

at line 385
bool insert(string $table, array $fields = [])

Perform insert operations against the database.

Example setup: $fields = [ 'fname' => 'John', 'lname' => 'Doe', 'email' => 'example@email.com' ]; $contacts = $db->insert('contacts', $fields);

Parameters

string $table

The name of the table we want to perform the insert operation.
array $fields

An associative array of key value pairs. The key is the name of the database field and the value is the value we will set to a particular field. The default value is an empty array.

Return Value

bool

Report whether or not the operation was successful.

at line 415
bool inTransaction()

Checks if a transaction is currently active.

Return Value

bool

True if a transaction is active, false otherwise

at line 424
int|string|null lastID()

The primary key ID of the last insert operation.

Return Value

int|string|null

The primary key ID from the last insert operation.

at line 442
DB query(string $sql, array $params = [], bool|string $class = false)

Performs database query operations that includes prepare, binding, execute, and fetch.

Parameters

string $sql

The database query we will submit to the database.
array $params

An associative array that contains key value pair parameters for our query such as conditions, bind, limit, offset, join, order, and sort. The default value is an empty array.
bool|string $class

A default value of false, it contains the name of the class we will build based on the name of a model.

Return Value

DB

The results of the database query. If the operation is not successful the $_error instance variable is set to true and is returned.

at line 493
protected bool _read(string $table, array $params = [], bool|string $class = false, bool $count = false)

Supports SELECT operations that maybe ran against a SQL database. It supports the ability to order and limit the number of results returned from a database query. The user can use parameters such as conditions, bind, order, limit, and sort.

Parameters

string $table

The name of the table that contains the record(s) we want to find.
array $params

An associative array that contains key value pair parameters for our query such as conditions, bind, limit, offset, join, order, and sort. The default value is an empty array.
bool|string $class

A default value of false, it contains the name of the class we will build based on the name of a model.
bool $count

Boolean switch for turning on support for count operations. Default value is false.

Return Value

bool

A true or false value depending on a successful operation.

at line 599
array results()

Returns all rows from the last query

Return Value

array

An array of objects that contain results of a database query.

at line 608
bool rollBack()

Rolls back the current database transaction.

Return Value

bool

True on success, false on failure

at line 623
bool tableExists(string $table)

Checks whether a given table exists in the currently connected database.

This method runs a driver-specific query to determine if the table is present. For SQLite, it queries the sqlite_master system table. For MySQL/MariaDB, it uses the SHOW TABLES LIKE statement.

Parameters

string $table

The name of the table to check for existence.

Return Value

bool

Returns true if the table exists in the database, or false if it does not.

at line 652
bool update(string $table, int $id, array $fields = [])

Performs update operation on a SQL database record.

Example setup: $fields = [ 'fname' => 'John', 'email' => 'example@email.com' ]; $contacts = $db->update('contacts', 3, $fields);

Parameters

string $table

$table The name of the table that contains the record we want to update.
int $id

The primary key for the record we want to remove from a database table.
array $fields

The value of the fields we want to set for the database record. The default value is an empty array.

Return Value

bool

True if the update operation is successful. Otherwise, we return false.

at line 667
int updateWhere(string $table, array $fields, array $params = [])

Updates records in a table using params-style conditions.

Parameters

string $table

The table to update.
array $fields

Key/value pairs to set.
array $params

Params like ['conditions' => 'queue = ?', 'bind' => [$queueName]]

Return Value

int

Number of rows affected.

at line 703
bool valueExistsInColumn(string $table, string $column, mixed $value)

Check if a value exists in a JSON or text-based column

Parameters

string $table

The table name
string $column

The column name (JSON or text-based)
mixed $value

The value to search for

Return Value

bool

True if value exists, False otherwise