Magento products import from database using SQL query

Magento products import from database using SQL query

August 11, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Magento products import from database using SQL query

Magento Products Import: Fetching Additional Product Attributes

As a seasoned developer working on Magento projects, I often need to fetch product information from the database for various purposes like data migration, custom reports, or integrations with other systems. The default Magento EAV (Entity-Attribute-Value) structure can be complex to navigate, especially when you want to retrieve multiple attributes in one query.

You’ve identified the core query to get product_id and name, which is an excellent starting point. Let’s build upon this to fetch additional product attributes like url_rewrite, price, image_url, description, and manufacturer.

Fetching Product URL

To get the product URL, you need to join the catalog_product_entity table with the core_url_rewrite table. Here’s an updated query:

SELECT e.entity_id AS product_id,
       var.value AS product_name,
       u.query AS product_url
FROM catalog_product_entity e
JOIN eav_attribute eav ON e.entity_type_id = eav.entity_type_id
JOIN catalog_product_entity_varchar var ON eav.attribute_id = var.attribute_id AND var.entity_id = e.entity_id
LEFT JOIN core_url_rewrite u ON e.entity_id = u.product_id AND u.store_id = 0 AND u.query IS NOT NULL
WHERE eav.attribute_code IN ('name', 'url_key')

Fetching Price, Description, and Manufacturer Attributes

For attributes like price, description, and manufacturer, you’ll need to join the tables based on the attribute set ID:

SELECT e.entity_id AS product_id,
       var.value AS product_name,
       u.query AS product_url,
       eav2.value AS price,
       eav3.value AS description,
       eav4.value AS manufacturer
FROM catalog_product_entity e
JOIN eav_attribute eav ON e.entity_type_id = eav.entity_type_id
JOIN catalog_product_entity_varchar var ON eav.attribute_id = var.attribute_id AND var.entity_id = e.entity_id
LEFT JOIN core_url_rewrite u ON e.entity_id = u.product_id AND u.store_id = 0 AND u.query IS NOT NULL
JOIN catalog_product_entity_int eav2 ON e.entity_id = eav2.entity_id AND eav2.attribute_id IN (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'price')
JOIN catalog_product_entity_text eav3 ON e.entity_id = eav3.entity_id AND eav3.attribute_id IN (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'description')
JOIN catalog_product_entity_int eav4 ON e.entity_id = eav4.entity_id AND eav4.attribute_id IN (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'manufacturer')
WHERE eav.attribute_code IN ('name', 'url_key') OR eav2.attribute_code = 'price' OR eav3.attribute_code = 'description' OR eav4.attribute_code = 'manufacturer'

Fetching Image URL

For the product image, you’ll need to join with the catalog_product_entity_media_gallery table:

SELECT e.entity_id AS product_id,
       var.value AS product_name,
       u.query AS product_url,
       eav2.value AS price,
       eav3.value AS description,
       eav4.value AS manufacturer,
       mgg.gallery_file
FROM catalog_product_entity e
JOIN eav_attribute eav ON e.entity_type_id = eav.entity_type_id
JOIN catalog_product_entity_varchar var ON eav.attribute_id = var.attribute_id AND var.entity_id = e.entity_id
LEFT JOIN core_url_rewrite u ON e.entity_id = u.product_id AND u.store_id = 0 AND u.query IS NOT NULL
JOIN catalog_product_entity_int eav2 ON e.entity_id = eav2.entity_id AND eav2.attribute_id IN (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'price')
JOIN catalog_product_entity_text eav3 ON e.entity_id = eav3.entity_id AND eav3.attribute_id IN (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'description')
JOIN catalog_product_entity_int eav4 ON e.entity_id = eav4.entity_id AND eav4.attribute_id IN (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'manufacturer')
LEFT JOIN catalog_product_entity_media_gallery mg ON e.entity_id = mg.entity_id
LEFT JOIN catalog_product_entity_media_content mgc ON mg.value = mgc.file
WHERE eav.attribute_code IN ('name', 'url_key') OR eav2.attribute_code = 'price' OR eav3.attribute_code = 'description' OR eav4.attribute_code = 'manufacturer'

Conclusion

Navigating the Magento database requires a solid understanding of its EAV structure and careful handling of joins to avoid performance issues. The queries provided above should give you a comprehensive set of product attributes, including product_id, name, url_rewrite, price, description, manufacturer, and image_url.

Remember to test your queries on a development or staging environment before running them in production. Also, consider optimizing your database structure if you frequently perform complex queries.

To learn more about Magento’s EAV system and advanced query techniques, I recommend checking out the official documentation: Magento Developer Documentation.

Happy coding!

Catalog Database Pricing Import/Export

Our network