Exports full user data from Prestashop, including address, phone, etc.

export-user-data.sql

/* Selecting users that made at least 1 order */
SELECT * FROM (
/* Users with gender specified */
SELECT 
PS_customer.id_customer AS "Customer ID", 
PS_gender_lang.name AS "Gender", 
PS_customer.firstname AS "First name",
PS_customer.lastname AS "Last name", 
PS_customer.email AS "Email", 
PS_customer.passwd AS "Password Hash", 
PS_customer.company AS "Company",
PS_address.company AS "Company address",
PS_customer.birthday AS "Birthday", 
PS_customer.newsletter AS "Opted for newsletter", 
PS_customer.ip_registration_newsletter AS "Newsletter registration IP",
PS_customer.note AS "Notes",
PS_customer.active AS "Is active",
PS_customer.date_add AS "Registration date",
PS_customer.date_upd AS "Update date",
PS_country_lang.name AS "Country", 
PS_address.id_state AS "State", 
PS_address.address1 AS "Address 1", 
PS_address.address2 AS "Address 2",
PS_address.postcode AS "ZIP code", 
PS_address.city AS "City", 
PS_address.phone AS "Phone", 
PS_address.phone_mobile AS "Mobile phone", 
PS_address.vat_number AS "VAT"
from PS_customer
INNER JOIN PS_address ON PS_customer.id_customer=PS_address.id_customer 
INNER JOIN PS_country_lang ON PS_address.id_country=PS_country_lang.id_country AND PS_country_lang.id_lang=1
INNER JOIN PS_gender_lang ON PS_customer.id_gender=PS_gender_lang.id_gender AND PS_gender_lang.id_lang=1 
UNION ALL
/* Users with gender unspecified */
SELECT
PS_customer.id_customer AS "Customer ID", 
"" AS "Gender", 
PS_customer.firstname AS "First name",
PS_customer.lastname AS "Last name", 
PS_customer.email AS "Email", 
PS_customer.passwd AS "Password Hash", 
PS_customer.company AS "Company",
PS_address.company AS "Company address",
PS_customer.birthday AS "Birthday", 
PS_customer.newsletter AS "Opted for newsletter", 
PS_customer.ip_registration_newsletter AS "Newsletter registration IP",
PS_customer.note AS "Notes",
PS_customer.active AS "Is active",
PS_customer.date_add AS "Registration date",
PS_customer.date_upd AS "Update date",
PS_country_lang.name AS "Country", 
PS_address.id_state AS "State", 
PS_address.address1 AS "Address 1", 
PS_address.address2 AS "Address 2",
PS_address.postcode AS "ZIP code", 
PS_address.city AS "City", 
PS_address.phone AS "Phone", 
PS_address.phone_mobile AS "Mobile phone", 
PS_address.vat_number AS "VAT"
from PS_customer
INNER JOIN PS_address ON PS_customer.id_customer=PS_address.id_customer 
INNER JOIN PS_country_lang ON PS_address.id_country=PS_country_lang.id_country AND PS_country_lang.id_lang=1
WHERE PS_customer.id_gender=0 
) x
ORDER BY x.`Customer ID`;


/* Selecting users that made no orders */
SELECT * FROM (
SELECT
/* Users with gender specified */
PS_customer.id_customer AS "Customer ID", 
PS_gender_lang.name AS "Gender", 
PS_customer.firstname AS "First name",
PS_customer.lastname AS "Last name", 
PS_customer.email AS "Email", 
PS_customer.passwd AS "Password Hash", 
PS_customer.company AS "Company",
PS_customer.birthday AS "Birthday", 
PS_customer.newsletter AS "Opted for newsletter", 
PS_customer.ip_registration_newsletter AS "Newsletter registration IP",
PS_customer.note AS "Notes",
PS_customer.active AS "Is active",
PS_customer.date_add AS "Registration date",
PS_customer.date_upd AS "Update date"
FROM PS_customer
INNER JOIN PS_gender_lang ON PS_customer.id_gender=PS_gender_lang.id_gender AND PS_gender_lang.id_lang=1
WHERE NOT EXISTS (SELECT
		          *
                  FROM PS_address
                  WHERE PS_customer.id_customer=PS_address.id_customer)
UNION ALL
/* Users with gender unspecified */
SELECT
PS_customer.id_customer AS "Customer ID", 
"" AS "Gender", 
PS_customer.firstname AS "First name",
PS_customer.lastname AS "Last name", 
PS_customer.email AS "Email", 
PS_customer.passwd AS "Password Hash", 
PS_customer.company AS "Company",
PS_customer.birthday AS "Birthday", 
PS_customer.newsletter AS "Opted for newsletter", 
PS_customer.ip_registration_newsletter AS "Newsletter registration IP",
PS_customer.note AS "Notes",
PS_customer.active AS "Is active",
PS_customer.date_add AS "Registration date",
PS_customer.date_upd AS "Update date"
FROM PS_customer
WHERE PS_customer.id_gender=0
AND NOT EXISTS (SELECT
		          *
                  FROM PS_address
                  WHERE PS_customer.id_customer=PS_address.id_customer)
) x
ORDER BY x.`Customer ID`;