Table of content

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.

This page explains the various methods, the contexts in which they should be used, and the development best practices.

At the bottom of the page are the main differences in the DB class between version 1.4 and 1.5 of PrestaShop.

Fundamentals

The DB class is really made of two classes:

• The Db class, which can found in the /classes/db/Db.php, and is abstracted.
• A subclass which extends the Db class. Currently, three class abstractions are supported as subclasses: MySQL, MySQLi and PDO.
  PDO is used by default; however, if the 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:

$db = Db::getInstance();

In some cases, you might encounter this alternative:

$db = Db::getInstance(_PS_USE_SQL_SLAVE_);

If PrestaShop's database user allows the use of MySQL slave servers in its architecture, then this last instance's connection can be done on the slave servers.
You should only use the PS_USE_SQL_SLAVE argument when making read-only queries (SELECT, SHOW, etc.), and only if these do not need a result to be immediately updated with a result. If you make a query on a table right after inserting data in that same table, you should make that query on the master server.

The available methods

insert()

Method signature: insert($table, $data, $null_values = false, $use_cache = true, $type = Db::INSERT, $add_prefix = true).

This method was created to automatically generate data insertion in the database, from a data table. It should be used instead of doing INSERT queries, unless these queries are rather complex (use of SQL functions, nested queries, etc.).

Building every query using one method allows you to centralize your calls. If one day you need to perform a specific processing on some tables during data insertion, you can do so by overloading this method using PrestaShop's overriding system.

Fictitious example:

$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:

INSERT INTO `prefix_target_table` (`id_target`, `name`) VALUES (10, 'myName')

Method parameters

update()

Method signature: update($table, $data, $where = '', $limit = 0, $null_values = false, $use_cache = true, $add_prefix = true)

This method works as the insert() method does, but for data update (UPDATE queries). Both have roughly the same parameters, with type gone and these two additions:

delete()

Method signature: delete($table, $where = '', $limit = 0, $use_cache = true, $add_prefix = true).

This method is an equivalent to insert() and update(), only for DELETE queries. You should use it for the same reasons.

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:

Db::getInstance()->delete('target_table', 'myField < 15', 3);

...will generate the following query:

DELETE FROM `prefix_target_table` WHERE myField < 15 LIMIT 3

execute()

Method signature: execute($sql, $use_cache = 1).

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:

$sql = 'DELETE FROM '._DB_PREFIX_.'product WHERE active = 0';
if (!Db::getInstance()->execute($sql))
	die('Erreur etc.)';

query()

Method signature: query($sql).

All the method of the DB classes that make SQL query use the query() as the common, low-level method. It does the same as the execute() method, with two exceptions:

• No cache control management.
• Will not return a boolean; instead returns a database resource that you can use with other DB class methods, such as nextRow().

executeS()

Method signature: executeS($sql, $array = true, $use_cache = 1).

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:

$sql = 'SELECT * FROM '._DB_PREFIX_.'shop';
if ($results = Db::getInstance()->ExecuteS($sql))
	foreach ($results as $row)
		echo $row['id_shop'].' :: '.$row['name'].'<br />';

getRow()

Method signature: getRow($sql, $use_cache = 1).

This method executes a given SQL query and retrieves the first row of results. It should only be used with 'read' queries (SELECT, SHOW, etc.). The query's results are cached, unless the $use_cache parameter is set to false.

Example:

$sql = 'SELECT * FROM '._DB_PREFIX_.'shop
	WHERE id_shop = 42’;
if ($row = Db::getInstance()->getRow($sql))
	echo $row['id_shop'].' :: '.$row['name'];

getValue()

Method signature: getValue($sql, $use_cache = 1).

This method executes a given SQL query and retrieves the first value of the first row of results. It should only be used with 'read' queries (SELECT, SHOW, etc.). The query's results are cached, unless the $use_cache parameter is set to false.

Example:

$sql = 'SELECT COUNT(*) FROM '._DB_PREFIX_.'shop';
$totalShop = Db::getInstance()->getValue($sql);

NumRows()

This method caches and returns the number of results from the most recent SQL query;

A few other methods

• Insert_ID(): returns the ID created during the latest INSERT query.
• Affected_Rows(): returns the number of lines impacted by the latest UPDATE or DELETE query.
• getMsgError(): returns the latest error message, if the query has failed.
• getNumberError(): returns the latest error number, if the query has failed.

Changes between PrestaShop 1.4 and 1.5

• The autoExecute() and autoExecuteWithNullValues() are deprecated. You should replace them with insert() and update(), respectively.
• The table prefix is no longer mandatory for the delete() method.
• The execute() method does not return a SQL resource but a boolean value. Use query() to get a resource.
• PDO and MySQLi support.