Fixing a catalogue-wide price problem in PrestaShop with SQL
Base prices at zero, the real price stored in the default combination, multistore on top. How I corrected the price structure at database level instead of product by product.
One query beats a thousand clicks.
When a data problem is structural, the fix should be structural too. This webshop showed the right prices on the front end, but the database stored them in a way that made every price change, discount and export error-prone.
The problem
In ps_product and ps_product_shop the base price of the products was 0. The actual selling price lived entirely in the price impact of the default combination. The storefront showed the right amount, so the problem was invisible to customers, but anything that reads the base price works with a zero. With multistore active, every product also exists in several shop contexts, which makes a partial fix easy and dangerous.
Diagnosis
- Mapped how PrestaShop builds a final price: base price plus the impact of the selected combination, per shop context.
- Identified the default combination of each product (
default_on = 1) and confirmed it carried the full price. - Listed which product, shop and combination tables needed the same change, so the fix could not drift between shop contexts.
The fix
- Used the price of the default combination as the new base price of each product.
- Recalculated the price impact of every other combination as the difference to that base, so the price a customer pays stays the same.
- Applied the change at both product and shop level, and checked the multistore logic separately.
- Cleared the PrestaShop cache after the database changes.
Before and after
What this means for you
Webshop platforms let you build a catalogue that looks right and is wrong underneath. If your feeds, discounts or reports disagree with your storefront, the database is where I start. See e-commerce SEO for what else I check on webshops.
Something in your catalogue does not add up?
Prices, stock, categories or URLs that look fine on the front end and wrong everywhere else usually have one structural cause. I find it and fix it at the source.
Developer-ready deliverables.
NL, BE, DE and UK markets.