How to find PrestaShop products with missing images, descriptions or EAN (SQL queries)

A catalog of a few hundred products is easy to keep tidy by hand. With a few thousand, plus combinations, plus supplier imports, gaps creep in that nobody sees in the back office: a product with one photo, a description of two lines, a size without a barcode, a brand left empty. This guide gives you ready-to-run SQL queries that list each kind of gap, so you know exactly what to fix and in what order.

Before you start

• All queries below only read data (SELECT). Run them in phpMyAdmin, Adminer or any MySQL client.
• Replace the ps_ prefix with your table prefix.
• id_lang = 1 and id_shop = 1 mean your default language and shop – change them if yours differ (check ps_lang and ps_shop).
• Most queries filter p.active = 1. Remove that line to also check products that are not published yet – a good habit before you switch them on.

1. Products without a brand

Google needs a brand for almost every product, and shoppers filter by it. In PrestaShop the brand is called manufacturer.

SELECT p.id_product, p.reference, pl.name
FROM ps_product p
JOIN ps_product_lang pl
  ON pl.id_product = p.id_product AND pl.id_lang = 1 AND pl.id_shop = 1
WHERE p.active = 1
  AND p.id_manufacturer = 0;

2. Products with fewer than 3 images

One photo is rarely enough to sell. Change 3 to whatever minimum you want.

SELECT p.id_product, pl.name, COUNT(i.id_image) AS images
FROM ps_product p
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_image i ON i.id_product = p.id_product
WHERE p.active = 1
GROUP BY p.id_product, pl.name
HAVING images < 3
ORDER BY images, p.id_product;

3. Short or empty descriptions

The query strips HTML tags and counts the remaining characters. It needs MySQL 8 or MariaDB 10.0.5+ (REGEXP_REPLACE).

SELECT p.id_product, pl.name,
       CHAR_LENGTH(TRIM(REGEXP_REPLACE(COALESCE(pl.description, ''), '<[^>]*>', ''))) AS chars
FROM ps_product p
JOIN ps_product_lang pl
  ON pl.id_product = p.id_product AND pl.id_lang = 1 AND pl.id_shop = 1
WHERE p.active = 1
HAVING chars < 300
ORDER BY chars;

Run it once per language (id_lang). Untranslated descriptions are a common hidden gap in multilingual shops.

4. Products and combinations without EAN

-- simple products
SELECT p.id_product, pl.name
FROM ps_product p
JOIN ps_product_lang pl
  ON pl.id_product = p.id_product AND pl.id_lang = 1 AND pl.id_shop = 1
WHERE p.active = 1
  AND NOT EXISTS (SELECT 1 FROM ps_product_attribute pa WHERE pa.id_product = p.id_product)
  AND COALESCE(p.ean13, '') = '';

-- combinations
SELECT p.id_product, pl.name, pa.id_product_attribute, pa.reference
FROM ps_product_attribute pa
JOIN ps_product p ON p.id_product = pa.id_product
JOIN ps_product_lang pl
  ON pl.id_product = p.id_product AND pl.id_lang = 1 AND pl.id_shop = 1
WHERE p.active = 1
  AND COALESCE(pa.ean13, '') = '';

More on GTINs and Google Merchant Center: How to find every PrestaShop product without a GTIN.

5. Combinations without an assigned image

When a shopper picks “red”, the photo should switch to red. That only works if images are assigned to combinations.

SELECT p.id_product, pl.name, pa.id_product_attribute, pa.reference
FROM ps_product_attribute pa
JOIN ps_product p ON p.id_product = pa.id_product
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_product_attribute_image pai
  ON pai.id_product_attribute = pa.id_product_attribute
WHERE p.active = 1
  AND pai.id_image IS NULL;

Note: if a shop only distinguishes sizes, missing combination images may be fine. They matter mainly for colours and materials.

6. Out of stock

SELECT p.id_product, pl.name, sa.id_product_attribute, sa.quantity
FROM ps_stock_available sa
JOIN ps_product p ON p.id_product = sa.id_product
JOIN ps_product_lang pl
  ON pl.id_product = p.id_product AND pl.id_lang = 1 AND pl.id_shop = 1
WHERE p.active = 1
  AND sa.quantity <= 0
  AND (
    sa.id_product_attribute > 0
    OR NOT EXISTS (SELECT 1 FROM ps_product_attribute pa WHERE pa.id_product = p.id_product)
  );

7. Zero weight, zero price, missing meta title

-- physical products without weight (shipping costs will be wrong)
SELECT id_product, reference FROM ps_product
WHERE active = 1 AND is_virtual = 0 AND weight = 0;

-- price 0 or less
SELECT ps.id_product, pl.name, ps.price
FROM ps_product_shop ps
JOIN ps_product_lang pl
  ON pl.id_product = ps.id_product AND pl.id_lang = 1 AND pl.id_shop = ps.id_shop
WHERE ps.id_shop = 1 AND ps.active = 1 AND ps.price <= 0;

-- empty meta title
SELECT p.id_product, pl.name
FROM ps_product p
JOIN ps_product_lang pl
  ON pl.id_product = p.id_product AND pl.id_lang = 1 AND pl.id_shop = 1
WHERE p.active = 1 AND COALESCE(pl.meta_title, '') = '';

What to fix first

Ten lists with hundreds of rows each are not a plan. A simple order that works for most shops:

1. Products you advertise. Price, stock, images and GTIN on anything in Google Shopping or Meta – errors there cost money every day.
2. Best sellers. Sort by sales and fix descriptions and images on the top 50 first.
3. Everything new. Add a check before publishing, so the gaps stop growing.
4. The long tail, in batches.

Limits of the SQL approach

The queries work, but they give you ten separate lists, one problem per row. You still have to merge them in a spreadsheet, decide which product is worst, and run everything again next month. They also treat every catalog the same way: a shop selling furniture needs different rules from one selling T-shirts.