Besoin d'un requête SQL: export produit ID/description/EAN/Marque

[Antoine MBZH]

Bonjour,

 

Est ce que quelqu'un peut me faire une requête SQL pour un export du catalogue comprenant ces infos svp ?

- ID du produit

- Description du produit (mais sans les balises HTML)

- Le code EAN du produit

- La marque du produit

Merci d'avance

[Eolia]

A adapter suivant votre préfixe et votre id_lang:

SELECT p.id_product, p.ean13, m.name, 
ExtractValue(pl.description, '//text()') as description
FROM `ps_product`p
INNER JOIN `ps_product_lang`pl
ON(pl.id_product = p.id_product AND pl.id_lang = 1)
INNER JOIN `ps_manufacturer`m
ON(m.id_manufacturer = p.id_manufacturer)

 

[Antoine MBZH]

Merci beaucoup pour votre aimabilité et votre réactivité.

Et ça marche, c'est cool

[Eolia]

C'est le but  

[LeviaMount]

On 11/6/2025 at 7:15 PM, Antoine MBZH said:

Hello

 

Can someone give me an SQL query for an export of the catalog including this information please?

- Product ID

- Product description (but without HTML tags)

- The EAN code of the product

- The brand of the product

Thanks in advance

You can export that data with a simple SQL SELECT query, depending a bit on how your catalog tables are structured. Assuming a typical setup with products, brands, and EAN stored in separate columns, you can try something like this:
 

SELECT 
    p.product_id AS "Product ID",
    REGEXP_REPLACE(p.description, '<[^>]*>', '', 'g') AS "Product Description",
    p.ean AS "EAN Code",
    b.brand_name AS "Brand"
FROM 
    products p
LEFT JOIN 
    brands b ON p.brand_id = b.brand_id;

Explanation:

• REGEXP_REPLACE(p.description, '<[^>]*>', '', 'g') removes HTML tags from the product description.
• Works in PostgreSQL and newer MySQL versions (8.0+).
• For SQL Server, you might need a CLR function or use plain text if tags are minimal.
• The LEFT JOIN ensures all products are included even if some don’t have an associated brand.
• Replace table and column names (products, brands, etc.) with the ones from your actual schema.

If your EAN or brand is stored in related tables (for example, product_attributes), you’ll need to adjust the joins accordingly.

Let me know if you need the query for a specific SQL engine (e.g., MySQL, SQL Server, or PostgreSQL)! 
Wishing you the best!

[Eolia]

Le 08/11/2025 à 2:37 PM, LeviaMount a dit :

You can export that data with a simple SQL SELECT query, depending a bit on how your catalog tables are structured. Assuming a typical setup with products, brands, and EAN stored in separate columns, you can try something like this:
 

SELECT 
    p.product_id AS "Product ID",
    REGEXP_REPLACE(p.description, '<[^>]*>', '', 'g') AS "Product Description",
    p.ean AS "EAN Code",
    b.brand_name AS "Brand"
FROM 
    products p
LEFT JOIN 
    brands b ON p.brand_id = b.brand_id;

Explanation:

• REGEXP_REPLACE(p.description, '<[^>]*>', '', 'g') removes HTML tags from the product description.
• Works in PostgreSQL and newer MySQL versions (8.0+).
• For SQL Server, you might need a CLR function or use plain text if tags are minimal.
• The LEFT JOIN ensures all products are included even if some don’t have an associated brand.
• Replace table and column names (products, brands, etc.) with the ones from your actual schema.

If your EAN or brand is stored in related tables (for example, product_attributes), you’ll need to adjust the joins accordingly.

Let me know if you need the query for a specific SQL engine (e.g., MySQL, SQL Server, or PostgreSQL)! 
Wishing you the best!

- Why use a regex when a built-in function does the job?

- You forgot a join on product_lang.

- The table is called product, not products.

- The other table is called manufacturer, not brands.

[Mediacom87]

Il y a 18 heures, Eolia a dit :

- Why use a regex when a built-in function does the job?

- You forgot a join on product_lang.

- The table is called product, not products.

- The other table is called manufacturer, not brands.

Simplement parce qu'il a demandé à l'IA et que l'IA ne sait pas faire avec les bonnes pratiques.

[Eolia]

Oui je me doute, mais j'aurai aimé avoir sa réponse.

[LeviaMount]

17 hours ago, Mediacom87 said:

Simplement parce qu'il a demandé à l'IA et que l'IA ne sait pas faire avec les bonnes pratiques.

Dear Sir/Madam,

Not everything you see online is generated by AI. I rely on my own expertise and experience to deliver the best possible solutions. If you believe my answer resembles something produced by AI, then I can assure you it is as accurate and well-informed as the output of highly advanced AI systems.

With 7 years of experience in database management and programming, I don't need AI to solve problems—unlike some who rely on tools like Gemini or ChatGPT and consider themselves experts.

If you think there is room for improvement in my solution, I welcome constructive feedback. Let's work together to find the best possible answer. I am always open to criticism, as it helps us all reach the right solution.

[LeviaMount]

17 hours ago, Mediacom87 said:

Simplement parce qu'il a demandé à l'IA et que l'IA ne sait pas faire avec les bonnes pratiques.

Also Let me know what exactly issue you have with my solution

Edited November 11 by LeviaMount (see edit history)

[LeviaMount]

On 11/10/2025 at 1:36 AM, Eolia said:

- Why use a regex when a built-in function does the job?

- You forgot a join on product_lang.

- The table is called product, not products.

- The other table is called manufacturer, not brands.

Your questions are justifiable, I explained you in basic terms with more clarity,  I just explained the basic structure so you can do it based on your requirements or database design. As you haven't mention any further details i can't help you to create clean and clear query. But Still I tried one more version and here it is

 

SELECT 
    p.product_id AS "Product ID",
    TRIM(BOTH ' ' FROM p.description) AS "Product Description",
    p.ean AS "EAN Code",
    m.manufacturer_name AS "Brand"
FROM 
    product p
JOIN 
    product_lang pl ON p.product_id = pl.product_id
JOIN 
    manufacturer m ON p.manufacturer_id = m.manufacturer_id
WHERE 
    pl.language_code = 'en'; -- Adjust for the desired language

 

Edited November 11 by LeviaMount (see edit history)

[Mediacom87]

[Mediacom87]

Le 11/11/2025 à 9:00 AM, LeviaMount a dit :

Si vous pensez que ma solution peut être améliorée, je suis ouvert à toute remarque constructive. Travaillons ensemble pour trouver la meilleure réponse possible. Je suis toujours ouvert à la critique, car elle nous aide tous à trouver la bonne solution.

 

Le 06/11/2025 à 6:55 PM, Eolia a dit :

A adapter suivant votre préfixe et votre id_lang:

SELECT p.id_product, p.ean13, m.name, 
ExtractValue(pl.description, '//text()') as description
FROM `ps_product`p
INNER JOIN `ps_product_lang`pl
ON(pl.id_product = p.id_product AND pl.id_lang = 1)
INNER JOIN `ps_manufacturer`m
ON(m.id_manufacturer = p.id_manufacturer)

 

 

[Mediacom87]

Il y a 23 heures, LeviaMount a dit :

Vos questions sont justifiées, je vous ai expliqué les bases de manière plus claire. Je viens de vous expliquer la structure de base afin que vous puissiez l'adapter à vos besoins ou à la conception de votre base de données. Comme vous n'avez pas donné plus de détails, je ne peux pas vous aider à créer une requête claire et nette. Mais j'ai tout de même essayé une autre version, la voici :

Là encore, grosse suspicion d'IA puisque nous savons tous exactement ici la structure des bases de données de PrestaShop et surtout nous savons quels sont les noms des tables ainsi que des données à récupérer. Voilà pourquoi la réponse initiale d'Eolia est parfaite et ne demande aucune réponse supplémentaire.

[Eolia]

Le 11/11/2025 à 9:21 AM, LeviaMount a dit :

Your questions are justifiable, I explained you in basic terms with more clarity,  I just explained the basic structure so you can do it based on your requirements or database design. As you haven't mention any further details i can't help you to create clean and clear query. But Still I tried one more version and here it is

 

SELECT 
    p.product_id AS "Product ID",
    TRIM(BOTH ' ' FROM p.description) AS "Product Description",
    p.ean AS "EAN Code",
    m.manufacturer_name AS "Brand"
FROM 
    product p
JOIN 
    product_lang pl ON p.product_id = pl.product_id
JOIN 
    manufacturer m ON p.manufacturer_id = m.manufacturer_id
WHERE 
    pl.language_code = 'en'; -- Adjust for the desired language

 

Bon là c'est encore pire...

- Votre requête ne supprime pas le balisage html

- La table product_lang n'a jamais contenu de colonne language_code

- Dans la base de données, pour faire matcher les langues, tout est référencé par id_lang et jamais autre chose

Il est clair que vous ne maitrisez pas du tout la solution Prestashop. Vos propositions sont fausses et incorrectes et démontre votre ignorance totale de ce CMS.

 

https://github.com/PrestaShop/PrestaShop/blob/071523a97b952a1db196b50263795d76e4e039cf/install-dev/data/db_structure.sql