Select a list of sales_order attributes from Magento

I was diagnosing an integration issue with Magento where the postal code validation on our side was failing on a few imported sales orders. It seems that the version of Magento our client is using thinks that “MI” is a perfectly good postal code for Lake Orion, Michigan.

No problem, just need to edit the order in Magento and fix the problem… Oh, wait, the original implementers of the client’s system figured it’d be OK to just disabled the Order Edit functionality because it was too much work to get it to work with their new “Features”.

Ok, looks like the only easy solution is to go straight to MySQL and make the change there. Not ideal, but will get this handful of orders to come through correctly. Playing around with Magento and the extensible nature of their database design, it’s a bit confusing to get at a list of the appropriate sales_order attributes. You can use the following MySQL query to get a list of the varchar attributes of a sales order.

select v.*

from sales_order s

    left join sales_order_entity e on (s.entity_id = e.parent_id)

    left join sales_order_entity_varchar v on (e.entity_id = v.entity_id)

where s.increment_id = 1

The results of the list will be returned in a flat list of attribute values associated with the entity associated with the sales order. With these results, I can easily find the value for the attribute I want to update.

value_id	entity_type_id	attribute_id	entity_id	value
796932	12	211	1	Shipping
796933	12	216	1	John
796934	12	218	1	Doe
796935	12	220	1
796936	12	221	1	1345 King Street
796937	12	222	1	Lake Orion
796938	12	223	1	Michigan
796939	12	225	1	MI
796940	12	226	1	US