Disable products without a price in Prestashop 1.7.x

I’ve been doing some work on Prestashop, and having 3000+ products in the store I manage for a client, I noticed that things started to look a bit messy to keep track of. In this series I will provide my very own tools to automate or batch-process tasks.

The problem

Prices – especially in a wholesale distributor that should account for different discount ranges – are tricky. Even more when you just launched you’re store and you’re actively adding products directly to the live shop.

Some of the products do not have a price. Easy as that. And what I want to do is to have some kind of script that could help me disabling those products, so that clients can’t get them for free. At the same time, since we use a custom import program to set the prices, products get their prices set and still remain disabled.

 

The solution

My solution was to create a MySQL procedure to set whether a product is enabled or not based on price.

Let’s get started. Connect to the PHPMyAdmin of your shop or directly to the database using any client. We want to  be able to run some SQL.

In my case I’m using MySQL Workbench to connect to the remote database and execute from here:

This code needs to be executed one-off

DELIMITER //
CREATE PROCEDURE EnableDisableProducts()
BEGIN
	DECLARE prod_id INT;
	DECLARE product_price DECIMAL(20,6) DEFAULT 0;
    DECLARE product_enabled TINYINT UNSIGNED DEFAULT 0;

	DECLARE done TINYINT DEFAULT FALSE;
	DECLARE product_cursor
	CURSOR FOR
	SELECT DISTINCT id_product
	FROM ps_product;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	OPEN product_cursor;

rowLoop: 
LOOP
	FETCH NEXT FROM product_cursor INTO prod_id;
    
    IF done THEN
		LEAVE rowLoop; 
	ELSE
		SELECT price, active INTO product_price, product_enabled
        FROM ps_product
        WHERE id_product = prod_id;
        
        IF product_price = 0 AND product_enabled THEN
			UPDATE ps_product
            SET active = 0
            WHERE id_product = prod_id;
            
			UPDATE ps_product_shop
            SET active = 0
            WHERE id_product = prod_id;
		ELSEIF product_price > 0 AND NOT product_enabled THEN
			UPDATE ps_product
            SET active = 1
            WHERE id_product = prod_id;
            
			UPDATE ps_product_shop
            SET active = 1
            WHERE id_product = prod_id;
        END IF;
    END IF;
END LOOP;

CLOSE product_cursor;
END; //

A procedure is a stored function in the database that can be called very easily. Most importantly is that it’s transactional. Transactional means that if the script fails at any time during the execution, all the changes are rolled back.

Now, a few technicalities about the script. You should aim to always have the tables ps_product and ps_product_shop up to date, and the script also does that. If you have a single shop it’s not so important, but Prestashop can be unpredictable, so better not risking.

At this point you can execute the SQL and the procedure is stored, but the products are still not touched.

From now on, whenever you want to run the enable/disable product script you just need to call the procedure:

CALL EnableDisableProducts();

And the products will be changed!