I have a large number of color attribute names in Magento. I mean a LARGE number – several thousand. For this reason loading the color attribute page in the Magento admin gets to be a bit cumbersome and hard to manage. In addition, there are a few color attributes that have null values for one reason or another and thus the page throws errors when I attempt to save any changes in the Magento Admin for the color attribute.
That said, there are at times changes need to be made. While this may sound like a “sloppy developer” comment, rather than sort out the issues with the null attributes, this is fairly simple to accomplish directly in the database.
Contents
Magento EAV Tables
There are two tables that are needed to accomplish this task.
| Table Name | Description | Table Contents |
| eav_attribute_option | The attributes and their sort order. | option_id: The attribute ID attribute_id: ID of the attribute (example: color, gender, size, etc.) sort_order: The sort order value for this attribute |
| eav_attribute_option_value | The text to be displayed for each attribute per store. | value_id: ID of the value option_id: The option id – directly correlates to the option_id in the eav_attribute_option table store_id: Store ID this attribute value is associated to value: The text value that is tied to this attribute option |
As you can see, these sort_order that we need to modify is in the eav_attribute_option table, but it’s tied directly to the ID of the attribute. If you know your attribute ID then this is a simple fix – just work directly within that table. But if you don’t know the attribute ID’s you are attempting to modify this appears difficult at first, but can be solved with a simple join query.
Find the Attribute ID
The one thing you do need to know is the attribute_id that you are working with. If you don’t know it, it can be found in the Magento Admin via the URL for the attribute.
- In the Magento Admin choose: Catalog -> Attributes -> Manage Attributes
- Select the attribute you are working with
- Look at the resulting URL – it should be something like:
https://www.yoursite.com/index.php/admin/catalog_product_attribute/edit/attribute_id/50/ - The number at the end of the URL, 50 in this example, is the attribute ID you are looking for.
Alternatively, this can be found on the frontend of the website.
- Right click on the dropdown or radio button that correlates to the attribute you are working with and choose Inspect Element. (Note: This should work in all modern browsers, but each implements this slightly different.)
- In the window that opens with the HTML code of the page, you’ll see the HTML input for that attribute. The attribute ID is part of the name and ID for the input fields. Here is an example of what the code might look like – notice the number 50, which is the attribute ID for this example.
- 1234<select name="super_attribute[50]" id="attribute50" class="required-entry super-attribute-select"><option value="123">Red</option><option value="124">Blue</option></select>
NOTE: If you use this option you could actually skip over the join query section and go right to the Change the Sort Order section below. Look for the shortcut option.
Join Query To Get Attribute With Text
With the attribute ID we can now run a query in the database to make this more accessible.
I’m listing several queries to accomplish this depending on your needs.
All Attributes With Attribute ID
Notice the attribute id of 50 is at the end of the where clause.
1 2 3 4 5 | SELECT * FROM eav_attribute_option_value JOIN eav_attribute_option ON eav_attribute_option_value.option_id=eav_attribute_option.option_id WHERE eav_attribute_option.attribute_id=50 |
Specific Attributes Based on Text Value
To narrow down the results of this query a bit more, we can list what attribute values we want returned. Looking at the example above, there are only two colors – Red and Blue. See how they are added at the end of the query below separated by a comma in quotes? That means we will just get those two returned in our results.
Also notice the attribute id of 50 is at the end of the where clause.
1 2 3 4 5 6 | SELECT * FROM eav_attribute_option_value JOIN eav_attribute_option ON eav_attribute_option_value.option_id=eav_attribute_option.option_id WHERE eav_attribute_option.attribute_id=50 AND eav_attribute_option_value.value IN ("Red", "Blue") |
Change The Sort Order
Sadly from the join query above you won’t be able to update the database records. So first thing’s first – save those results in some way. Take a screenshot or export them to a spreadsheet, open a new query window for the next step or whatever you need to do to be able to reference this result again quickly.
Now we need to run another query that will allow us to view the attributes we are interested in updating in the eav_attribute_option table so that we can set the sort order.
From the result of the above copy out the column of option_id‘s and convert them to a comma separated string. Example:
1 | 123, 124 |
SHORTCUT: If you used the step above to view your input box HTML code, you’ll notice these are the same ID’s for each of the drop down or radio button items. For that reason, you could have really skipped over the join query step and gone right to this step.
We’ll now run one more query to get only these options. This one is simpler.
1 | SELECT * FROM eav_attribute_option WHERE option_id IN (123, 124) |
Notice that you put the string of option_id‘s inside the parenthesis at the end of the query.
When you run this query you get the results of just the options you were trying to focus in on. Now you can update the sort order in your database and you should be all set to go.
It may be worth using numbers that aren’t right at the top of the numeric list. For example, if you use 1, 2, 3 and then later on need to make a change and put another item above this one you’ll have to change all of the values. If you instead use something like 100, 110, 120 this gives you some wiggle room. If another color needs to go into position #2 you can use 105. Then another that should top the list, try 90. This saves some time in the long run.
