Combining Magento 1.9 Customer Data: Names, Emails, and Phone Numbers
As a Magento developer, you may find yourself needing to extract customer dataâspecifically names, email addresses, and phone numbersâfor various reasons such as marketing campaigns or data migration projects. In this post, we’ll combine two existing SQL queries to create a single script that fetches all three pieces of information in one go.
The Original Queries
The current queries provided by the community members are quite efficient for their respective tasks:
- User names and emails (by @Steve Robbins):
SELECT ce.entity_id,
CONCAT(cevf.value, ' ', cevl.value) fullname,
ce.email
FROM customer_entity ce
INNER JOIN customer_entity_varchar cevf ON ce.entity_id = cevf.entity_id
INNER JOIN eav_attribute eaf ON eaf.attribute_id = cevf.attribute_id
INNER JOIN customer_entity_varchar cevl ON ce.entity_id = cevl.entity_id
INNER JOIN eav_attribute eal ON eal.attribute_id = cevl.attribute_id
INNER JOIN eav_entity_type eet ON eet.entity_type_id = eal.entity_type_id = eaf.entity_type_id
WHERE eet.entity_type_code = 'customer'
AND eaf.attribute_code = 'firstname'
AND eal.attribute_code = 'lastname'
ORDER BY ce.entity_id;
- Phone numbers only (by @zaka47):
SELECT customer_address_entity_varchar.value FROM customer_address_entity_varchar
LEFT JOIN customer_entity ON customer_entity.entity_id = "your_customer_id"
LEFT JOIN customer_address_entity ON customer_address_entity.parent_id = customer_entity.entity_id
JOIN eav_attribute ON eav_attribute.attribute_code="telephone"
WHERE customer_address_entity_varchar.attribute_id = eav_attribute.attribute_id;
Combining the Queries
To combine these queries, we’ll use subqueries and join them on the entity_id of the customer. This will allow us to fetch all three pieces of information in one result set:
SELECT c.fullname,
c.email,
p.phone_number
FROM (
SELECT ce.entity_id,
CONCAT(cevf.value, ' ', cevl.value) fullname,
ce.email
FROM customer_entity ce
INNER JOIN customer_entity_varchar cevf ON ce.entity_id = cevf.entity_id
INNER JOIN eav_attribute eaf ON eaf.attribute_id = cevf.attribute_id
INNER JOIN customer_entity_varchar cevl ON ce.entity_id = cevl.entity_id
INNER JOIN eav_attribute eal ON eal.attribute_id = cevl.attribute_id
INNER JOIN eav_entity_type eet ON eet.entity_type_id = eal.entity_type_id = eaf.entity_type_id
WHERE eet.entity_type_code = 'customer'
AND eaf.attribute_code = 'firstname'
AND eal.attribute_code = 'lastname'
) c
LEFT JOIN (
SELECT ce.entity_id,
cav.value phone_number
FROM customer_entity ce
INNER JOIN customer_address_entity ca ON ca.parent_id = ce.entity_id
INNER JOIN customer_address_entity_varchar cav ON cav.entity_id = ca.entity_id
INNER JOIN eav_attribute ea ON ea.attribute_id = cav.attribute_id
WHERE ea.attribute_code = 'telephone'
) p ON c.entity_id = p.entity_id;
The Final Result
The resulting query will return a table with the desired structure:
| Full Name | Email Address | Phone Number |
|---|---|---|
| David Ros | david@email.com | 02-30493929 |
| Joe Pratt | joe@email.com | 03-20392030 |
This combined script should now provide you with a single, efficient way to fetch customer names, emails, and phone numbers from your Magento 1.9 database.
Remember that when dealing with sensitive data like email addresses and phone numbers, always ensure compliance with privacy laws such as GDPR or CCPA. For more information on customer data management in Magento, check out the official documentation: https://docs.magento.com/user-guide/marketing/sales-channel-private-data.html.
Happy coding!