[MOD]- Hide Empty Category Using Query

Code: Select all

BEGIN

DECLARE last_id INT;
DECLARE i INT ;


SET i=1;
SELECT MAX(category_id) INTO last_id  FROM category;
/*SELECT last_id;*/-- Muestra cantidad total de categorias
WHILE i < last_id DO -- Inicio de "FOR"

IF NOT EXISTS (SELECT product_id  FROM product_to_category WHERE product_to_category.category_id=i  LIMIT 1) THEN -- if 
IF NOT EXISTS (SELECT parent_id FROM category WHERE category_id=i AND parent_id='0')THEN -- si no es category principal
IF NOT EXISTS (SELECT category_id FROM category WHERE parent_id=i) THEN
UPDATE category SET status='0' WHERE category_id=i;

END IF;
END IF;
ELSE UPDATE category SET status='1' WHERE category_id=i; -- si dio todo posivo por las dudas cambia status a 1
END IF;

SET i =i+1;

END WHILE;
END

Code: Select all

BEGIN

DECLARE last_id INT;
DECLARE i INT ;
DECLARE q INT;

SET i=1;
SELECT MAX(category_id) INTO last_id FROM category;

WHILE i <= last_id DO 

SET @q = (SELECT product.quantity FROM product_to_category INNER JOIN product ON product_to_category.product_id = product.product_id WHERE product_to_category.category_id=i ORDER BY product.quantity DESC LIMIT 1);

IF (SELECT @q > 0) THEN 

UPDATE category SET status='1' WHERE category_id=i;

ELSE UPDATE category SET status='0' WHERE category_id=i; 

END IF;

SET i =i+1;

END WHILE;


END