Magento products import from database using SQL query

Magento uses the EAV structure in its database system. I have this query that gives me produ开发者_开发知识库ct_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

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