0

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

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();
  }
}
  • Thanks for that, but I only want to change the postcode for customers from ireland, and only change the billing address (not change the shipping address) – PaddyD Nov 1 '18 at 10:11
  • @PaddyD, I have added sample code to give you a start. – Jaimin Sutariya Nov 1 '18 at 11:49

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.