Help me please to write a SQL query that returns the customer ID of addresses that contain the "@" symbol in the zip code field. Or just tell me which tables magento uses to keep zip codes. Thanks


You can use:

$collection = Mage::getResourceModel('customer/address_collection')
    ->addAttributeToFilter('postcode', array('like' => '%@%'));

// or
foreach ($collection as $address) {
    var_dump($address->getData()); // complete address data
    var_dump($address->getData('parent_id')) // customer ID only

To get the SQL query:

echo $collection->getSelect()->__toString();

Results in:

SELECT `e`.*, `at_postcode`.`value` AS `postcode` FROM 
`prefix_customer_address_entity` AS `e`
INNER JOIN `prefix_customer_address_entity_varchar` AS `at_postcode` ON (`at_postcode`.`entity_id` = `e`.`entity_id`)AND (`at_postcode`.`attribute_id` = '30')
WHERE (`e`.`entity_type_id` = '2') AND (at_postcode.value LIKE '%@%')

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.