Adding SQL Queries in PrestaShop 1.5

One of the features of PrestaShop is the ability to use the SQL manager function to make custom queries to find specific data within the database of the shop.  You can create queries, view the results, and export them to a CSV file.  The following tutorial reviews how to add SQL queries in PrestaShop 1.5.

Using the SQL Manager in Advanced Parameters to add a SQL Query

1. To get to the Performance section of the Advanced Parameters for Prestashop, first login to the Administrator as an Administrator
2. Hover over menu bar where it says ADVANCED PARAMETERS - click on SQL MANAGER when it appears in the drop-down menu.
3. You will see something similar to this screenshot:

   

   Note the blue field and the instructions.  This is an information field provided by Prestashop.  It gives direct instructions on how to add a new query,  view the results and export the results.  We will focus on simply adding query in this article.

4. In the top right hand corner click on the green ADD NEW button.  You will the following screen:

   

   
   The two required fields are the name of the query you want to create as well the query itself.  Click on the NAME field and name the query.  For example, the sample screenshots below will show a simple MySQL Query (for information on using MySQL queries, go to MYSQL documentation on Entering Queries) called "newquery" and then it will show a very simple MySQL query pulling data from the PS_ADDRESS table:

   

   In order to add the query, simply click in the REQUEST field and type in the code.  The request above is a simple select request which will show all the addresses in the PS_ADDRESS table.

5. You can type in the name of the table directly in the request or you can actually select the table from the LIST of MYSQL TABLES and it will appear in the request field.  Conveniently, Prestashop has also listed all of the tables in the database that can be addressed.  Clicking on the table in the list will look like this:

   

   The table attributes are listed and are added to the query by simply clicking on the ADD TRIBUTE button beside each table attribute.  The attribute is added into the query field for you saving some typing time (especially if the attribute name is pretty long).  You can click on multiple tables for the query.  Care should be taken with the queries you are running on the database.  Remember that you are taking resources from the server to run these queries.

6. Click on the SAVE button and it will return to list view and look like this:

   

   
   Here you will see the following options:

   Icon	Action description of icon
   	CSV export option
   	View query results
   	Edit query results
   	Delete query

   
   
   
7. To find more information on using the CSV Export option, go to How to Export SQL Query results to CSV in PrestaShop 1.5.  The other options are relatively self-explantory. Click on on the VIEW QUERY RESULTS ICON and you will see the following:
   
   

   This screen also lets you back up and EDIT the query.  Editing the query is exactly the same as adding the request expect that code for the query already exists. 
   
   The EDIT option is available in the ACTION icons list as well as the DELETE option represented by the trashcan icon.  If you wish to delete a specific query, you can delete by selecting the checkbox and the clicking on DELETE SELECTED, or you can click on the trashcan icon for the specific query you wish to remove.

Querying the database is made into an simple task with the PrestaShop SQL Manager feature.  However, it requires some knowledge of querying SQL databases in order to properly obtain the proper data and show the resutls in a meaningful manner.  Make sure to show care when crafting queries as SQL queries can be very large obtaining unnecessary data and possibly resulting in a resource hit on your server.