Add custom column through mysql

Question

I'm creating a service to import data from another cart to magento. Due to the amount of data we're importing, I'm importing straight into the magento db to create the customers, addresses, orders, products, etc.

Can someone point me to the best way to add a column to the tables that will hold the old store ids for cross referencing back to the old data - AND for it to be seen in admin.

Thanks

Answer 1

In addition to clockworkgeek's point about later upgrades to the system, you also avoid much of the complexity of Magento's EAV system by using the framework.

User data in Magento is strung out between the customer_entity_* tables that you mentioned, and adding new data to the system will require you to grab each of the column definitions (from eav_attribute) and place the data appropriately. This is even more of a pain than it seems.

In summary, no, your update statement is not enough to save user data. Magento executes dozens of queries to add customers to the system, and you'll need do the same.

Source:

• https://stackoverflow.com/questions/4197878/adding-customers-to-magento-in-raw-sql

---

EDIT:

Then, A quick approach would be to enable SQL Logging, create the needed customer attributes and view the log of queries generated:

In Varien_Db_Adapter_Pdo_Mysql

Magento 1.4 : lib/varien/Db/Adapter/Pdo/Mysql.php

set

protected $_debug               = true;
protected $_logAllQueries       = true;

and (if nor already there) create the folder defined in

protected $_debugFile           = 'var/debug/sql.txt';

Give read / write permission

• https://stackoverflow.com/a/4460639/158325

Answer 2

After some experimenting - here's how for anyone that may need it.

step 1 - create attribute You need to determine from the entity_types table what kind of entity (1-8) for your type. In this example - products.

INSERT INTO magento.eav_attribute
(entity_type_id,attribute_code,backend_type,frontend_input,frontend_label,frontend_class,is_required,is_user_defined,is_unique) VALUES (4,'lcase_no_space_name','varchar','text','Foo','validate-digits',0,1,0);

set @attrbuteid = LAST_INSERT_ID();

step 2 - insert into catalog_eav_attribute

INSERT INTO magento.catalog_eav_attribute (attribute_id,is_global,is_configurable) VALUES (@attrbuteid,0,0);

step 3 - this determines where it shows. You'll need to determine what attribute_set_id (eav_attribute_set table) and what attribute_group_id (eav_attribute_group table) is. This is for a simple product. For configurable, you'll need to use the ids for the configurable items.

INSERT INTO magento.eav_entity_attribute (entity_type_id,attribute_set_id,attribute_group_id,attribute_id,sort_order) VALUES (4,4,7,@attrbuteid,31);