Hi,
it's possible make a query to get products id, sku, name, img, stock qty, price, from magento db.
i need all of these columns (id, sku, name, img, stock qty, price) .
my knowledge of mysql is limited
SELECT A.entity_id, X.sku, A.value as name, B.value as img FROM catalog_product_entity_varchar A INNER JOIN catalog_product_entity X ON A.entity_id = X.entity_id LEFT JOIN catalog_product_entity_varchar B ON A.entity_id = B.entity_id WHERE A.attribute_id = 71 and B.attribute_id = 85 and (X.attribute_set_id = 13 or X.attribute_set_id = 11)
With this query i get entity_id, sku, name, img, can i join this with the remaining columns, price, stock qty ?
Solved! Go to Solution.
It's always difficult if you do not know the exact attribute IDs. Also you didn't mentioned, if you use a multistore setup or which image you want to export (image, small_image, thumbnail). Anyway, here is a generic query that fetches the admin store values and image only:
-- Product entity type ID SET @etype = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product'); -- Product name attribute ID SET @name = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = @etype); -- Product image attribute ID SET @image = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'image' AND entity_type_id = @etype); -- Product price attribute ID SET @price = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'price' AND entity_type_id = @etype); -- Admin store ID SET @store = 0; -- Query SELECT e.entity_id AS 'id', e.sku, v1.value AS 'name', v2.value AS 'image', si.qty AS 'stock qty', d1.value AS 'price' FROM catalog_product_entity e LEFT JOIN cataloginventory_stock_item si ON e.entity_id = si.product_id LEFT JOIN catalog_product_entity_varchar v1 ON e.entity_id = v1.entity_id AND v1.store_id = @store AND v1.attribute_id = @name LEFT JOIN catalog_product_entity_varchar v2 ON e.entity_id = v2.entity_id AND v2.store_id = @store AND v2.attribute_id = @image LEFT JOIN catalog_product_entity_decimal d1 ON e.entity_id = d1.entity_id AND d1.store_id = @store AND d1.attribute_id = @price;
If you know the attribute IDs, you can replace the variables of course.
It's always difficult if you do not know the exact attribute IDs. Also you didn't mentioned, if you use a multistore setup or which image you want to export (image, small_image, thumbnail). Anyway, here is a generic query that fetches the admin store values and image only:
-- Product entity type ID SET @etype = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product'); -- Product name attribute ID SET @name = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = @etype); -- Product image attribute ID SET @image = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'image' AND entity_type_id = @etype); -- Product price attribute ID SET @price = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'price' AND entity_type_id = @etype); -- Admin store ID SET @store = 0; -- Query SELECT e.entity_id AS 'id', e.sku, v1.value AS 'name', v2.value AS 'image', si.qty AS 'stock qty', d1.value AS 'price' FROM catalog_product_entity e LEFT JOIN cataloginventory_stock_item si ON e.entity_id = si.product_id LEFT JOIN catalog_product_entity_varchar v1 ON e.entity_id = v1.entity_id AND v1.store_id = @store AND v1.attribute_id = @name LEFT JOIN catalog_product_entity_varchar v2 ON e.entity_id = v2.entity_id AND v2.store_id = @store AND v2.attribute_id = @image LEFT JOIN catalog_product_entity_decimal d1 ON e.entity_id = d1.entity_id AND d1.store_id = @store AND d1.attribute_id = @price;
If you know the attribute IDs, you can replace the variables of course.
lot of thx. this is what i was looking for
Hi,
This seems to work great to generate the list in PHPMyAdmin but when I export it as a CSV all the product names and images get replaced by "Null," and idea what is causing this?
Thanks
I guess, phpMyAdmin cannot handle Multi-Querys in CSV-Export. You could skip the variable assignment and merge it all together. Give it a try:
SELECT e.entity_id AS 'id', e.sku, v1.value AS 'name', v2.value AS 'image', si.qty AS 'stock qty', d1.value AS 'price' FROM catalog_product_entity e LEFT JOIN cataloginventory_stock_item si ON e.entity_id = si.product_id LEFT JOIN catalog_product_entity_varchar v1 ON e.entity_id = v1.entity_id AND v1.store_id = 0 AND v1.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product')) LEFT JOIN catalog_product_entity_varchar v2 ON e.entity_id = v2.entity_id AND v2.store_id = 0 AND v2.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'image' AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product')) LEFT JOIN catalog_product_entity_decimal d1 ON e.entity_id = d1.entity_id AND d1.store_id = 0 AND d1.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'price' AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product'));
Or just give HeidiSQL or MySQL Workbench a try, they can handle CSV-Export with multi-queries.
This is not giving correct result incase you are dealing with bundle products
Hola, muchas graciías por el aporte. Me ha servido mucho.
Alguien podría explicarme como puedo conectar HeidiSQL a la base de datos de magento?
Hi diegofdola91bc,
Connecting with HeidiSQL to a Magento DB is the same procedure as for any other DB with any other tool.
Just create a new connection in HeidiSQL and setup Hostname/IP, username and password. Here is a short How-To: https://www.inmotionhosting.com/support/website/databases/connecting-heidi-sql-database/
If your DB is bound to localhost/127.0.0.1 by configuration only, you can setup a SSH tunnel with HeidiSQL also.
I can't provide a more specific example, since i don't know your DB credentials of course ;-)
Best regards,
Peter