Database Framework

BIGACE comes with a database abstraction layer, that can be switched using the Services Framework.

You can access it, using the global object:

:::php
$GLOBALS['_BIGACE']['SQL_HELPER']

Please note: Fetching CMS specific data should always be done by using the the proper PHP API methods.

Load a Statement

Load a Statement from the folder /system/sql/ by passing its name (for example foo.sql) without the extension:

:::php
$sql = $GLOBALS['_BIGACE']['SQL_HELPER']->loadStatement('foo');

Prepare Statements

If you use replacer in your Statement, you have to prepare the Statement for execution.

:::php
$sql = "SELECT * FROM {DB_PREFIX}user WHERE cid={CID} AND id={USER_ID} AND name={NAME}";
$values = array('USER_ID' => '123', 'NAME' => 'admin');

$sql = $GLOBALS['_BIGACE']['SQL_HELPER']->prepareStatement($sql, $values, true);

The last call gives you the ready to execute SQL Statement:

:::php
$result = $GLOBALS['_BIGACE']['SQL_HELPER']->execute($sql);

You see the two replacer {DB_PREFIX} and {CID}? We did not pass values for those. But why?!?

The answer is easy, these are auto-global values, that will be fetched from the system environment and automatically replaced.

Escaping

The third parameter (a boolean) in the prepareStatement() call indicates, wheter the values will be escaped or not.

You might skip this parameter (default is false), when escaping would cause trouble.

For security reason, it is recommended to use "true", that way the DB Framework handles the escaping.

Executing Statements

Execute a Stement by calling:

:::php
$result = $GLOBALS['_BIGACE']['SQL_HELPER']->execute($sql);
$id = $GLOBALS['_BIGACE']['SQL_HELPER']->insert($sql); // for auto_increment values

Reading results

Count results:

:::php
$amount = $result->count();

Fetch first result:

:::php
$first = $result->next();

Loop through results:

:::php
$amount = $result->count();
for($i=0; $i < $amount; $i++) {
  $current = $result->next();
}