Jump to content

Database

ClicShopping

Recommended Posts

ClicShopping Contributor

ClicShopping

Posted

Database ClicShopping\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 ClicShopping\OM\Db;

$CLICSHOPPING_Db = Db::initialize();

Parameters 

Db::initialize($server, $username, $password, $database, $port, array $driver_options)
Parameter     Value
$server                     The address of the database server. Default: db_server
$username             The username to connect to the database server with. Default: db_server_username
$password             The password of the user account. Default: db_server_password
$database             The name of the database. Default: db_database
$port                     The port number of the database server. Default: null
$driver_options     Additional driver options to use for the database connection. Defaults:

PDO::ATTR_ERRMODE

    PDO::ERRMODE_WARNING
PDO::ATTR_DEFAULT_FETCH_MODE

    PDO::FETCH_ASSOC
PDO::ATTR_STATEMENT_CLASS

    ClicShopping\OM\DbStatement
PDO::MYSQL_ATTR_INIT_COMMAND

    set session sql_mode="STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

A database connection is created on each page request and is available in the Registry as Db.

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 = $CLICSHOPPING_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: 

Value Type     Function
String             bindValue
Integer             bindInt
Boolean             bindBool
Decimal             bindDecimal
Null                     bindNull
[code]

Table names prefixed with [b]:table_[/b] are binded and prefixed automatically with db_table_prefix.

[b]Single Function Calls[/b]

[b]Select Queries[/b]

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

[code]
$Qstates = $CLICSHOPPING_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 = $CLICSHOPPING_Db->exec('delete from :table_states where country_id = 1');
echo 'Affected rows: ' . $result;
[code]

[b]Results[/b]

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

[b]Single Result Set[/b]

Returning a single result set is performed as:

[code]
$Qstate = $CLICSHOPPING_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 = $CLICSHOPPING_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 = $CLICSHOPPING_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 = $CLICSHOPPING_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.';
}

 

Please note that the following will not work:

  

$Qstates = $CLICSHOPPING_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');
    }
}

 

as calling fetch() in the if statement to check if a row exists and looping through the results again in the while statement will skip the first row of the result set due to the first call to fetch(). The do { .. } while ( .. ) method shown above is the correct way.

Type Hinting

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

Value Type             Function
String                     value
HTML Safe String     valueProtected
Integer                     valueInt
Decimal                     valueDecimal

 

  

$Qproducts = $CLICSHOPPING_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());
}
[code]

[b]Affected Rows[/b]

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

[code]
$Qupdate = $CLICSHOPPING_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();

 

Please do not use rowCount() for select queries as this is not supported by PDO.

Total Rows

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

  

$Qtotal = $CLICSHOPPING_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();

 

getPageSetTotalRows() requires SQL_CALC_FOUND_ROWS to exist in the query and automatically retrieves the total rows using select found_rows() after the query has been executed.

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

  

$Qorders = $CLICSHOPPING_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 = $CLICSHOPPING_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)

 

  

Parameter                         Value
$max_results                             The number of results to show per page.
$page_set_keyword                     The name of the parameter holding the current page value. Default: page
$placeholder_offset                     The name of the binding placeholder used as the limit offset in the sql query. Default: page_set_offset
$placeholder_max_results     The name of the binding placeholder used as the limit row number in the sql query. Default: page_set_max_results

 

The parameter name of the current page value is passed as the second parameter. The default value is page and the value is retrieved from $_GET['page'] if it exists.

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 = $CLICSHOPPING_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)

 

  

Parameter                           Value
$key                              The name of the cache block to retrieve or save.
$expire                              The time in minutes the cached data should be saved for. A value of 0 keeps the cached data indefinitly until it has been manually cleared. Default: 0
$cache_empty_results     A boolean value to cache or not cache empty result sets. Default: false

 

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 = $CLICSHOPPING_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)

 

  

Parameter     Value
$table     One (string) or more tables (array) to retrieve the rows from. Aliases may be used as:
['countries as c', 'states as s']

Table names are automatically prefixed unless the prefix_tables option is set as false (see the $options parameter).
$fields     One (string) or more fields (array) to retrieve. Aliases may be used as:
['c.countries_id as id', 'c.countries_title as title']

$where     Array containing keys and values matching the column name to the condition:
['id' => 1]

$order     One (string) or more fields (array) to sort by:
['title', 'c.date_added']

$limit     An integer value to limit the number of rows to, or an array containing two integer values to limit the number of rows (second value) with an offset (first value):
[1, 15]

$cache     An array consisting of the parameters (in order) sent to setCache().
$options     An array containing the following options:

['prefix_tables' => true]

 

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

  

$Qproducts = $CLICSHOPPING_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 = $CLICSHOPPING_Db->save('states', [
                                            'title' => 'California'
                                        	], [
                                            'id' => 1
                                        	]
                                );

echo 'Affected rows: ' . $result;

 

Parameters

  

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

Parameter     Value
$table     The table to save the data to.
$data     An associative key=>value array containing the data to save in the table. The array keys must match the table field names the array value should be saved in.
$where_condition     If no condition is passed, the data is inserted into the table as a new record. If an associative $key=>$value array is passed, it is used as the where condition of the query to update the data of an existing record.
$options     An array containing the following options:

['prefix_tables' => true]

Db::delete()

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

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

echo 'Affected rows: ' . $result;

 

Parameters 

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

  

Parameter     Value
$table     The table to delete the records from.
$where_condition     If no condition is passed, all records in the table are deleted. If an associative $key=>$value array is passed, it is used as the where condition of the query to delete the matching records. The array keys must match the table field names the array value is matched against.
$options     An array containing the following options:
['prefix_tables' => true]


use Cache 

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

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


get information from Db 

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

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

  

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

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

 

Link to post
Guest
This topic is now closed to further replies.
Go to topic listing
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use

  I accept