Best Practices for the Class Db with PrestaShop v1.5
By Raphaël Malié - Core Developer, PrestaShop
Most modules and developments on PrestaShop require you to use or enter information on a database. Any developer sees the core DB class as an essential part of the process.
On top of providing potential abstraction for other types of relational database, the DB class has several tools to make life simpler!
Learn about the various methods, when to use them and the best practices for the development stage.
A similar article was written about the usage of the 1,4, and this article will present in the same way but for the 1,5 version. At the bottom of the page, you will also find the main differences between the 1,4 and 1,5 as far as these classes are concerned.
The Class Basics
The DB Class is actually made up of two classes:
- DB class in the file ~/classes/Db.php is abstract
- A daughter class extending the Db Class. Currently, three abstractions are possible as daughter class : MySQL, MySQLi and PDO. PDO is used as default, but if the extension is not installed with your host, MySQLi will be used, and if neither of these two are installed, the default will be MySQL.
Although DB is a pseudo-singleton, it can still be activated manually if necessary as the developer is public. However, in PrestaShop it must be accessed as follows:
$db = Db::getInstance();
In some cases you may see requests as per the following code:
$db = Db::getInstance(_PS_USE_SQL_SLAVE_);
When the above is connected, it could enter slave servers if the PrestaShop user allows the use of slave MySQL servers in their architecture. The standard _PS_USE_SQL_SLAVE_ argument should only be used for read-only queries (SELECT, SHOW etc.), and only if a result does not need to be immediately updated. It is necessary to use the master server to make a select query just after entering something on the same table.
The different methods
1. The insert($table, $data, $null_values = false, $use_cache = true, $type = Db::INSERT, $add_prefix = true) Method
This method automatically generates the insertion or update of the base from a table of data. This method should be used instead of making INSERT or UPDATE requests unless these requests are slightly complex (using SQL functions, intersect queries etc.). The benefit of using one method to do everything is to centralise requests. You can edit this method using PrestaShop’s 1.4 override system when there is a particular process to apply to tables during insertion.
Dummy example:
$target = Tools::getValue('id');
$name = Tools::getValue('name');
Db::getInstance()->insert('target_table', array(
'id_target' => (int)$target,
'name' => pSQL($name),
));
Requesting this method results in the following SQL query:
INSERT INTO `prefix_target_table` (`id_target`, `name`) VALUES (10, 'myName')
The arguments of this method are as follows :
- $table: table name (the prefix will automatically be inserted, you needn’t specify)
- $data: table containing the data to be inserted
- $null_values: if "true", then the NULL values will be inserted similarly into the base
- $use_cache: if "false", deactivates the PrestaShop SQL cache management for this request (do not change this setting unless you know exactly what you’re doing)
- $type: can take the constants Db::INSERT, Db::INSERT_IGNORE or Db::REPLACE if you wish to change the type of insertion.
- $add_prefix: if "false", will not automatically add table prefixes before table name.
Important:
Always ensure that your data is protected before transferring them to insert().
In the example, the id_target must be an integer and the name must be protected against SQL injections with pSQL()
2. The update ($table, $data, $where = '', $limit = 0, $null_values = false, $use_cache = true, $add_prefix = true) method
This method does the same as insert(), but for data updates (UPDATE requests). The arguments are mainly the same, with just two that differ :
- $where: clause WHERE for the update
- $limit: you can limit the number of results to update.
3. The delete method ($table, $where = '', $limit = 0, $use_cache = true, $add_prefix = true)
This method is the DELETE version of insert() and update() It can be used for the same purpose. The $limit argument limits the number of saved items you can delete. The other benefit of this method is that it can be used with PrestaShop’s SQL query cache system and deletes the cached queries unless the $use_cache argument is false.
Example:
Db::getInstance()->delete(‘target_table’, ‘myField < 15’, 3);
will generate the following query
DELETE FROM `prefix_target_table` WHERE myField < 15 LIMIT 3
4. The Execute($sql, $use_cache = 1) method
This method executes the given SQL query. It should only be used for write-only queries (INSERT, UPDATE, DELETE, TRUNCATE etc.) as it also deletes the query cache (unless the $use_cache argument is false).
Example:
$sql = ‘DELETE FROM ‘._DB_PREFIX_.’product WHERE active = 0’;
if (!Db::getInstance()->execute($sql))
die(‘Erreur etc.)’;
Important:
It is recommended to use the insert(), update() and delete() methods as much as possible, only use execute() if the requests are too complex.
Also note that this method returns a boolean (true or false), not a database resource that can be used.
5. The query($sql,) method
All the Class methods running SQL requests use this very basic method. It does the same as the execute() method, with only two differences ;
- There is no cache management in this method
- Rather than a boolean, it will provide a resource that you can use with other methods such as nextRow()
6. The executeS($sql, $array = true, $use_cache = 1) method
This method executes the given SQL query and and loads all the results on a multidimensional table. It should not be used with read-only queries (SELECT, SHOW etc.).The query results will be cached unless the argument $use_cache is false. The second argument $array is depreciated and should not be used, leave it as true.
Example:
$sql = ‘SELECT * FROM ‘._DB_PREFIX_.’shop’;
if ($results = Db::getInstance()->ExecuteS($sql))
foreach ($results as $row)
echo $row[‘id_shop’].’ :: ‘.$row[‘name’].’
’;
7. The getRow($sql, $use_cache = 1) method
This method executes the given SQL query and collects the first line of results. It should only be used with read-only queries (SELECT, SHOW, etc.). The query results will be cached unless the argument $use_cache is false.
Warning: this method automatically adds a LIMIT clause to the query. Ensure that you do not add one manually.
Example:
$sql = ‘SELECT * FROM ‘._DB_PREFIX_.’shop
WHERE id_shop = 42’;
if ($row = Db::getInstance()->getRow($sql))
echo $row[‘id_shop’].’ :: ‘.$row[‘name’];
8. The getValue($sql, $use_cache = 1) method
This method executes the given SQL query and collects the first result of the first line only. It should only be used with read-only queries (SELECT, SHOW, etc.). The query results will be cached unless the argument $use_cache is false.
Warning: this method automatically adds a LIMIT clause to the query. Ensure that you do not add one manually.
Example:
$sql = ‘SELECT COUNT(*) FROM ‘._DB_PREFIX_.’shop’;
$totalShop = Db::getInstance()->getValue($sql);
9. The NumRows() method
This method caches and displays the number of results from the last SQL query.
Warning: this method is not depreciated but we strongly advise you not to use it for reasons of best practice. It is actually better to collect the number of results via a SELECT COUNT(*) query beforehand.
10. A few other methods
- Insert_ID(): displays the ID created by the last executed INSERT query
- Affected_Rows(): displays the number of lines affected by the last executed UPDATE or DELETE query
- getMsgError(): displays the last error message if a query has failed
- getNumberError(): displays the last error number if a query has failed
11. Changes between the 1.4 and 1.5 versions.
- The autoExecute() () and autoExecuteWithNullValues() methods have been replaced with insert() and update()
- Table prefixes are no longer compulsory for the delete() method
- The execute() method no longer displays a resource but rather a boolean, use query() to obtain a resource.
- PDO and MySQLi support.
