How to create a list of all products (Presta 1.6)

[Scientific]

Hello everyone, please from the experienced of you advice. How can I create complete listings from the database of all products?

Please have someone for me a functional SQL query that returns a list of all products?

Thank you very much for your help.

I found some, but neither works (returns empty result) id_lang I edited:

SELECT
    p.id_product,
    pa.reference,
    pl.name,
    @id_image := ifnull(pai.id_image, pi.id_image) as id_image,
    concat('http://',
            ifnull(shop_domain.value, 'domain'),
            '/img/p/',
            if(CHAR_LENGTH(@id_image) >= 5,
                concat(SUBSTRING(@id_image from - 5 FOR 1), '/'),
                ''),
            if(CHAR_LENGTH(@id_image) >= 4,
                concat(SUBSTRING(@id_image from - 4 FOR 1), '/'),
                ''),
            if(CHAR_LENGTH(@id_image) >= 3,
                concat(SUBSTRING(@id_image from - 3 FOR 1), '/'),
                ''),
            if(CHAR_LENGTH(@id_image) >= 2,
                concat(SUBSTRING(@id_image from - 2 FOR 1), '/'),
                ''),
            if(CHAR_LENGTH(@id_image) >= 1,
                concat(SUBSTRING(@id_image from - 1 FOR 1), '/'),
                ''),
            @id_image,
            '.jpg') as image_url,
    GROUP_CONCAT(DISTINCT (pal.name)
        SEPARATOR ', ') as combination,
    ROUND(p.price, 2) as price,
    p.active,
    pq.quantity
FROM
    ps_product p
        LEFT JOIN
    ps_product_attribute pa ON (p.id_product = pa.id_product)
        LEFT JOIN
    ps_stock_available pq ON (p.id_product = pq.id_product
        AND pa.id_product_attribute = pq.id_product_attribute)
        LEFT JOIN
    ps_product_lang pl ON (p.id_product = pl.id_product)
        LEFT JOIN
    ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute)
        LEFT JOIN
    ps_attribute_lang pal ON (pac.id_attribute = pal.id_attribute)
        LEFT JOIN
    ps_product_attribute_image pai ON (pa.id_product_attribute = pai.id_product_attribute)
        LEFT JOIN
    ps_image pi ON p.id_product = pi.id_product
        LEFT JOIN
    ps_configuration shop_domain ON shop_domain.name = 'PS_SHOP_DOMAIN'
WHERE
    pl.id_lang = (
        SELECT
            id_lang
        FROM
            ps_lang
        ORDER BY id_lang ASC
        LIMIT 1
    )
        AND
    pal.id_lang = pl.id_lang
GROUP BY pa.reference
ORDER BY p.id_product , pac.id_attribute;

And this also not working:

SELECT
    p.id_product,
    pa.reference,
    pl.name,
    GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ", ") as combination,
    p.price,
    pq.quantity
FROM ps_product p
LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product AND pa.id_product_attribute = pq.id_product_attribute)
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute)
LEFT JOIN ps_attribute_lang pal ON (pac.id_attribute = pal.id_attribute)
WHERE pl.id_lang = 1
AND pal.id_lang = 1
GROUP BY pa.reference
ORDER BY p.id_product, pac.id_attribute

[Scientific]

This working fine for me, but not containst necesary data like price, vat, description and images of products. Please how to add this infromations to result of SQL querry?

SELECT
            p.id_product,
            pa.id_product_attribute as id_product_attribute,
            pa.reference,
            CONCAT(p.supplier_reference, " - ", pa.supplier_reference) as supplier_reference,
            pa.upc,
            pa.ean13,
            p.price,
            pl.name,
            GROUP_CONCAT(DISTINCT(pal.name) SEPARATOR ", ") as combination,
            pq.quantity, pa.weight, p.width, p.depth, p.height
        FROM ps_product p
        LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product)
        LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product AND pa.id_product_attribute = pq.id_product_attribute)
        LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
        LEFT JOIN ps_product_attribute_combination pac ON (pa.id_product_attribute = pac.id_product_attribute)
        LEFT JOIN ps_attribute_lang pal ON (pac.id_attribute = pal.id_attribute)
        LEFT JOIN ps_product_attribute_image pai on(pa.id_product_attribute = pai.id_product_attribute)
        WHERE pl.id_lang = 6
        AND pal.id_lang = 6
        GROUP BY pa.reference

        union

        SELECT
            p.id_product,
            "" as id_product_attribute,
            p.reference,
            p.supplier_reference,
            p.upc,
            p.ean13,
            p.price,
            pl.name,
            "" as combination,
            p.quantity, p.weight, p.width, p.depth, p.height
        FROM ps_product p
        LEFT JOIN ps_stock_available pq ON (p.id_product = pq.id_product)
        LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
        WHERE pl.id_lang = 6
        GROUP BY p.reference

        ORDER BY id_product, id_product_attribute