Magento products import from database using SQL query

Magento uses the EAV structure in its database system. I have this query that gives me product_id and name of products in my magento store.

SELECT e.entity_id AS product_id, var.value AS product_name
FROM catalog_product_entity e, eav_attribute eav, catalog_product_entity_varchar var
WHERE
   e.entity_type_id = eav.entity_type_id
   AND eav.attribute_code = 'name'
   AND eav.attribute_id = var.attribute_id
   AND var.entity_id = e.entity_id

I need help in getting product_url|price|image_url|description|manufacturer

One thought on “Magento products import from database using SQL query”

  1. I’m not going to post the entire SQL query because it is far too tedious trying to get data out of Magento manually via the database, but I will say you’re on the right track. To cut down on the number of joins for this sort of thing, I retrieve my attribute_ids from the eav table and use them directly. This means that my query will only work on my install of Magento, but that hasn’t been an issue for me.

    select attribute_code, attribute_id, backend_type from eav_attribute
        where entity_type_id = (select entity_type_id from eav_entity_type where entity_type_code = 'catalog_product')
          and attribute_code in ('name', 'url_path', 'price', 'image', 'description', 'manufacturer');
    

    Yields:

    +----------------+--------------+--------------+
    | attribute_code | attribute_id | backend_type |
    +----------------+--------------+--------------+
    | description    |           61 | text         |
    | image          |           74 | varchar      |
    | manufacturer   |           70 | int          |
    | name           |           60 | varchar      |
    | price          |           64 | decimal      |
    | url_path       |           87 | varchar      |
    +----------------+--------------+--------------+
    

    Now you’re ready for tedium! For each attribute code, join against the backend table (catalog_product_entity_$BACKEND_TYPE) on your given attribute ID. For me, this would turn a sku/name/id query (your query actually doesn’t need to join against products, since you use the entity_id to make the join…) into:

    select p.sku, p.entity_id, n.value name
        from catalog_product_entity p
        join catalog_product_entity_varchar n on n.entity_id = p.entity_id
      where n.attribute_id = 60;
    

    Continue adding new join-statement|where-clause|select-clause sets until you have all the joins you wanted originally.

    That said, Jonathan is correct that using the Magento framework to manage this data would be far easier than doing it manually via the database. Unless you have an extreme number of products that you need to load all at once (note there are two assumptions there, and you can work to reduce either), it would be far more robust to use the framework.

    Hope that helps!

    Thanks,
    Joe

Leave a Reply

Your email address will not be published. Required fields are marked *