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

[php]

// 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()

[/php]

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

[php]

// 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

[/php]

The Query object in Prestashop

[php]

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)

[/php]

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

Example Usage

[php]

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

[/php]

Quick escape in a SQL query

[php]

pSQL($string, $htmlOK = false)

[/php]

Sanitize data which will be injected into SQL query

Example Usage

[php]

$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).’\”);

[/php]

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.

Wrapping it up

We saw quite a number of useful PrestaShop Functions today, which are enough to start coding your own, simple module. Apart from trying to remember them all, I suggest creating code snippets with some text expander, so you can quickly drop them in, without having to remember the exact syntax.

Looking for quality PrestaShop hosting? Check out Arvixe Web Solutions.

Tags: , , , | Posted under PrestaShop | RSS 2.0

Author Spotlight

Fabio Porta

Fabio has been involved in web development and design since 2005, when launched his first website at the age of 16. He’s now highly skilled in both client and server side development, along with design, and since August 2012 runs a successful website about PrestaShop tutorials and Prestashop Modules called Nemo’s Post Scriptum, at http://nemops.com

Leave a Reply

Your email address will not be published. Required fields are marked *