LogoLogo
Homepage
English
English
  • Contributing to the documentation
  • Getting started with PrestaShop 1.7
    • What you need to get started
    • Installing PrestaShop
    • Installing PrestaShop on your computer
    • Installing PrestaShop using the command-line script
    • Uninstalling PrestaShop
    • Misc. information
  • User Guide PrestaShop 1.7
    • Training
    • Customizing your shop
    • Connecting to the PrestaShop back office
    • Discovering the Administration Area
    • First steps with PrestaShop 1.7
    • Selling with PrestaShop
      • Managing your Orders
        • Order list management
        • Order page management
          • Editing an order
          • Creating returns and refunds
        • Creating a back office order
        • Invoices
        • Credit slips
        • Delivery slips
        • Shopping carts
      • Managing your Product Catalog
        • Managing Products
        • Managing Categories
        • Monitoring your Catalog
        • Managing Product Attributes
        • Managing Product Features
        • Managing Brands
        • Managing Suppliers
        • Managing Files
        • Managing Discounts
          • Cart Rules
          • Catalog Price Rules
        • Managing Stock
          • Stock Overview
          • Stock Movements
      • Managing your Customers
        • Your customers
        • Customer addresses
        • Outstanding
      • Managing the Customer Service
        • Customer Service
        • Order Messages
        • Merchandise Returns
      • Understanding your statistics
    • Improving your shop
      • Managing your Modules
        • Modules and Services
          • Module notifications
          • Modules Selection
          • Installed modules
        • Module Catalog
      • Customizing your store design
        • Theme and Logo
        • Theme Catalog
        • Email Theme
        • Pages - Managing Static Content
        • Positions
        • Image Settings
        • Link Widget
      • Managing Shipping
        • Carriers
        • Shipping Preferences
      • Managing Payments
        • Payment Methods
        • Payment Preferences
      • Going International
        • Localization
          • Localization settings
          • Languages
          • Currencies
          • Geolocation
        • Locations
          • Countries
          • Zones
          • States
        • Managing Taxes
          • Taxes
          • Tax Rules
        • Translations
    • Configuring your shop
      • Configuring your Shop Parameters
        • General settings
          • General parameters
          • Maintenance
        • Order Settings
          • Orders preferences
          • Statuses
        • Product Settings
        • Customer Settings
          • Customer Preferences
          • Groups
          • Titles
        • Contact
        • Traffic
          • SEO and URLs
          • Search Engines
          • Referrers
        • Search
          • Search parameters
          • Tags
      • Configuring Advanced Parameters
        • Information
        • Performance
        • Administration
        • Email
        • Import
        • Team
          • Employees
          • Profiles
          • Permissions
        • Database
          • SQL Manager
          • Database Backup
        • Logs
        • Webservice
        • Multistore
        • Experimental features
    • Managing multiple stores
      • The multistore interface
        • Adding a new store
        • Adding a new group of stores
        • Setting a store's URL
      • Sample Usages and Specifics
    • Browsing the front office
    • Complying with the GDPR
Powered by GitBook
On this page
  • Creating a new query
  • Starting a query
  • Settings
  • Some sample queries
  • Listing all the e-mails address of all the customers
  • Listing all the e-mails address of all the customers who are subscribed to your newsletter
  • Listing all the products which are active and have a description in French (id_lang = 4)
  • Listing all the orders, with details about carrier, currency, payment, total and date

Was this helpful?

Edit on GitHub
  1. User Guide PrestaShop 1.7
  2. Configuring your shop
  3. Configuring Advanced Parameters
  4. Database

SQL Manager

PreviousDatabaseNextDatabase Backup

Was this helpful?

The SQL manager is a complex feature, which should be reserved for technical people who know how to explore a database using the SQL language. In return for this complexity, it can be extremely powerful and proves 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 (***********).

Creating a new query

As usual, the "Add new SQl query" button leads to the creation form. It has two main fields:

  • SQL query name. Make the name as long and descriptive as necessary.

  • SQL query. The SQL query itself. You are free to perform JOINs or other intricate selections.

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.

Starting a query

Each saved query in the table has four icons to the right of its row:

  • Export. Performs the query, and has it downloaded by you in CSV format.

  • View. Performs the query, and has it displayed in an HTML table, right within the PrestaShop interface.

  • Edit. You can edit a query as often as necessary, in order to refine it and get better results.

  • Delete. Once a query is not used anymore (or simply because it does not work), you can delete it by clicking this button and confirming your choice.

Settings

There is only one setting at this time:

  • Select your default file encoding. You can configure the character encoding of the downloaded CSV file. The default, UTF-8, is recommended, but you can select ISO-8859-1 if need be.

Some sample queries

The possibilities are endless, but here are a few sample queries to help you build your own.

Listing all the e-mails address of all the customers

SELECT email FROM ps_customer

Listing all the e-mails address of all the customers who are subscribed to your newsletter

SELECT emailFROM ps_customerWHERE newsletter = 1

Listing all the products which are active and have a description in French (id_lang = 4)

Listing all the orders, with details about carrier, currency, payment, total and date

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` oLEFT 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`)

SELECT p.id_product, , pl.link_rewrite, pl.descriptionFROM ps_product pLEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)WHERE p.active = 1AND pl.id_lang = 4

pl.name