Table of content
| Table of Contents | ||
|---|---|---|
|
...
DB class best practices
Most of the time, creating a module or overriding PrestaShop means using or inserting data in the database. Knowing how to properly use the DB core class is therefore mandatory for developers. Besides providing you with an abstraction for other potential database system, the DB class offers several tools to make your life easier.
...
- The
Dbclass, which can found in the/classes/db/Db.php, and is abstracted. - A subclass which extends the
Dbclass. Currently, three class abstractions are supported as subclasses: MySQL, MySQLi and PDO.
PDF PDO is used by default; however, if the PDF PDO extension is not installed on the server, the MySQLi extension is used instead. And if MySQLi is not installed either, then MySQL is used.
DB is a pseudo-singleton, as it can still be manually instantiated, because its constructor is public. However, within PrestaShop, it is recommended to instantiate it this way:
| Code Block |
|---|
$db = Db::getInstance();
|
In some cases, you might encounter this alternative:
| Code Block |
|---|
$db = Db::getInstance(_PS_USE_SQL_SLAVE_);
|
...
Fictitious example:
| Code Block |
|---|
$target = Tools::getValue('id');
$name = Tools::getValue('name');
Db::getInstance()->insert('target_table', array(
'id_target' => (int)$target,
'name' => pSQL($name),
));
|
Triggering this code generates the following SQL query:
| Code Block |
|---|
INSERT INTO `prefix_target_table` (`id_target`, `name`) VALUES (10, 'myName')
|
...
The $limit parameter enables you to limit the number of records to that you wish to delete. The other advantage of this method is that it will be used by PrestaShop's SQL queries cache system, and will therefore delete the affected queries in cache, unles the $use_cache is false.
Example:
| Code Block |
|---|
Db::getInstance()->delete('target_table', 'myField < 15', 3);
|
...will generate the following query:
| Code Block |
|---|
DELETE FROM `prefix_target_table` WHERE myField < 15 LIMIT 3
|
...
This method executes the given SQL query. It should only be used for 'write' queries (INSERT, UPDATE, DELETE, TRUNCATE, etc.), because it also deletes the query cache (unles $use_cache is set to false).
Example:
| Code Block |
|---|
$sql = 'DELETE FROM '._DB_PREFIX_.'product WHERE active = 0';
if (!Db::getInstance()->execute($sql))
die('Erreur etc.)';
|
| Tip |
|---|
You should use |
...
This method executes a given SQL query, and makes that whole resulting data available through a multidimensional array. It should only be used for 'read' queries (SELECT, SHOW, etc.). The query's results are cached, unless the $use_cache parameter is set to false. The second parameter, $array(), is deprecated and should not be used, leave it as true.
Example:
| Code Block |
|---|
$sql = 'SELECT * FROM '._DB_PREFIX_.'shop';
if ($results = Db::getInstance()->ExecuteS($sql))
foreach ($results as $row)
echo $row['id_shop'].' :: '.$row['name'].'<br />';
|
...
| Warning |
|---|
This method automatically adds a LIMIT clause to the query. Be careful not to add one manually. |
Example:
| Code Block |
|---|
$sql = 'SELECT * FROM '._DB_PREFIX_.'shop
WHERE id_shop = 42’;
if ($row = Db::getInstance()->getRow($sql))
echo $row['id_shop'].' :: '.$row['name'];
|
...
| Warning |
|---|
This method automatically adds a LIMIT clause to the query. Be careful not to add one manually. |
Example:
| Code Block |
|---|
$sql = 'SELECT COUNT(*) FROM '._DB_PREFIX_.'shop';
$totalShop = Db::getInstance()->getValue($sql);
|
| Note |
|---|
|
NumRows()
This method caches and returns the number of results from the most recent SQL query;
...