Export DB query, Products with Supplier ID columns [Solved by ChatGPT]

[Eutanasio]

Hi!

I'm trying to extract from the admin a table with a list of products that includes the "supplier ID" (see 3rd line on this query) of the products in a column, but I get it empty:

SELECT SQL_CALC_FOUND_ROWS p.`id_product`  AS `id_product`,
 p.`reference`  AS `reference`,
 id.supplier,
 ean13,
 upc,
isbn,
 sa.`price`  AS `pvp ht`,
 p.`wholesale_price` AS `achat ht`,
 pl.`name`  AS `nom`,
 cl.`name`  AS `name_category`,
 sav.`quantity`  AS `qte`
FROM  `ps_product` p 
 LEFT JOIN `ps_product_lang` pl ON (pl.`id_product` = p.`id_product` AND pl.`id_lang` = 3 AND pl.`id_shop` = 1) 
 LEFT JOIN `ps_stock_available` sav ON (sav.`id_product` = p.`id_product` AND sav.`id_product_attribute` = 0 AND sav.id_shop = 1  AND sav.id_shop_group = 0 ) 
 JOIN `ps_product_shop` sa ON (p.`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 cl.`id_lang` = 3 AND cl.id_shop = 1) 
 LEFT JOIN `ps_category` c ON (c.`id_category` = cl.`id_category`) 
 LEFT JOIN `ps_shop` shop ON (shop.id_shop = 1) 
 LEFT JOIN `ps_image_shop` image_shop ON (image_shop.`id_product` = p.`id_product` AND image_shop.`cover` = 1 AND image_shop.id_shop = 1) 
 LEFT JOIN `ps_image` i ON (i.`id_image` = image_shop.`id_image`) 
 LEFT JOIN `ps_product_download` pd ON (pd.`id_product` = p.`id_product`) 
WHERE (1 AND cl.`name` LIKE '%home%' AND sav.`quantity`  >=1  AND sa.`active` = 0 AND state = 1)
 
ORDER BY  `id_product` desc
 
LIMIT 0, 1700
;

The ID SUPPLIER is the one on the "Option" tab of the products, at the bottom of the page.

Thanks for the help!

[WebDesk Solution]

Hello @omar2886

To obtain a comprehensive list of products containing the "supplier ID", please utilize the following query.

SELECT SQL_CALC_FOUND_ROWS

    p.`id_product` AS `id_product`,
    p.`reference` AS `reference`,
    sa.`price` AS `price`,
    p.`id_shop_default` AS `id_shop_default`,
    p.`is_virtual` AS `is_virtual`,
    `p`.`id_supplier` AS `Supplier ID`,
    pl.`name` AS `name`,
    pl.`link_rewrite` AS `link_rewrite`,
    sa.`active` AS `active`,
    shop.`name` AS `shopname`,
    image_shop.`id_image` AS `id_image`,
    cl.`name` AS `name_category`,
    0 AS `price_final`,
    pd.`nb_downloadable` AS `nb_downloadable`,
    sav.`quantity` AS `sav_quantity`,
    IF(sav.`quantity` <= 0, 1, 0) AS `badge_danger`
FROM
    `ps_product` p
LEFT JOIN `ps_product_lang` pl ON
    (
        pl.`id_product` = p.`id_product` AND pl.`id_lang` = 1 AND pl.`id_shop` = 1
    )
LEFT JOIN `ps_stock_available` sav ON
    (
        sav.`id_product` = p.`id_product` AND sav.`id_product_attribute` = 0 AND sav.id_shop = 1 AND sav.id_shop_group = 0
    )
JOIN `ps_product_shop` sa ON
    (
        p.`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 cl.`id_lang` = 1 AND cl.id_shop = 1
    )
LEFT JOIN `ps_category` c ON

    (c.`id_category` = cl.`id_category`)
LEFT JOIN `ps_shop` shop ON
    (shop.id_shop = 1)
LEFT JOIN `ps_image_shop` image_shop ON
    (
        image_shop.`id_product` = p.`id_product` AND image_shop.`cover` = 1 AND image_shop.id_shop = 1
    )
LEFT JOIN `ps_image` i ON
    (
        i.`id_image` = image_shop.`id_image`
    )
LEFT JOIN `ps_product_download` pd ON
    (pd.`id_product` = p.`id_product`)
WHERE
    (1 AND state = 1) AND p.id_supplier != 0
ORDER BY
    `id_product`
DESC
LIMIT 0, 20;

We hope this solution will work for you.

Thanks.

Edited March 13, 2023 by WebDesk Solution (see edit history)

[Eutanasio]

2 hours ago, WebDesk Solution said:

Hello @omar2886

To obtain a comprehensive list of products containing the "supplier ID", please utilize the following query.

SELECT SQL_CALC_FOUND_ROWS

    p.`id_product` AS `id_product`,
    p.`reference` AS `reference`,
    sa.`price` AS `price`,
    p.`id_shop_default` AS `id_shop_default`,
    p.`is_virtual` AS `is_virtual`,
    `p`.`id_supplier` AS `Supplier ID`,
    pl.`name` AS `name`,
    pl.`link_rewrite` AS `link_rewrite`,
    sa.`active` AS `active`,
    shop.`name` AS `shopname`,
    image_shop.`id_image` AS `id_image`,
    cl.`name` AS `name_category`,
    0 AS `price_final`,
    pd.`nb_downloadable` AS `nb_downloadable`,
    sav.`quantity` AS `sav_quantity`,
    IF(sav.`quantity` <= 0, 1, 0) AS `badge_danger`
FROM
    `ps_product` p
LEFT JOIN `ps_product_lang` pl ON
    (
        pl.`id_product` = p.`id_product` AND pl.`id_lang` = 1 AND pl.`id_shop` = 1
    )
LEFT JOIN `ps_stock_available` sav ON
    (
        sav.`id_product` = p.`id_product` AND sav.`id_product_attribute` = 0 AND sav.id_shop = 1 AND sav.id_shop_group = 0
    )
JOIN `ps_product_shop` sa ON
    (
        p.`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 cl.`id_lang` = 1 AND cl.id_shop = 1
    )
LEFT JOIN `ps_category` c ON

    (c.`id_category` = cl.`id_category`)
LEFT JOIN `ps_shop` shop ON
    (shop.id_shop = 1)
LEFT JOIN `ps_image_shop` image_shop ON
    (
        image_shop.`id_product` = p.`id_product` AND image_shop.`cover` = 1 AND image_shop.id_shop = 1
    )
LEFT JOIN `ps_image` i ON
    (
        i.`id_image` = image_shop.`id_image`
    )
LEFT JOIN `ps_product_download` pd ON
    (pd.`id_product` = p.`id_product`)
WHERE
    (1 AND state = 1) AND p.id_supplier != 0
ORDER BY
    `id_product`
DESC
LIMIT 0, 20;

We hope this solution will work for you.

Thanks.

Thanks but it doesn't work, gives no result at all. My query works just fine, the only thing I need to add is the column with supplier id

[WebDesk Solution]

20 hours ago, omar2886 said:

Thanks but it doesn't work, gives no result at all. My query works just fine, the only thing I need to add is the column with supplier id

Based on our analysis, we have verified that the query you provided is functioning properly. However, it appears that you have included a condition within the query to retrieve a product that is currently inactive. As a result, there is a possibility that the query may not return any results.

To obtain accurate results, we suggest modifying the query to exclude the inactive product condition. This will ensure that the query returns appropriate results as intended.

Change 1 : https://prnt.sc/XCHTGYpryFRX
Change 2 : https://prnt.sc/6_OOenmBP3Ab

Hope this helps!

[Eutanasio]

11 minutes ago, WebDesk Solution said:

Based on our analysis, we have verified that the query you provided is functioning properly. However, it appears that you have included a condition within the query to retrieve a product that is currently inactive. As a result, there is a possibility that the query may not return any results.

To obtain accurate results, we suggest modifying the query to exclude the inactive product condition. This will ensure that the query returns appropriate results as intended.

Change 1 : https://prnt.sc/XCHTGYpryFRX
Change 2 : https://prnt.sc/6_OOenmBP3Ab

Hope this helps!

Thanks for following up with me and the screenshots. I've tried using id_supplier and this column appears is 0's in all rows, which is strange as for the products I have either nothing on that field or an alphanumeric reference.

Thanks

[Eutanasio]

I finally decided to ask ChatGPT and it immediately gave me a working code! 🤣

SELECT SQL_CALC_FOUND_ROWS p.`id_product`  AS `id_product`,
 p.`reference`  AS `reference`,
 ps.`product_supplier_reference` AS supplier_reference,
 ean13,
 upc,
isbn,
 sa.`price`  AS `pvp ht`,
 p.`wholesale_price` AS `achat ht`,
 pl.`name`  AS `nom`,
 cl.`name`  AS `name_category`,
 sav.`quantity`  AS `qte`
FROM  `ps_product` p 
 LEFT JOIN `ps_product_lang` pl ON (pl.`id_product` = p.`id_product` AND pl.`id_lang` = 3 AND pl.`id_shop` = 1) 
 LEFT JOIN `ps_stock_available` sav ON (sav.`id_product` = p.`id_product` AND sav.`id_product_attribute` = 0 AND sav.id_shop = 1  AND sav.id_shop_group = 0 ) 
 JOIN `ps_product_shop` sa ON (p.`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 cl.`id_lang` = 3 AND cl.id_shop = 1) 
 LEFT JOIN `ps_category` c ON (c.`id_category` = cl.`id_category`) 
 LEFT JOIN `ps_shop` shop ON (shop.id_shop = 1) 
 LEFT JOIN `ps_image_shop` image_shop ON (image_shop.`id_product` = p.`id_product` AND image_shop.`cover` = 1 AND image_shop.id_shop = 1) 
 LEFT JOIN `ps_image` i ON (i.`id_image` = image_shop.`id_image`) 
 LEFT JOIN `ps_product_download` pd ON (pd.`id_product` = p.`id_product`)
 LEFT JOIN `ps_product_supplier` ps ON (ps.`id_product` = p.`id_product` AND ps.`id_product_attribute` = 0)
WHERE (1 AND cl.`name` LIKE '%home%' AND sav.`quantity`  >=1  AND sa.`active` = 0 AND state = 1)

ORDER BY  `id_product` desc

LIMIT 0, 1700
;