Get customer names, emails and phone numbers from Magento 1.9 database

Get customer names, emails and phone numbers from Magento 1.9 database

August 5, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Get customer names, emails and phone numbers from Magento 1.9 database

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:

  1. 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;
  1. 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 NameEmail AddressPhone Number
David Rosdavid@email.com02-30493929
Joe Prattjoe@email.com03-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!

Magento 1 Database Email Orders

Our network