Extracting Customer Phone Numbers from PrestaShop 1.7: A SQL Query Solution

Extracting Customer Phone Numbers from PrestaShop 1.7: A SQL Query Solution

This insight summarizes a PrestaShop forum thread where a user needed to export customer names, and phone numbers from their PrestaShop 1.7.8.10 database. The default PrestaShop export functionality only provided email addresses, prompting the user to seek a solution for extracting phone numbers as well.

The solution involves using a SQL query to retrieve the desired data from the PrestaShop database. Here's a breakdown of the steps and the SQL query provided:

1. The Initial Query: A forum user provided the following SQL query to extract the necessary information:

SELECT c.id_customer, c.firstname, c.lastname, c.email, a.phone, a.phone_mobile FROM ps_customer c LEFT JOIN ps_address a ON a.id_customer = c.id_customer AND a.deleted = 0 AND a.active = 1 WHERE c.deleted = 0 ORDER BY c.id_customer ASC;

Note: The ps_ prefix needs to be replaced with the actual prefix used in the PrestaShop installation.

2. Finding the Table Prefix: The original poster (OP) struggled to locate the table prefix. The forum provided several ways to find it:
• In the PrestaShop admin panel under Advanced Parameters -> Information, in the Database Information section.
• In the ./app/config/parameters.php file on the server via FTP.
• By opening the database in phpMyAdmin.
3. Executing the Query: The query can be executed using the SQL Manager in the PrestaShop admin panel (Parameters -> SQL Manager). The results can then be exported as a CSV file.
4. Addressing Missing Data and Formatting Issues: The OP noted that some contacts were missing from the export. This was because those customers had not provided their phone numbers. Additionally, the leading zero was missing from the phone numbers when opening the CSV file in LibreOffice Calc.
5. Improved Query: A refined SQL query was suggested to exclude duplicate customers and records without phone numbers:

SELECT c.id_customer, c.firstname, c.lastname, c.email, a.phone, a.phone_mobile FROM ps_customer c LEFT JOIN ps_address a ON a.id_customer = c.id_customer AND a.deleted = 0 AND a.active = 1 WHERE c.deleted = 0 AND (a.phone NOT IN('') OR a.phone_mobile NOT IN ('')) GROUP BY c.id_customer ORDER BY c.id_customer ASC;

6. CSV Formatting Solution: The solution to the leading zero issue in LibreOffice Calc was to select the phone number column during CSV import and change the column type from "Standard" to "Text".

Conclusion: This thread provides a practical solution for PrestaShop store owners who need to export customer phone numbers. It highlights the use of SQL queries within the PrestaShop environment and addresses common issues encountered during data export and formatting.