The SQL manager is a complex feature, which should be reserved to technical people who know how to explore a database using the SQL language. In return for this complexity, it can be extremely powerful and prove immensely useful to those who master it.
This tool enables you to perform SQL queries directly on the PrestaShop database, and save them for use at any later time. Indeed, PrestaShop presents its database data in many ways, but you might need something more, or more simply, something rawer than PrestaShop's clean interface. Using the SQL manager you can perform complex queries which build upon tables of data in the way YOU need them.
For instance, using this tool and your knowledge of SQL, you could create a reusable query giving you an updated list of the clients who are subscribed to your newsletter, or get a list of products in HTML or CSV format.
For security reasons, some types of queries are not allowed: UPDATE, DELETE, CREATE TABLE, DROP, etc. In short, you can only read data (SELECT query).
Also, secure keys or passwords are hidden (***********).
As usual, the "Add New" button leads to the creation form. It has two main fields:
Additionally, the "List of MySQL Tables" section helps you explore the database, and makes it easier for you to build your queries. It gives you a handy and clickable selector of all the currently available database tables. Select a table to make PrestaShop display its attributes and types, then click "Add attribute to SQL query" to send its name into the "Request" field.
Saving the form sends you back to the main page, with its list of queries.
Each saved query in the table has four icons to the right of its row:
There is only one setting at this time:
The possibilities are endless, but here are a few sample queries to help you build your own.
SELECT email FROM ps_customer
SELECT email FROM ps_customer WHERE newsletter = 1
SELECT p.id_product, pl.name, pl.link_rewrite, pl.description FROM ps_product p LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product) WHERE p.active = 1 AND pl.id_lang = 4
SELECT o.`id_order` AS `id`, CONCAT(LEFT(c.`firstname`, 1), '. ', c.`lastname`) AS `Customer`, ca.`name` AS `Carrier`, cu.`name` AS `Currency`, o.`payment`, CONCAT(o.`total_paid_real`, ' ', cu.`sign`) AS `Total`, o.`date_add` AS `Date` FROM `ps_orders` o LEFT JOIN `ps_customer` c ON (o.`id_customer` = c.`id_customer`) LEFT JOIN `ps_carrier` ca ON (o.id_carrier = ca.id_carrier) LEFT JOIN `ps_currency` cu ON (o.`id_currency` = cu.`id_currency`)