how to get full details of each product item in mysql database in prestashop

Question

i'm trying to get full details of each item(product id, product name, reference code, features, description_short, description) in database but i don't know the exact query for this, my database is mysql all the details required for the item is resided in different tables... each feature name has different id's and each feature value has different id's, i don't know the query for getting all the details for an item..

please send me the query for this....

thanks in advance

Answer 1

please try this query

SELECT
        a.`id_product`,b.name as name,`reference`,a.price as price,sa.active as active
        , shp.name as shopname, a.id_shop_default, MAX(image_shop.id_image) id_image, cl.name `name_category`, sa.`price`, 0 AS price_final, sav.`quantity` as sav_quantity, sa.`active`
        FROM `ps_product` a
        LEFT JOIN `ps_product_lang` b ON (b.`id_product` = a.`id_product` AND b.`id_lang` = 1 AND b.`id_shop` = 1)

        LEFT JOIN `ps_image` i ON (i.`id_product` = a.`id_product`)
        LEFT JOIN `ps_stock_available` sav ON (sav.`id_product` = a.`id_product` AND sav.`id_product_attribute` = 0
         AND sav.id_shop = 1 )  JOIN `ps_product_shop` sa ON (a.`id_product` = sa.`id_product` AND sa.id_shop = 1)
                LEFT JOIN `ps_category_lang` cl ON (sa.`id_category_default` = cl.`id_category` AND b.`id_lang` = cl.`id_lang` AND cl.id_shop = 1)
                LEFT JOIN `ps_shop` shop ON (shop.id_shop = 1) 
                LEFT JOIN `ps_image_shop` image_shop ON (image_shop.`id_image` = i.`id_image` AND image_shop.`cover` = 1 AND image_shop.id_shop = 1) 

        GROUP BY sa.id_product