• Database
  • Introduction
  • Connections
  • Queries
    • Prepared Statements
      • Binding Parameters
    • Single Function Calls
  • Results
    • Fetching
    • Result Exists
    • Type Hinting
    • Affected Rows
    • Total Rows
    • Page Sets
    • Caching
  • Shortcuts

Database OSC\OM\Db

Introduction

The Db class manages the connection to the database server and executes sql queries. It extends the native PHP PDO class with custom functionality optimized to the framework.

The class executes sql queries safely and securely by binding parameter values to the query via placeholders rather then having the values being injected into the sql query itself.

Connections

Db::initialize() opens a new connection to the database server. All parameters of the function are optional where the installation configuration values are used as default values.

use OSC\OM\Db;

$OSCOM_Db = Db::initialize();

Parameters

Db::initialize($server, $username, $password, $database, $port, array $driver_options)

Queries

Prepared Statements

Queries are performed with Db::prepare() which securely binds values to the query using placeholders.

$seach = 'chocolate';
$category_id = 1;
$price = '4.99';

$Qproducts = $OSCOM_Db->prepare('select title from :table_products where description like :description and category_id = :category_id and status = :status and price < :price order by title');
$Qproducts->bindValue(':description', '%' . $chocolate . '%');
$Qproducts->bindInt(':category_id', $category_id);
$Qproducts->bindBool(':status', true);
$Qproducts->bindDecimal(':price', $price);
$Qproducts->execute();

while ($Qproducts->fetch()) {
    echo $Qproducts->value('title');
}

Binding Parameters

Parameters can be binded to the query using the following functions:

Single Function Calls

Select Queries

Simple select queries that do not need parameters to be binded can be executed with Db::query(). This functions returns a normal result set.

$Qstates = $OSCOM_Db->query('select id, title from :table_states where country_id = 1 order by title');

while ($Qstates->fetch()) {
    echo $Qstates->value('title');
}

Update/Delete Queries

Simple update/delete queries that do not need parameters to be binded can be executed with Db::exec(). This functions returns the number of rows affected.

$result = $OSCOM_Db->exec('delete from :table_states where country_id = 1');

echo 'Affected rows: ' . $result;

Results

Results can be returned as a single result set, a multiple result set, and as an array containing all rows or columns.

Fetching

Single Result Set

Returning a single result set is performed as:

$Qstate = $OSCOM_Db->prepare('select title from :table_states where id = :id');
$Qstate->bindInt(':id', 1);
$Qstate->execute();

if ($Qstate->fetch() !== false) {
    echo 'State: ' . $Qstate->value('title');
}

Multiple Result Set

Returning a multiple result set is performed as:

$Qstates = $OSCOM_Db->prepare('select id, title from :table_states where country_id = :country_id');
$Qstates->bindInt(':country_id', 1);
$Qstates->execute();

while ($Qstates->fetch()) {
    echo 'State: ' . $Qstates->value('title');
}

Array Result Set

An array can be retrieved containing either all rows of the result set or all columns of the current row:

$Qstates = $OSCOM_Db->prepare('select id, title from :table_states where country_id = :country_id');
$Qstates->bindInt(':country_id', 1);
$Qstates->execute();

$states_all = $Qstates->fetchAll();

$current_state = $Qstates->toArray();

Result Exists

Checking to see if a result exists is performed as:

$Qstates = $OSCOM_Db->prepare('select id, title from :table_states where country_id = :country_id');
$Qstates->bindInt(':country_id', 1);
$Qstates->execute();

if ($Qstates->fetch() !== false) {
    echo 'States:';

    do {
        echo $Qstates->value('title');
    } while ($Qstates->fetch());
} else {
    echo 'No states exist.';
}

$Qstates = $OSCOM_Db->prepare('select id, title from :table_states where country_id = :country_id');
$Qstates->bindInt(':country_id', 1);
$Qstates->execute();

if ($Qstates->fetch() !== false) {
    echo 'States:';

    while ($Qstates->fetch()) {
        echo $Qstates->value('title');
    }
}

Type Hinting

Columns can be returned as a specific variable type using the following functions:

$Qproducts = $OSCOM_Db->prepare('select id, title, code, price from :table_products where description like :description order by title');
$Qproducts->bindValue(':description', '%chocolate%');
$Qproducts->execute();

if ($Qproducts->fetch() !== false) {
    do {
        echo $Qproducts->valueInt('id') . ': ' . $Qproducts->valueProtected('title') . ' (' . $Qproducts->value('code') . ') = ' .
             $Qproducts->valueDecimal('price');
    } while ($Qproducts->fetch());
}

Affected Rows

The number of rows affected by an insert, update, or delete query can be returned as:

$Qupdate = $OSCOM_Db->prepare('update :table_states set title = :title where id = :id');
$Qupdate->bindValue(':title', 'Beverly Hills');
$Qupdate->bindInt(':id', 1);
$Qupdate->execute;

echo 'Affected rows: ' . $Qupdate->rowCount();

Total Rows

Retrieving the total rows of a query can be performed as:

$Qtotal = $OSCOM_Db->prepare('select SQL_CALC_FOUND_ROWS id from :table_orders where status = :status');
$Qtotal->bindBool(':status', true);
$Qtotal->execute();

echo 'Total rows: ' . $Qtotal->getPageSetTotalRows();

It is also possible to use fetchAll() however this method uses more server resources and is not recommended:

$Qorders = $OSCOM_Db->prepare('select id from :table_orders where status = :status');
$Qorders->bindBool(':status', true);
$Qorders->execute();

echo 'Total rows: ' . count($Qtotal->fetchAll());

Page Sets

Returning a page set result is performed as:

$Qorders = $OSCOM_Db->prepare('select SQL_CALC_FOUND_ROWS order_number, total_price from :table_orders where customer_id = :customer_id and status = :status order by id desc limit :page_set_offset, :page_set_max_results');
$Qorders->bindInt(':customer_id', 1);
$Qorders->bindBool(':status', true);
$Qorders->setPageSet(15);
$Qorders->execute();

if ($Qorders->getPageSetTotalRows() > 0) {
    echo 'Orders';

    while ($Qorders->fetch()) {
        echo 'Order #' . $Qorders->valueInt('order_number') . ': ' . $Qorders->valueDecimal('total_price');
    }

    echo $Qorders->getPageSetLabel('Displaying <strong>{{listing_from}}</strong> to <strong>{{listing_to}}</strong> (of <strong>{{listing_total}}</strong> orders)');

    echo $Qorders->getPageSetLinks();
}

Parameters

setPageSet($max_results, $page_set_keyword, $placeholder_offset, $placeholder_max_results)

Caching

Caching of select query result sets improves performance by storing the result of the query in a cache file and subsequently reading the cached data until the cache expiration time is reached. As soon as the cache expiration time is reached, the database is queried again and the cached information is refreshed with the new result set.

$Qcfg = $OSCOM_Db->prepare('select key, value from :configuration');
$Qcfg->setCache('configuration');
$Qcfg->execute();

while ($Qcfg->fetch()) {
    echo $Qcfg->value('key') . ': ' . $Qcfg->value('value');
}

Parameters

setCache($key, $expire, $cache_empty_results)

Shortcuts

Shortcut functions wrap Db::prepare() into a simpler interface to help write code faster for simpler queries.

Db::get()

Db::get() can be used to retrieve rows from a simple query.

$Qstates = $OSCOM_Db->get('states', [
    'id',
    'title'
], [
    'country_id' => 1
], 'title');

while ($Qstates->fetch()) {
    echo $Qstates->value('title');
}

Parameters

Db::get($table, $fields, array $where, $order, $limit, $cache, array $options)

['countries as c', 'states as s']

['c.countries_id as id', 'c.countries_title as title']

['id' => 1]

['title', 'c.date_added']

[1, 15]

['prefix_tables' => true]

A more complex multi-relationship query example can be performed as:

$Qproducts = $OSCOM_Db->get([
    'products p',
    'products_to_categories p2c'
], [
    'count(*) as total'
], [
    'p.products_id' => [
        'rel' => 'p2c.products_id'
    ],
    'p.products_status' => '1',
    'p2c.categories_id' => '1'
]);

$products_count = $Qproducts->valueInt('total');

Db::save()

Db::save() can be used to insert or update data in a table.

$result = $OSCOM_Db->save('states', [
    'title' => 'California'
], [
    'id' => 1
]);

echo 'Affected rows: ' . $result;

Parameters

Db::save($table, array $data, array $where_condition, array $options)

['prefix_tables' => true]

Db::delete()

Db::delete() can be used to delete a single, multiple, or all records from a table.

$result = $OSCOM_Db->delete('states', [
    'id' => 1
]);

echo 'Affected rows: ' . $result;

Parameters

Db::delete($table, array $where_condition, array $options)

['prefix_tables' => true]