Hi, we have a script that searches for specific products from the entire DB (MariaDB) and transports them to a specific folder.
We execute this script via the PhpMyAdmin routine function.
If instead of searching the entire DB we want to "limit" the search to just two folders (3871 and 298) what should we change within the script?
Thank you for any help
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE prodId INT;
DECLARE catId INT;
DECLARE cur_prodCats CURSOR FOR
SELECT
id_product AS prodId,
id_category_default AS catId
FROM
ps_product
WHERE id_product IN
(SELECT
temp.id_product
FROM
(SELECT DISTINCT
ps_product_lang.id_product
FROM
ps_product_lang
WHERE (
LCASE(ps_product_lang.name) LIKE '%USATO%'
)
OR (
LCASE(ps_product_lang.name) LIKE '%usati%'
)
OR (
LCASE(ps_product_lang.name) LIKE '%usato%'
)
OR (
LCASE(ps_product_lang.name) LIKE '%refurbished%'
)
OR (
LCASE(ps_product_lang.name) LIKE '%outlet%'
)
OR (
LCASE(ps_product_lang.name) LIKE '%ricondizionato%'
)
OR (
LCASE(ps_product_lang.name) LIKE '%ricondizionati%'
)) AS temp
LEFT JOIN ps_product
ON temp.id_product = ps_product.id_product
WHERE ps_product.id_category_default NOT IN (129));
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur_prodCats;
read_loop1 :
LOOP
FETCH cur_prodCats INTO prodId,
catId;
IF done
THEN LEAVE read_loop1;
END IF;
DELETE
FROM
ps_category_product
WHERE id_product = prodId;
IF NOT EXISTS
(SELECT
*
FROM
ps_category_product
WHERE id_category = 1
AND id_product = prodId)
THEN
INSERT INTO ps_category_product
VALUES
(1, prodId, prodId);
END IF;
IF NOT EXISTS
(SELECT
*
FROM
ps_category_product
WHERE id_category = 2
AND id_product = prodId)
THEN
INSERT INTO ps_category_product
VALUES
(2, prodId, prodId);
END IF;
IF NOT EXISTS
(SELECT
*
FROM
ps_category_product
WHERE id_category = catId
AND id_product = prodId)
THEN
INSERT INTO ps_category_product
VALUES
(129, prodId, prodId);
ELSE IF NOT EXISTS
(SELECT
*
FROM
ps_category_product
WHERE id_category = 129
AND id_product = prodId)
THEN
UPDATE
ps_category_product
SET
id_category = 129
WHERE id_product = prodId
AND id_category = catId;
END IF;
END IF;
END LOOP;
CLOSE cur_prodCats;
UPDATE
ps_product
SET
id_category_default = 129
WHERE id_product IN
(SELECT
temp.id_product
FROM
(SELECT DISTINCT
ps_product_lang.id_product
FROM
ps_product_lang
WHERE (
LCASE(ps_product_lang.name) LIKE '%USATO%'
)
OR (
LCASE(ps_product_lang.name) LIKE '%usati%'
)
OR (
LCASE(ps_product_lang.name) LIKE '%usato%'
)
OR (
LCASE(ps_product_lang.name) LIKE '%refurbished%'
)
OR (
LCASE(ps_product_lang.name) LIKE '%outlet%'
)
OR (
LCASE(ps_product_lang.name) LIKE '%ricondizionato%'
)
OR (
LCASE(ps_product_lang.name) LIKE '%ricondizionati%'
)) AS temp
LEFT JOIN ps_product
ON temp.id_product = ps_product.id_product
WHERE ps_product.id_category_default NOT IN (129));
UPDATE
ps_product_shop
SET
id_category_default = 129
WHERE id_product IN
(SELECT
temp.id_product
FROM
(SELECT DISTINCT
ps_product_lang.id_product
FROM
ps_product_lang
WHERE (
LCASE(ps_product_lang.name) LIKE '%USATO%'
)
OR (
LCASE(ps_product_lang.name) LIKE '%usati%'
)
OR (
LCASE(ps_product_lang.name) LIKE '%usato%'
)
OR (
LCASE(ps_product_lang.name) LIKE '%refurbished%'
)
OR (
LCASE(ps_product_lang.name) LIKE '%outlet%'
)
OR (
LCASE(ps_product_lang.name) LIKE '%ricondizionato%'
)
OR (
LCASE(ps_product_lang.name) LIKE '%ricondizionati%'
)) AS temp
LEFT JOIN ps_product_shop
ON temp.id_product = ps_product_shop.id_product
WHERE ps_product_shop.id_category_default NOT IN (129));
END