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