moy2010 Posted September 14, 2015 Share Posted September 14, 2015 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. Link to comment Share on other sites More sharing options...
cristic Posted September 15, 2015 Share Posted September 15, 2015 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. 1 Link to comment Share on other sites More sharing options...
rvcat Posted September 16, 2015 Share Posted September 16, 2015 Hello moy2010you'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 Link to comment Share on other sites More sharing options...
cristic Posted September 17, 2015 Share Posted September 17, 2015 Hello moy2010you'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. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now