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