mysql query to change the postcode field on the billing address

Question

I need a mysql query to change the postcode field on the billing address on customer accounts to blank for user only from one country (Ireland). Currently we have 5000+ user accounts with N/A or NA in the postcode for the billing address and we need this to be changed to blank. I had a look at the database and i can't see where the postcode/zip field is stored.

Thanks

Answer 1

Customer address details are stored in EAV structure in Magento database. First you need to find the attribute_id for the attribute postcode

Go to table eav_attribute and search for attribute_code=postcode. This will give you the attribute code for postcode field in your database.

Now go to table customer_address_entity_varchar and search for attribute_id=YOUR_ATTRIBUTE_ID. This will give you postcode data of all customer addresses.

You can create a query to remove N/A or NA from the table.

Below script may help you, its not tested so test it on a dummy database first. This is just a sample script, you can update as required.

<?php

require_once('app/Mage.php'); //Path to Magento
umask(0);
Mage::app();

$addressesCollection = Mage::getModel("customer/address")->getCollection();
  $addressesCollection->addAttributeToSelect('*');
  $addressesCollection->addFieldToFilter('country_id','IE');

foreach ($addressesCollection as $address) {
  if($address->getPostcode() == 'NA' || $address->getPostcode() == 'N/A'){
     $address->setPostcode(NULL)->save();
  }
}