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 Db::getInstance()->Insert_ID()
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, "nemo@nemops.com", "Fabio", "Porta")');
// Inserting a row
// The $data array must be configured like
// column => value
$data = array(
'id_customer' => 1,
'email' => "nemo@nemops.com",
'firstname' => "Fabio",
'lastname' => "Porta",
);
Db::getInstance()->insert('customer', $data);
// Updating values, array configured as above
$data = array(
'id_customer' => 1,
'email' => "nemo@nemops.com",
'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' => "nemo@nemops.com",
'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::select($fields) DbQuery::from($table, $alias = null) DbQuery::join($fields) DbQuery::leftJoin($table, $alias = null, $on = null) DbQuery::where($restriction) DbQuery::having($restriction) DbQUery::orderBy($fields) DbQUery::groupBy($fields) 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)
->groupBy('p.id_product')
->limit(5)
$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.
