2

I am kind of stuck getting data from magento 1.9. The solution i needed is mysql query so that I can collect all data in one table.

The values in table i needed from different table are product id, name, sku, description, price, imagename, image path, meta title, meta description. I don't know much coding so definite needed mysql query. Looking forward a solution from experience developers. Cheers

  • +1 for the question. – Sumit Sep 14 at 18:11
  • all this data can be obtained by doing a product export to csv ... just saying, no need to query anything – jscar Sep 16 at 8:55
1

I can give you an example SQL query to get the product data on basis of attribute_id. You can use this as per your requirement.

//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 or add the variables in the above query.

Hope it helps!!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.