My attributes have hundreds of options which are empty/null, is there some sql statement or code to delete all null values?

  • 1
    A screen shot or further description would be required otherwise we are just guessing at what to delete in your store. I'm sure you don't want that. What have you tried so far? – philwinkle Jul 23 '13 at 4:42

By default if you enter attribute options via admin->catalog->attributes->manage attributes, you cannot add empty option to the attribute. Maybe these empty options were added via custom script. But you can remove these options. Below I give custom script to delete empty options. You can remove them with database query, but I don't advice to direct touch to db.

 * @var $attribute Mage_Eav_Model_Entity_Attribute_Abstract
$attribute                  = Mage::getModel('catalog/resource_eav_attribute')
  ->loadByCode(Mage_Catalog_Model_Product::ENTITY, 'some_attribute_code');
$setup                      = new Mage_Eav_Model_Entity_Setup('core_setup');
$newOptions['attribute_id'] = $attribute->getAttributeId();
$options                    = $attribute->getSource()->getAllOptions();
foreach ($options as $option) {
    if ($option['label'] === '' && $option['value']) {// if option label is empty and has value
        $newOptions['value'][$option['value']][]  = $option['label'];
        $newOptions['delete'][$option['value']]   = $option['value'];// delete it
$setup->addAttributeOption($newOptions);//update options


1.Create store_root/shell/empty.php file with code snippet:

require_once 'abstract.php';
class Empty_Attribute_Remover extends Mage_Shell_Abstract
    public function run()
        $attribute                  = Mage::getModel('catalog/resource_eav_attribute')
          ->loadByCode(Mage_Catalog_Model_Product::ENTITY, 'some_attribute_code');

        $optionCollection = Mage::getResourceModel('eav/entity_attribute_option_collection')
            ->setPositionOrder('desc', true)
        foreach($optionCollection as $option){
            //remove if options value is empty
            if ($option->getValue()=='') {

$shell = new Empty_Attribute_Remover();

2.Run this php script to remove empty options of attribute with some_attribute_code:

php -f empty.php
  • Hopefully im doing this right, i installed this script in the root folder and ran the php script and it did not remove any null attributes. Is there perhaps a sql command i can run on my test database? – David Vudoo Aug 22 '13 at 16:23
  • I have uploaded a screenshot i.imgur.com/U7fCRSH.jpg – David Vudoo Aug 22 '13 at 16:35
  • @DavidVudoo try updated answer. – mageUz Jan 16 '14 at 0:40

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.