Search the Community

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'); }