DB Query of Products info

[Alex Stefan]

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 ?

[Peter Uhlich · Accepted 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.

[Alex Stefan]

lot of thx. this is what i was looking for

[figad]

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

[Peter Uhlich]

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.

[Anurag_15]

This is not giving correct result incase you are dealing with bundle products

[diegofdola91bc]

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?  

[Peter Uhlich]

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