1

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.

  • Your customers will have a billing and shipping address. Do you want both? – brentwpeterson Oct 30 '15 at 16:48
2

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();
0

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

0

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.