LogoLogo
Homepage
  • Documentations for PrestaShop 1.5
  • English documentation 1.5
    • About PrestaShop 1.5
    • New features in PrestaShop 1.5
    • Getting Started
      • What you need to get started
      • Installing PrestaShop
      • Installing PrestaShop using the command line
      • Updating & Uninstalling
      • Misc. information
    • Updating PrestaShop
      • Automatic update
      • Manual update
      • Making and restoring your own backup
      • In case of issues
    • User Guide
      • Training
      • Customizing your shop
      • Browsing the front-office
      • Connecting to the PrestaShop back-office
      • First steps with PrestaShop 1.5
      • Adding Products and Product Categories
      • A Look Inside the Catalog
      • Managing Orders
      • Managing Customers
      • Creating Price Rules and Vouchers
      • Managing Shipping
      • Understanding Local Settings
      • Managing Modules and Themes
      • Making the Native Modules Work
      • Understanding the Preferences
      • Understanding the Advanced Parameters
      • Administering the Back-Office
      • Understanding Statistics
      • Advanced Stock Management
      • Managing Multiple Shops
    • Developer Guide
      • Developer tutorials
        • Using the PrestaShop Web Service
          • Webservice one-page documentation
          • Chapter 1 - Creating Access to Back Office
          • Chapter 2 - Discovery - Testing access to the web service with the browser
          • Chapter 3 - First steps - Access the Web service and list client
          • Chapter 4 - Retrieve Data - Retrieving a Client
          • Chapter 5 - Modification - Update client
          • Chapter 6 - Creation - Remote Online Form
          • Chapter 7 - Removal - Remove customer accounts from the database
          • Chapter 8 - Advanced Use
          • Chapter 9 - Image management
          • Chapter 10 - Price management
          • Cheat-sheet - Concepts outlined in this tutorial
          • Web service reference
        • Helpers
          • HelperForm
          • HelperOptions
          • HelperList
          • Using helpers to overload a back-office template
        • New Developers Features In PrestaShop 1.5
        • Using the Context Object
        • DB class best practices
        • Public and overloadable methods
        • Overriding default behaviors
        • Using addJquery(), addJqueryPlugin() and addJqueryUI()
        • Handling special characters in links
        • Auto-updating modules
        • Front-Office Controllers
        • Controllers correspondence table
        • Specifics of multistore module development
        • Developer tips and tricks
        • PrestaShop's developer tools
        • Using the backward compatibility toolkit
      • Fundamentals
      • Coding Standards
      • Setting up your local development environment
      • Diving into PrestaShop Core development
      • Creating a PrestaShop module
      • Creating a front-office module
      • Creating a module with both front-end and back-end controllers
      • Hooks in PrestaShop 1.5
      • Translations in PrestaShop 1.5
      • How to use the Forge to contribute to PrestaShop
      • How to write a commit message
      • Contributing code to PrestaShop
    • Designer Guide
      • Theme development fundamentals
      • Integrating content in a page using hooks
      • Characteristics of a front-office theme
      • Characteristics of a back-office theme
      • Creating your own theme
      • Theme templates and Smarty
      • Using jQuery and Ajax
      • Best practices
      • Designer tutorials
        • Changes in version 1.5 which impact theme development
        • Changing a 1.4 theme to support gift products
        • Implementing layered navigation in a theme
    • System Administrator Guide
    • Merchant's Guide
      • Our advices
      • Sample price rules
    • FAQ
      • Using PrestaShop with WordPress
    • Troubleshooting
  • Documentation française 1.5
    • À propos de PrestaShop 1.5
    • Nouveautés de PrestaShop 1.5
    • Guide de démarrage
      • Ce dont vous avez besoin
      • Installer PrestaShop
      • Installer PrestaShop en ligne de commande
      • Mettre PrestaShop à jour
      • Informations diverses
    • Guide de mise à jour
      • Mise à jour automatique
      • Mise à jour manuelle
      • Faire une sauvegarde et la restaurer
      • En cas de problème
    • Guide de l'utilisateur
      • Formations
      • Personnaliser votre boutique
      • Parcourir le front-office
      • Se connecter au back-office de Prestashop
      • Premiers pas avec PrestaShop 1.5
      • Ajouter des produits et des catégories de produits
      • Un aperçu du catalogue
      • Gérer les commandes
      • Gérer les clients
      • Mettre en place des promotions
      • Gérer les transporteurs
      • Comprendre les réglages locaux
      • Gérer les modules et les thèmes
      • Configurer les modules natifs
      • Comprendre les préférences
      • Comprendre les paramètres avancés
      • Administrer le back-office
      • Comprendre les statistiques
      • Gestion avancée des stocks
      • Gérer plusieurs boutiques
    • Guide du développeur
      • Fondamentaux
      • Norme de développement
      • Mettre en place votre environnement de développement
      • Plonger dans le développement PrestaShop
      • Créer un module PrestaShop
      • Les hooks de PrestaShop 1.5
      • Les traductions dans PrestaShop 1.5
      • Comment utiliser la Forge pour contribuer à PrestaShop
      • Comment écrire un descriptif de modification
    • Guide de l'intégrateur
      • Fondamentaux de la création de thème
      • Intégrer du contenu dans une page à l'aide de hooks
      • Caractéristiques d'un thème front-office
      • Caractéristiques d'un thème back-office
      • Template de thème et Smarty
      • Utiliser jQuery et Ajax
      • Bonnes pratiques
    • Guide de l'administrateur système
    • Guide du vendeur
      • Exemples de promotions
      • Nos bons conseils
    • Questions fréquentes
    • Dépannage
  • Documentación española 1.5
    • Acerca de PrestaShop 1.5
    • Introducción
      • Lo que necesita para empezar
    • Guía del usuario
      • Formación
      • Personalización de su tienda
      • Exploración del front-office
      • Conexión al back-office de PrestaShop
      • Primeros pasos con PrestaShop 1.5
      • Añadir productos y categorías de productos
      • Una mirada interna al catálogo
      • Gestionar pedidos
      • Gestionar clientes
      • Crear reglas de precios y cupones
      • Gestionar el transporte
      • Comprender la Configuración Local
      • Gestionar módulos y temas
      • Configurar los Módulos Nativos
      • Comprender las Preferencias
      • Comprender los Parámetros avanzados
      • Administrar el Back-Office
      • Comprender las estadísticas
      • Gestión avanzada de stock
      • Gestionar varias tiendas
    • Guía del Desarrollador
      • Tutoriales para Desarrolladores
        • Uso del Web Service de Prestashop
          • Capítulo 1 - Creación de acceso al Back Office
  • Документация на русском языке 1.5
    • Информация о PrestaShop 1.5
    • Новые функции в PrestaShop 1.5
    • Приступая к работе
      • Что нужно чтобы приступить к работе
      • Установка PrestaShop
      • Установка PrestaShop при помощи командной строки
      • Обновление и удаление PrestaShop
      • Прочая информация
    • Руководство пользователя
      • Обучение
      • Настройка вашего магазина
      • Изучение публичной части сайта
      • Бэк-офис PrestaShop
      • Первые шаги в PrestaShop 1.5
      • Добавление товаров и товарных категорий
      • Внутри каталога
      • Управление заказами
      • Работа с клиентами
      • Создание правил ценообразования корзины и ваучеров
      • Управление доставкой
      • Ориентация в локальных настройках
      • Управление модулями и темами
      • Настойка встроенных модулей
      • Ориентация в настройках
      • Ориентация в расширенных параметрах
      • Администрирование Back-Office
      • Ориентация в статистике
      • Расширенное управление запасами
      • Управление мультимагазином
Powered by GitBook
On this page
  • DB class best practices
  • Fundamentals
  • The available methods
  • Changes between PrestaShop 1.4 and 1.5

Was this helpful?

  1. English documentation 1.5
  2. Developer Guide
  3. Developer tutorials

DB class best practices

PreviousUsing the Context ObjectNextPublic and overloadable methods

Last updated 4 years ago

Was this helpful?

Table of content

/*<![CDATA[*/ div.rbtoc1597308498708 {padding: 0px;} div.rbtoc1597308498708 ul {list-style: disc;margin-left: 0px;} div.rbtoc1597308498708 li {margin-left: 0px;padding-left: 0px;} /*]]>*/

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

Make sure that your data is always checked and protected when doing an insertion. In our example, we want to make sure that we do have an integer with an explicit (int) cast, and that the name is protected against SQL injections thanks to the pSQL() method.

Method parameters

Parameter

Description

$table

Table's name. The PrestaShop prefix is automatically inserted, you do not have to put it in.

$data

The data array, containing the data to be inserted, with name as keys and data as values.

$null_values

If true, then values that are passed as NULL will be inserted as such in the database.

$use_cache

If false, PrestaShop's cache management is disabled during this query. Do not change this parameter unless you knew exactly what you are doing.

$type

If you wish to change the insertion, this parameter can take the following constants: Db::INSERT, Db::INSERT_IGNORE or Db::REPLACE.

$add_prefix

If false, table prefix will not be automatically added to the table name.

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:

Parameter

Description

$where

Takes the update's WHERE clause.

$limit

You can limit the number of records that you will update.

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

You should use insert(), update() and delete() as much as possible, and only use execute() if the query gets too complex. Please note that this method returns a boolean value (true or false), not a database resource that can then be used.

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.

This method automatically adds a LIMIT clause to the query. Be careful not to 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'];

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.

This method automatically adds a LIMIT clause to the query. Be careful not to add one manually.

Example:

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

getValue() does not protect your code from hacking attempts (SQL injections, XSS flaws and CRSF breaches). You still have to secure your data yourself. One PrestaShop-specific securization method is pSQL($value): it helps protect your database against SQL injections.

NumRows()

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

This method has not yet been deprecated, but it is still not recommended to use for best-practices reasons. Indeed, it is better to retrieve the number of results using a SELECT COUNT (*) before.

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.

DB class best practices
Fundamentals
The available methods
insert()
update()
delete()
execute()
query()
executeS()
getRow()
getValue()
NumRows()
A few other methods
Changes between PrestaShop 1.4 and 1.5