Essential Prestashop Functions – Day 2

Prestashop has lots of time-saving functions that we can use when developing modules or extensions. In this second batch we will examine database-related methods.

NOTICE: Values with “=” in the declaration are optionals.

Database-related Functions

// Retrieving an array of values from the given table
Db::getInstance()->executeS($sql, $array = true, $use_cache = true);

// Retrieving a single value
Db::getInstance()->getValue($sql, $use_cache = true);

// Retrieving a whole row
Db::getInstance()->getRow($sql, $use_cache = true);

// Executing a generic query, returns true if succeeded, false if failed
Db::getInstance()->execute($sql, $use_cache = true)

// Inserting a row
Db::getInstance()->insert($table, $data, $null_values = false, $use_cache = true, $type = Db::INSERT, $add_prefix = true)

// Updating values
Db::getInstance()->update($table, $data, $where = '', $limit = 0, $null_values = false, $use_cache = true, $add_prefix = true)

// Erase the given entry
Db::getInstance()->delete($table, $where = '', $limit = 0, $use_cache = true, $add_prefix = true)

// Escape data
Db::getInstance()->escape($string, $html_ok = false, $bq_sql = false)

// Get the primary key of the last item you added

These methods serve as helpers so that we don’t have to create a new mysql connection every time we want to run a query

Example Usage

// Returns an array with each item correspinding to a single database row
// It should only be used to retrieve values, use execute or the other helpers to insert/update them
$customers = Db::getInstance()->executeS('SELECT * FROM '._DB_PREFIX_.'customers');

// Returns product data of the ones that belong to the given category only (id = 4)
$products = Db::getInstance()->executeS('
	SELECT * FROM '._DB_PREFIX_.'product p
	LEFT JOIN '._DB_PREFIX_.'category_product cp ON (cp.id_product = p.id_product)
	WHERE cp.id_category = 4

// Retrieving the specific product name for our current language
$product_name = Db::getInstance()->getValue('SELECT name FROM '._DB_PREFIX_.'product_lang WHERE id_product = 1 AND id_lang = ' . $this->context->language->id);

// Get the whole row for customer id = 1
$customer = Db::getInstance()->getRow('SELECT * FROM '._DB_PREFIX_.'customer WHERE id_customer = 1');

// Insert some data manually (not recommended, unless you have some really specific SQL to use)
Db::getInstance()->execute('INSERT INTO '._DB_PREFIX_.'customer (id_customer, email, firstname, lastname) VALUES (9, "", "Fabio", "Porta")');

// Inserting a row
// The $data array must be configured like 
// 		column => value

$data = array(
	'id_customer' => 1,
	'email' => "",
	'firstname' => "Fabio",
	'lastname' => "Porta",
Db::getInstance()->insert('customer', $data);

// Updating values, array configured as above
$data = array(
	'id_customer' => 1,
	'email' => "",
	'firstname' => "Fabio",
	'lastname' => "Porta",
Db::getInstance()->update('customer', $data, 'id_customer = 1');

// Erase customer with id = 1
Db::getInstance()->delete('customer', 'id_customer = 1');

// Escape data
$sanitized = Db::getInstance()->escape('<div class="test"><div>', true);
// will return <div class=\"test\"><div>

// Get the primary key of the last item you added

$data = array(
	'email' => "",
	'firstname' => "Fabio",
	'lastname' => "Porta",
Db::getInstance()->insert('customer', $data);
$last_id = Db::getInstance()->Insert_ID()
// $last_id will be the id_customer of the entry we just added

The Query object in Prestashop

DbQuery::from($table, $alias = null)
DbQuery::leftJoin($table, $alias = null, $on = null)
DbQuery::limit($limit, $offset = 0)

All these functions are used to streamline a query creation. Inspect the DbQuery class to have a complete list.

Example Usage

	// get all products with id > 3, with relative language data

	$query = new DbQuery();
	$query->select('p.*, pl.*')
		->from('product', 'p')
		->leftJoin('product_lang', 'pl', 'p.id_product = pl.id_product')
		->where('p.id_product > 34')
		->where('pl.id_lang = ' . $this->context->language->id)

	$result = Db::getInstance()->getValue($query);

Quick escape in a SQL query

pSQL($string, $htmlOK = false)

Sanitize data which will be injected into SQL query

Example Usage

	$search = '2" clamps';
	// get aliases for the given search word, as you can see the above requires douple quotes to be escaped
	$aliases = Db::getInstance()->executeS('
	SELECT a.alias
	FROM `'._DB_PREFIX_.'alias` a
	WHERE `search` = \''.pSQL($search).'\'');

Wrapping it up

All of the methods we examined today are a huge time saver when dealing with the Database in Prestashop. I rarely use the query object, I admit, as I prefer relying on the other methods. However, since it’s getting more and more built into the standard workflow of core modules and methods, it’s good practice to start using that as well, and integrate it into your own Prestashop Module/Extension.

Additional Resources

You like the tuts and want to say "thank you"? Well, you can always feel free to donate:

  • Minh

    Hi NemoPS , in UpdateValues , can i update any fields i like ?
    For ex :

    $data = array(

    ‘id_customer’ => 1,

    ‘email’ => “”,

    //’firstname’ => “Fabio”,

    //’lastname’ => “Porta”,

    // I don’t want to update ‘firstname’ and ‘lastname;
    Db::getInstance()->update(‘customer’, $data, ‘id_customer = 1′);

    Thanks for your post , this is very huge helpful and sorry about my english.

    • NemoPS

      Hi Minh,
      yes, you can update those ones only, of course


  • Gabriel Arama

    Very useful article.
    There is a small typing mistake “$customer = Db::getInstance()->getROw” in the first Example Usage.

    Keep up the good work!

    • NemoPS

      Whoops :D Thanks!

Store Top Sales

You like the tuts and want to say "thank you"? Well, you can always feel free to donate: