How to automatically disable out of stock products in PrestaShop

databaseIntroduction

Today I want to show you how to automatically disable out of stock products in your store. Unfortunately, by default you can't hide products which are out of stock. You can only disallow to order them. So the product will appear on list. In this tutorial you will read how to disable it. As you know, PrestaShop is based on MySQL database, which mean that most important informations (products + categories + etc.) are stored in tables. To achieve what we want - we will use MySQL triggers.

 

Module  

If you're not interested in triggers, modifications and if you don't have technical skills - we just want to inform that some time ago we released commercial module to disable out of stock products in PrestaShop automatically (this module has a feature that allows to disable out of stock products)

 

 

 

 

What is MySQL trigger?

Trigger is a rule that you put on a table which basically says, whenever you DELETE, UPDATE or INSERT IGNORE something in this table, also do something else.

 

 

Facts about PrestaShop:

  1. Product informations are stored in tables with ps_product  prefix,
  2. Stock informations are stored in table ps_stock_available (prestashop 1.5.x)
  3. To disable product in the shop you have to change the active field in ps_product_shop table to 0
  4. To enable product in the shop you have to change the active field in ps_product_shop table to 1

 

Concept

We want to create MySQL trigger which will change product visibility in store to off (active=0), when the product quantity equals 0. When product stock value will be bigger than 0 - then product visibility will be turned to on (active=1). As I said the trigger is a rule that we can put on table whenever we (or PHP script - like PrestaShop) DELETE, UPDATE OR INSERT IGNORE something in this table. When you change product quantity in back office or when someone buy something from you - PHP script automatically UPDATE ps_stock_available table and change the quantity for product that you sold.

 

How to create trigger?

You can create trigger in many ways. You can use SQL query, you can use some database managers like PHPMyAdmin. I will show you both. Okay, let's do the trick :)

 

Each MySQL trigger requires:

  1. A unique name. The best thing is to use a name which describes the table and action that you want to achieve. Anyway, the name depends on you. You can define name exactly as you want - but remember that it must be unique.
  2. The table for which trigger will work, single trigger can only monitor a single table.
  3. When the trigger occurs - you can easily setup when the trigger will occur. You can create trigger BEFORE or AFTER DELTE, UPDATE or INSERT IGNORE something to table
  4. The Trigger body - a set of SQL queries tu run.

 

 

Our trigger SQL query:

CREATE TRIGGER change_active_after_update AFTER UPDATE ON ps_stock_available
FOR EACH ROW
BEGIN
UPDATE ps_product_shop SET active=0 WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity=0);
UPDATE ps_product_shop SET active=1 WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity>0);
END

All you have to do with this query - is to run it in prestashop, or somewhere else - for example in database managers like PHPMyAdmin.

 

 Let me say something more about query above.

CREATE TRIGGER change_active_after_update AFTER UPDATE ON ps_stock_available
FOR EACH ROW

This mean that we creating trigger named change_active_after_update AFTER any UPDATE ON the ps_stock_available table. FOR EACH ROW means that triger will monitor all available entries.

 

BEGIN

This means that we start our trigger body here. All SQL queries between the BEGIN & END will run one by one.

 

UPDATE ps_product_shop SET active=0 WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity=0);

This means that we will SELECT all products from your store, which have quantity value 0. And then query will change the active param to 0 - wich mean that each product with quantity = 0 will be disabled.

 

UPDATE ps_product_shop SET active=1 WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity>0);

This query is similar to previous, but it will enable all products from your store which are in stock (with quantity value > 0)

 

END

END means that here is an end of the trigger body.

 

 

Creating trigger in PHPMyAdmin

I will use here PHPMyAdmin in version 3.5.2.2 (check documentation of PHPMyAdmin). But you can use newest version and older one too, the process of creating trigger is the same in all versions. For the first - log in to your PHPMyAdmin and select database with your prestashop store:

 

PHPMyAdmin pretashop database

 

Search for ps_stock_available table. We will create trigger on this table, so we have to open it. When you find it on the list - click on table name. you will see something like:

 

prestashop ps_stock_available

 

Click on triggers button in top horizontal menu. You will see all defined trigers for this table (of course if you created). If not - the list will be empty:

 

prestashop ps_stock_available create triggers

 

Click on "Add trigger" button. You will see form, where you have to specify trigger parameteres. Fill it exactly as I show below. To the  Definition field - paste the trigger body. Here it is (copy BEGIND + BODY + END), all of the code below:

 

BEGIN
UPDATE ps_product_shop SET active=0 WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity=0);
UPDATE ps_product_shop SET active=1 WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity>0);
END

 

Here is the trigger parameters form:

 

prestashop trigger window ps_stock_available mysql

 

When you are convinced that you filled form exactly as I - click on "Go" button. You will se confirmation page:

 

Prestashop trigger ps_stock_available product disable

 

 

Your trigger is ready now! 

 

 

author milos myszczuk
Article by Milosz Myszczuk PrestaShop expert, official PrestaShop community moderator. PHP developer, specialist in relative and spatial databases management, GIS Analyst, CEO & founder of VEKIA interactive agency. Read more about VEKIA company
If you like my articles and want much more valuable tips, feel free to send me donation
1.4 version 1.4.11 1.6 404 addon admin advertise ahref ajax alpha animation api app application authentication back office backup badge banner basics block bootstrap button cache carrier cart catalog category certificate changelog chat class clear client clip cms code colors columns comments configuration contact container content controller cookie counter country coupon css csv currency customer dashboard database debug default delete delivery desktop developer device disable discount displayNav displayTop download dynamic editor effect empty encrypt engine error exchange exclude export facebook faceshop fade fancoupon fancybox fanpage fatal feature feed field file fix fixed font footer free friendly url front ftp full gallery generate gift global godaddy google google+ gray grid groupon header help hide highlight homefeatured homepage hook hosting hover howto htaccess html html5 ID image import include input instagram installation integration iPhone issue javascript jquery kgb knowhow languages law left likebox link list livingsocial loading log login logo loyality mail mailing maintenance manufacturer marketing marquee mcrypt menu meta mobile modification module movie moving multilanguage multiupload must have mysql news newsletter notification number open graph order override page password performance PHP phpmyadmin picture pinterest plugin popup post prestashop prestashop 1.0 prestashop 1.1 prestashop 1.2 prestashop 1.3 prestashop 1.4 prestashop 1.5 price rules problem product profile promotion proslider purifier quantity query quick tip random rates register reinsurance release reporting reset responsive restore results ribbon rich text right sales search security seo service shadow share shipping shop shopmania slider smarty social networks SQL SSL statistics stock store style subcategory superuser support switcher tab tablet tag tax template text theme tinyMCE tips and tricks tpl tracking translations tree trends trigger tumblr tutorial twitter update upgrade upload variables video visits voucher vulnerability web2print wide widget width window wishlist wysiwyg youtube zip zopim