Get users address through sql query

Question

Is there a way to execute an sql query to get all my customers emails+addresses? I know about doing it from the admin panel but it only lets me do so many at a time. I need to be able to do it all at once preferably with an sql query.

Answer 1

Try this

SELECT `e`.*, `at_prefix`.`value` AS `prefix`, `at_firstname`.`value` AS `firstname`, `at_middlename`.`value` AS `middlename`, `at_lastname`.`value` AS `lastname`, `at_suffix`.`value` AS `suffix`, CONCAT(IF(at_prefix.value IS NOT NULL AND at_prefix.value != '', CONCAT(LTRIM(RTRIM(at_prefix.value)), ' '), ''), LTRIM(RTRIM(at_firstname.value)), ' ', IF(at_middlename.value IS NOT NULL AND at_middlename.value != '', CONCAT(LTRIM(RTRIM(at_middlename.value)), ' '), ''), LTRIM(RTRIM(at_lastname.value)), IF(at_suffix.value IS NOT NULL AND at_suffix.value != '', CONCAT(' ', LTRIM(RTRIM(at_suffix.value))), '')) AS `name`, `at_default_billing`.`value` AS `default_billing`, `at_billing_street`.`value` AS `billing_street`, `at_billing_postcode`.`value` AS `billing_postcode`, `at_billing_city`.`value` AS `billing_city`, `at_billing_telephone`.`value` AS `billing_telephone`, `at_billing_fax`.`value` AS `billing_fax`, `at_billing_region`.`value` AS `billing_region`, `at_billing_country_code`.`value` AS `billing_country_code`, `at_default_shipping`.`value` AS `default_shipping`, `at_shipping_street`.`value` AS `shipping_street`, `at_shipping_postcode`.`value` AS `shipping_postcode`, `at_shipping_city`.`value` AS `shipping_city`, `at_shipping_telephone`.`value` AS `shipping_telephone`, `at_shipping_fax`.`value` AS `shipping_fax`, `at_shipping_region`.`value` AS `shipping_region`, `at_shipping_country_code`.`value` AS `shipping_country_code`, `at_taxvat`.`value` AS `taxvat` FROM `customer_entity` AS `e` LEFT JOIN `customer_entity_varchar` AS `at_prefix` ON (`at_prefix`.`entity_id` = `e`.`entity_id`) AND (`at_prefix`.`attribute_id` = '826') LEFT JOIN `customer_entity_varchar` AS `at_firstname` ON (`at_firstname`.`entity_id` = `e`.`entity_id`) AND (`at_firstname`.`attribute_id` = '1') LEFT JOIN `customer_entity_varchar` AS `at_middlename` ON (`at_middlename`.`entity_id` = `e`.`entity_id`) AND (`at_middlename`.`attribute_id` = '827') LEFT JOIN `customer_entity_varchar` AS `at_lastname` ON (`at_lastname`.`entity_id` = `e`.`entity_id`) AND (`at_lastname`.`attribute_id` = '2') LEFT JOIN `customer_entity_varchar` AS `at_suffix` ON (`at_suffix`.`entity_id` = `e`.`entity_id`) AND (`at_suffix`.`attribute_id` = '828') LEFT JOIN `customer_entity_int` AS `at_default_billing` ON (`at_default_billing`.`entity_id` = `e`.`entity_id`) AND (`at_default_billing`.`attribute_id` = '7') LEFT JOIN `customer_address_entity_text` AS `at_billing_street` ON (`at_billing_street`.`entity_id` = `at_default_billing`.`value`) AND (`at_billing_street`.`attribute_id` = '16') LEFT JOIN `customer_address_entity_varchar` AS `at_billing_postcode` ON (`at_billing_postcode`.`entity_id` = `at_default_billing`.`value`) AND (`at_billing_postcode`.`attribute_id` = '14') LEFT JOIN `customer_address_entity_varchar` AS `at_billing_city` ON (`at_billing_city`.`entity_id` = `at_default_billing`.`value`) AND (`at_billing_city`.`attribute_id` = '15') LEFT JOIN `customer_address_entity_varchar` AS `at_billing_telephone` ON (`at_billing_telephone`.`entity_id` = `at_default_billing`.`value`) AND (`at_billing_telephone`.`attribute_id` = '17') LEFT JOIN `customer_address_entity_varchar` AS `at_billing_fax` ON (`at_billing_fax`.`entity_id` = `at_default_billing`.`value`) AND (`at_billing_fax`.`attribute_id` = '18') LEFT JOIN `customer_address_entity_varchar` AS `at_billing_region` ON (`at_billing_region`.`entity_id` = `at_default_billing`.`value`) AND (`at_billing_region`.`attribute_id` = '12') LEFT JOIN `customer_address_entity_varchar` AS `at_billing_country_code` ON (`at_billing_country_code`.`entity_id` = `at_default_billing`.`value`) AND (`at_billing_country_code`.`attribute_id` = '11') LEFT JOIN `customer_entity_int` AS `at_default_shipping` ON (`at_default_shipping`.`entity_id` = `e`.`entity_id`) AND (`at_default_shipping`.`attribute_id` = '8') LEFT JOIN `customer_address_entity_text` AS `at_shipping_street` ON (`at_shipping_street`.`entity_id` = `at_default_shipping`.`value`) AND (`at_shipping_street`.`attribute_id` = '16') LEFT JOIN `customer_address_entity_varchar` AS `at_shipping_postcode` ON (`at_shipping_postcode`.`entity_id` = `at_default_shipping`.`value`) AND (`at_shipping_postcode`.`attribute_id` = '14') LEFT JOIN `customer_address_entity_varchar` AS `at_shipping_city` ON (`at_shipping_city`.`entity_id` = `at_default_shipping`.`value`) AND (`at_shipping_city`.`attribute_id` = '15') LEFT JOIN `customer_address_entity_varchar` AS `at_shipping_telephone` ON (`at_shipping_telephone`.`entity_id` = `at_default_shipping`.`value`) AND (`at_shipping_telephone`.`attribute_id` = '17') LEFT JOIN `customer_address_entity_varchar` AS `at_shipping_fax` ON (`at_shipping_fax`.`entity_id` = `at_default_shipping`.`value`) AND (`at_shipping_fax`.`attribute_id` = '18') LEFT JOIN `customer_address_entity_varchar` AS `at_shipping_region` ON (`at_shipping_region`.`entity_id` = `at_default_shipping`.`value`) AND (`at_shipping_region`.`attribute_id` = '12') LEFT JOIN `customer_address_entity_varchar` AS `at_shipping_country_code` ON (`at_shipping_country_code`.`entity_id` = `at_default_shipping`.`value`) AND (`at_shipping_country_code`.`attribute_id` = '11') LEFT JOIN `customer_entity_varchar` AS `at_taxvat` ON (`at_taxvat`.`entity_id` = `e`.`entity_id`) AND (`at_taxvat`.`attribute_id` = '830') WHERE (`e`.`entity_type_id` = '1')

Basically this query you will get using customer collection with join of address data. if this doesn't work you can create one sample file put below code in that and run it in browser you will get the query which you can run directly in database.

<?php
require_once 'app/Mage.php';
umask(0);
$app = Mage::app('default');

 $collection = Mage::getResourceModel('customer/customer_collection')
                ->addNameToSelect()
                ->addAttributeToSelect('email')
                ->addAttributeToSelect('created_at')
                ->addAttributeToSelect('group_id')
                ->joinAttribute('billing_street', 'customer_address/street', 'default_billing', null, 'left')
                ->joinAttribute('billing_postcode', 'customer_address/postcode', 'default_billing', null, 'left')
                ->joinAttribute('billing_city', 'customer_address/city', 'default_billing', null, 'left')
                ->joinAttribute('billing_telephone', 'customer_address/telephone', 'default_billing', null, 'left')
                ->joinAttribute('billing_fax', 'customer_address/fax', 'default_billing', null, 'left')
                ->joinAttribute('billing_region', 'customer_address/region', 'default_billing', null, 'left')
                ->joinAttribute('billing_country_code', 'customer_address/country_id', 'default_billing', null, 'left')

                ->joinAttribute('shipping_street', 'customer_address/street', 'default_shipping', null, 'left')
                ->joinAttribute('shipping_postcode', 'customer_address/postcode', 'default_shipping', null, 'left')
                ->joinAttribute('shipping_city', 'customer_address/city', 'default_shipping', null, 'left')
                ->joinAttribute('shipping_telephone', 'customer_address/telephone', 'default_shipping', null, 'left')
                ->joinAttribute('shipping_fax', 'customer_address/fax', 'default_shipping', null, 'left')
                ->joinAttribute('shipping_region', 'customer_address/region', 'default_shipping', null, 'left')
                ->joinAttribute('shipping_country_code', 'customer_address/country_id', 'default_shipping', null, 'left')
                ->joinAttribute('taxvat', 'customer/taxvat', 'entity_id', null, 'left');

 echo $collection->getSelect();

Answer 2

Try this:

SELECT
customer_entity.email,
customer_entity.entity_id,
customer_address_entity_text.value
FROM customer_entity
JOIN customer_address_entity ON customer_entity.entity_id = 
customer_address_entity.parent_id
JOIN customer_address_entity_text ON customer_address_entity.entity_id = 
customer_address_entity_text.entity_id;

It works for me!

Answer 3

getting the address data from the magento EAV database structure is not a short task with SQL because you have to add a join for each attribute you need to select.

This is an example from one of our export scripts:

    SELECT email as email, name.value as firstname,
 masteraddress.parent_id as id_indirizzo, adr_city.value as _address_city,
FROM customer_entity c
INNER JOIN customer_entity_varchar name on name.entity_id = c.entity_id AND nome.attribute_id = 1
...

INNER JOIN customer_address_entity masteraddress on masteraddress.parent_id = c.entity_id
INNER JOIN customer_address_entity_varchar adr_city on adr_city.entity_id = masteraddress.entity_id AND adr_city.attribute_id = 15

Where email is a field in the customer_entity table, the name field is an EAV Attribute that in our db has id =1.

Then we join the customer_address_entity to get the addresses associated whith the customers and the adr_city filed is an address attribute which have id of 15.

Hope tha above is a starting point to develop your script

Answer 4

Ancient question but some of us still have to deal with Magento 1 data especially migrations. Here is a raw SQL query to get Magento 1 customer collection with join of default billing and default shipping address data.

SELECT `e`.entity_id,
       `e`.email,
       `e`.created_at,
       `e`.is_active,
       `at_prefix`.`value`                AS `prefix`,
       `at_firstname`.`value`             AS `firstname`,
       `at_middlename`.`value`            AS `middlename`,
       `at_lastname`.`value`              AS `lastname`,
       `at_suffix`.`value`                AS `suffix`,
       CONCAT(IF(at_prefix.value IS NOT NULL AND at_prefix.value != '', CONCAT(LTRIM(RTRIM(at_prefix.value)), ' '), ''),
              LTRIM(RTRIM(at_firstname.value)), ' ', IF(at_middlename.value IS NOT NULL AND at_middlename.value != '',
                                                        CONCAT(LTRIM(RTRIM(at_middlename.value)), ' '), ''),
              LTRIM(RTRIM(at_lastname.value)),
              IF(at_suffix.value IS NOT NULL AND at_suffix.value != '', CONCAT(' ', LTRIM(RTRIM(at_suffix.value))),
                 ''))                     AS `name`,
       `at_default_billing`.`value`       AS `default_billing`,
       `at_billing_company`.`value`       AS `billing_company`,
       `at_billing_street`.`value`        AS `billing_street`,
       `at_billing_city`.`value`          AS `billing_city`,
       `at_billing_region`.`value`        AS `billing_region`,
       `at_billing_postcode`.`value`      AS `billing_postcode`,
       `at_billing_country_code`.`value`  AS `billing_country_code`,
       `at_billing_telephone`.`value`     AS `billing_telephone`,
       `at_billing_fax`.`value`           AS `billing_fax`,
       `at_default_shipping`.`value`      AS `default_shipping`,
       `at_shipping_company`.`value`      AS `shipping_company`,
       `at_shipping_street`.`value`       AS `shipping_street`,
       `at_shipping_city`.`value`         AS `shipping_city`,
       `at_shipping_region`.`value`       AS `shipping_region`,
       `at_shipping_postcode`.`value`     AS `shipping_postcode`,
       `at_shipping_country_code`.`value` AS `shipping_country_code`,
       `at_shipping_telephone`.`value`    AS `shipping_telephone`,
       `at_shipping_fax`.`value`          AS `shipping_fax`
FROM `customer_entity` AS `e`
         LEFT JOIN `customer_entity_varchar` AS `at_prefix`
                   ON (`at_prefix`.`entity_id` = `e`.`entity_id`) AND (`at_prefix`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'prefix' AND et.entity_type_code = 'customer'))
         LEFT JOIN `customer_entity_varchar` AS `at_firstname`
                   ON (`at_firstname`.`entity_id` = `e`.`entity_id`) AND (`at_firstname`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'firstname' AND et.entity_type_code = 'customer'))
         LEFT JOIN `customer_entity_varchar` AS `at_middlename`
                   ON (`at_middlename`.`entity_id` = `e`.`entity_id`) AND (`at_middlename`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'middlename' AND et.entity_type_code = 'customer'))
         LEFT JOIN `customer_entity_varchar` AS `at_lastname`
                   ON (`at_lastname`.`entity_id` = `e`.`entity_id`) AND (`at_lastname`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'lastname' AND et.entity_type_code = 'customer'))
         LEFT JOIN `customer_entity_varchar` AS `at_suffix`
                   ON (`at_suffix`.`entity_id` = `e`.`entity_id`) AND (`at_suffix`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'suffix' AND et.entity_type_code = 'customer'))
         LEFT JOIN `customer_entity_int` AS `at_default_billing`
                   ON (`at_default_billing`.`entity_id` = `e`.`entity_id`) AND
                      (`at_default_billing`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'default_billing' AND et.entity_type_code = 'customer'))
         LEFT JOIN `customer_address_entity_varchar` AS `at_billing_company`
                   ON (`at_billing_company`.`entity_id` = `at_default_billing`.`value`) AND
                      (`at_billing_company`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'company' AND et.entity_type_code = 'customer_address'))
         LEFT JOIN `customer_address_entity_text` AS `at_billing_street`
                   ON (`at_billing_street`.`entity_id` = `at_default_billing`.`value`) AND
                      (`at_billing_street`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'street' AND et.entity_type_code = 'customer_address'))
         LEFT JOIN `customer_address_entity_varchar` AS `at_billing_city`
                   ON (`at_billing_city`.`entity_id` = `at_default_billing`.`value`) AND
                      (`at_billing_city`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'city' AND et.entity_type_code = 'customer_address'))
         LEFT JOIN `customer_address_entity_varchar` AS `at_billing_region`
                   ON (`at_billing_region`.`entity_id` = `at_default_billing`.`value`) AND
                      (`at_billing_region`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'region' AND et.entity_type_code = 'customer_address'))
         LEFT JOIN `customer_address_entity_varchar` AS `at_billing_postcode`
                   ON (`at_billing_postcode`.`entity_id` = `at_default_billing`.`value`) AND
                      (`at_billing_postcode`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'postcode' AND et.entity_type_code = 'customer_address'))
         LEFT JOIN `customer_address_entity_varchar` AS `at_billing_country_code`
                   ON (`at_billing_country_code`.`entity_id` = `at_default_billing`.`value`) AND
                      (`at_billing_country_code`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'country_id' AND et.entity_type_code = 'customer_address'))
         LEFT JOIN `customer_address_entity_varchar` AS `at_billing_telephone`
                   ON (`at_billing_telephone`.`entity_id` = `at_default_billing`.`value`) AND
                      (`at_billing_telephone`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'telephone' AND et.entity_type_code = 'customer_address'))
         LEFT JOIN `customer_address_entity_varchar` AS `at_billing_fax`
                   ON (`at_billing_fax`.`entity_id` = `at_default_billing`.`value`) AND
                      (`at_billing_fax`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'fax' AND et.entity_type_code = 'customer_address'))
         LEFT JOIN `customer_entity_int` AS `at_default_shipping`
                   ON (`at_default_shipping`.`entity_id` = `e`.`entity_id`) AND
                      (`at_default_shipping`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'default_shipping' AND et.entity_type_code = 'customer'))
         LEFT JOIN `customer_address_entity_varchar` AS `at_shipping_company`
                   ON (`at_shipping_company`.`entity_id` = `at_default_shipping`.`value`) AND
                      (`at_shipping_company`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'company' AND et.entity_type_code = 'customer_address'))
         LEFT JOIN `customer_address_entity_text` AS `at_shipping_street`
                   ON (`at_shipping_street`.`entity_id` = `at_default_shipping`.`value`) AND
                      (`at_shipping_street`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'street' AND et.entity_type_code = 'customer_address'))
         LEFT JOIN `customer_address_entity_varchar` AS `at_shipping_city`
                   ON (`at_shipping_city`.`entity_id` = `at_default_shipping`.`value`) AND
                      (`at_shipping_city`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'city' AND et.entity_type_code = 'customer_address'))
         LEFT JOIN `customer_address_entity_varchar` AS `at_shipping_region`
                   ON (`at_shipping_region`.`entity_id` = `at_default_shipping`.`value`) AND
                      (`at_shipping_region`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'region' AND et.entity_type_code = 'customer_address'))
         LEFT JOIN `customer_address_entity_varchar` AS `at_shipping_postcode`
                   ON (`at_shipping_postcode`.`entity_id` = `at_default_shipping`.`value`) AND
                      (`at_shipping_postcode`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'postcode' AND et.entity_type_code = 'customer_address'))
         LEFT JOIN `customer_address_entity_varchar` AS `at_shipping_country_code`
                   ON (`at_shipping_country_code`.`entity_id` = `at_default_shipping`.`value`) AND
                      (`at_shipping_country_code`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'country_id' AND et.entity_type_code = 'customer_address'))
         LEFT JOIN `customer_address_entity_varchar` AS `at_shipping_telephone`
                   ON (`at_shipping_telephone`.`entity_id` = `at_default_shipping`.`value`) AND
                      (`at_shipping_telephone`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'telephone' AND et.entity_type_code = 'customer_address'))
         LEFT JOIN `customer_address_entity_varchar` AS `at_shipping_fax`
                   ON (`at_shipping_fax`.`entity_id` = `at_default_shipping`.`value`) AND
                      (`at_shipping_fax`.`attribute_id` = (SELECT attribute_id FROM `eav_attribute` ea LEFT JOIN `eav_entity_type` et ON ea.entity_type_id = et.entity_type_id  WHERE `ea`.`attribute_code` = 'fax' AND et.entity_type_code = 'customer_address'))
WHERE (`e`.`entity_type_id` = '1')