Magento – Sql query to return list of confirmed customer email, customer name

magento-1.9MySQL

Please help me with the mysql query to display list of all confirmed customer from my magento db

Best Answer

Below collection will give you all list of confirmed customer collection

$collection = Mage::getResourceModel('customer/customer_collection')
                ->addNameToSelect()
                ->addAttributeToSelect('email')
                ->addAttributeToFilter('confirmation', array('null' => true));

echo $collection->getSelect();

Query will be like

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_confirmation`.`value` AS `confirmation` 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') INNER JOIN `customer_entity_varchar` AS `at_confirmation` ON (`at_confirmation`.`entity_id` = `e`.`entity_id`) AND (`at_confirmation`.`attribute_id` = '831') WHERE (`e`.`entity_type_id` = '1') AND (at_confirmation.value IS NULL)
Related Topic