MySQL Trigger Right Syntax for out of stock products

[moy2010]

Hi, Prestashop community. I'm trying to hide products that have been out of stock for over 3 months. So far the only solution I've found is to use a MySQL Trigger event.

 

What I have at the moment is (Trigger Syntax):

 

CREATE TRIGGER change_active_after_update AFTER UPDATE ON ps_stock_available

FOR EACH ROW

BEGIN

UPDATE ps_product_shop SET visibility="none" WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity=0);

UPDATE ps_product_shop SET visibility="both" WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity>0);

END

 

And to check the date I have this:

 

WHERE time < date_sub(now(),interval 3 month)

 

This "time" should be put next to "id_product" using the "AND" operator or should I use an ampersand?

 

If you think this wouldn't be a solution for what I'm looking for and I would better go for a different one such as a PHP script, please let me know.

 

Thanks in advance.

[cristic]

First of all - this should be with AND operator.

 

But I recommend using a php script, that you can put inside a cron job. 

If you have a trigger, every time ps_stock_available gets updated - the trigger gets executed - and in a lot of cases it can be unneeded. Useless mysql job - whereas the php approach is way better, that can be executed once per day.

[rvcat]

Hello moy2010
you're right to go through a trigger. it fast and then it is the basic engine of data that will work.

The trigger will function when the amount will be changed.

 

CREATE TRIGGER ps_stock_available_after_update AFTER UPDATE ON ps_stock_available

FOR EACH ROW

BEGIN

  if (old.quantity <> new.quantity) then

  begin

      update ps_product_shop SET visibility=if(new.quantity>0, "both", "none")

       where id_product = new.id_product;

  end if;

END

[cristic]

 

Hello moy2010

you're right to go through a trigger. it fast and then it is the basic engine of data that will work.

The trigger will function when the amount will be changed.

 

CREATE TRIGGER ps_stock_available_after_update AFTER UPDATE ON ps_stock_available

FOR EACH ROW

BEGIN

  if (old.quantity <> new.quantity) then

  begin

      update ps_product_shop SET visibility=if(new.quantity>0, "both", "none")

       where id_product = new.id_product;

  end if;

END

 

This is not what moy2010 wants. The goal is not to hide right away products that becomes out of stock. 

Only if the product is more than 3 months old with quantity = 0. So your solution will not work.