Magento – Automate the Creation of Additional/Custom Category Attributes

Home / Blog / Magento – Automate the Creation of Additional/Custom Category Attributes

If you search the net for how to add new custom attributes to categories, you’ll find many sites that define the steps in manual SQL inserts. The issue I found was that most of these sites are specific to Magento versions previous to 1.4/Enterprise 1.9 and leave out a crucial step. Also, I find it to be bad practice to manually insert rows into your Magento database because it makes it hard to deploy across environments.

The general approach to create custom category attributes is the following 3 SQL inserts:

INSERT INTO `eav_attribute` (
				`attribute_id` ,
				`entity_type_id` ,
				`attribute_code` ,
				`attribute_model` ,
				`backend_model` ,
				`backend_type` ,
				`backend_table` ,
				`frontend_model` ,
				`frontend_input` ,
				`frontend_label` ,
				`frontend_class` ,
				`source_model` ,
				`is_required` ,
				`is_user_defined` ,
				`default_value` ,
				`is_unique` ,
				`note`
	)
	VALUES (
			NULL ,  '3',  'header_image', NULL ,  'catalog/category_attribute_backend_image',  'varchar', NULL , NULL ,  'image',  'Header Image', NULL , NULL ,  '0',  '0', NULL ,  '0',  ''
	);

	SET @attribute_id = LAST_INSERT_ID();
	
	INSERT INTO `eav_entity_attribute` (
				`entity_attribute_id` ,
				`entity_type_id` ,
				`attribute_set_id` ,
				`attribute_group_id` ,
				`attribute_id` ,
				`sort_order`
	)
	VALUES (
		NULL ,  '3',  '3',  '3',  @attribute_id,  '30'
	);
	
	INSERT INTO `catalog_eav_attribute` (
				`attribute_id` ,
				`frontend_input_renderer` ,
				`is_global` ,
				`is_visible` ,
				`is_searchable` ,
				`is_filterable` ,
				`is_comparable` ,
				`is_visible_on_front` ,
				`is_html_allowed_on_front` ,
				`is_used_for_price_rules` ,
				`is_filterable_in_search` ,
				`used_in_product_listing` ,
				`used_for_sort_by` ,
				`is_configurable` ,
				`apply_to` ,
				`is_visible_in_advanced_search` ,
				`position` ,
				`is_wysiwyg_enabled`
	)
	VALUES (
		@attribute_id, NULL ,  '1',  '1',  '0',  '0',  '0',  '0',  '0',  '0',  '0',  '0',  '0',  '1',  '',  '0',  '',  '0'
	);

The above code is specific to one of my Magento installs, which in this case I’m creating a custom image attribute. You can create any sort of attribute by identifying a similar attribute and copying most of the columns.

Now, this alone will give you your custom attribute, and if you only have one environment, this may be sufficient. Most projects, however, have development, test, and production environments, and if you manually insert SQL, it can be hard to ensure data consistency.

For this reason, its good practice to create a custom module where you can create a sql setup script that will automatically run and insert your desired columns. For my project, I created a module called Brim_Catalog with the following folder hierarchy:

-app
– code
– local
– Brim
– Catalog
– etc (create a config.xml file here)
– sql
– brim_catalog_setup (create a mysql-install-0.1.0.php here)

Now, in your config.xml, create the following resource declaration:


<resources>
            <brim_catalog_setup>
                <setup>
                    <module>Brim_Catalog</module>
                </setup>
                <connection>
                    <use>core_setup</use>
                </connection>
            </brim_catalog_setup>
        </resources>

Finally, you can define your SQL in the mysql-install-0.1.0.php file as such:



startSetup();

// create a new category attribute 'header_image' to display as the header on category landing pages
$installer->run("
	INSERT INTO `eav_attribute` (
				`attribute_id` ,
				`entity_type_id` ,
				`attribute_code` ,
				`attribute_model` ,
				`backend_model` ,
				`backend_type` ,
				`backend_table` ,
				`frontend_model` ,
				`frontend_input` ,
				`frontend_label` ,
				`frontend_class` ,
				`source_model` ,
				`is_required` ,
				`is_user_defined` ,
				`default_value` ,
				`is_unique` ,
				`note`
	)
	VALUES (
			NULL ,  '3',  'header_image', NULL ,  'catalog/category_attribute_backend_image',  'varchar', NULL , NULL ,  'image',  'Header Image', NULL , NULL ,  '0',  '0', NULL ,  '0',  ''
	);

	SET @attribute_id = LAST_INSERT_ID();
	
	INSERT INTO `eav_entity_attribute` (
				`entity_attribute_id` ,
				`entity_type_id` ,
				`attribute_set_id` ,
				`attribute_group_id` ,
				`attribute_id` ,
				`sort_order`
	)
	VALUES (
		NULL ,  '3',  '3',  '3',  @attribute_id,  '30'
	);
	
	INSERT INTO `catalog_eav_attribute` (
				`attribute_id` ,
				`frontend_input_renderer` ,
				`is_global` ,
				`is_visible` ,
				`is_searchable` ,
				`is_filterable` ,
				`is_comparable` ,
				`is_visible_on_front` ,
				`is_html_allowed_on_front` ,
				`is_used_for_price_rules` ,
				`is_filterable_in_search` ,
				`used_in_product_listing` ,
				`used_for_sort_by` ,
				`is_configurable` ,
				`apply_to` ,
				`is_visible_in_advanced_search` ,
				`position` ,
				`is_wysiwyg_enabled`
	)
	VALUES (
		@attribute_id, NULL ,  '1',  '1',  '0',  '0',  '0',  '0',  '0',  '0',  '0',  '0',  '0',  '1',  '',  '0',  '',  '0'
	);
");

$installer->endSetup();

That’s all there is to it. Now, every environment that you deploy your code to will be guaranteed to have the same custom category attributes.

Showing 3 comments
  • Srdjan

    Thanks! Good to have SQL background! :)

  • Imran

    Hi iam newbee in magento, i have the same requirement what u have explained in the above tutorial but iam not able to excute it , i have gone through both the steps one is directly executing in mysql and other by creating the module but not have luck , not got the desired result i.e. a custom attribute in catagory ..
    please help me in this regard , how to create the module in details , is there any steps im missing in creating the module or while executing the sql quires..

    thanks in advance …
    with regards..
    imran

  • tmillhouse

    Sorry for the late response. I haven’t fooled around with this particular code in a while, and I’d need to revisit before I could help debug. If you’re still having this issue, let me know if there are is a specific error that you’re encountering, and I’ll see if I can debug from there.

Leave a Comment