Hallo,
mal als Lösung:
function set_category_recursive($categories_id, $status = "0") {
// set status of categories with sub categories
xtc_db_query("update ".TABLE_CATEGORIES." set categories_status = '".$status."' where categories_id in (
SELECT
categories_id
FROM
(
SELECT
@rownum := @rownum+1 AS rownum,
IF(@lastid <> mylist.categories_id, @id := mylist.categories_id, @id) AS pathid,
@lastid := mylist.categories_id AS categories_id,
@id := (SELECT parent_ID FROM ".TABLE_CATEGORIES." WHERE categories_id = @id) AS parent_ID
FROM
(SELECT @id := 0, @lastid := 0, @rownum := 0) AS vars,
(SELECT categories_id FROM ".TABLE_CATEGORIES.") AS myloop,
(SELECT categories_id FROM ".TABLE_CATEGORIES.") AS mylist
) AS t
WHERE
pathid = '".$categories_id."')");
// set status of products
xtc_db_query(" UPDATE ".TABLE_PRODUCTS." set products_status = '".$status."' where products_id in (
SELECT products_id FROM products_to_categories WHERE categories_id IN (
SELECT
categories_id
FROM
(
SELECT
@rownum := @rownum+1 AS rownum,
IF(@lastid <> mylist.categories_id, @id := mylist.categories_id, @id) AS pathid,
@lastid := mylist.categories_id AS categories_id,
@id := (SELECT parent_ID FROM ".TABLE_CATEGORIES." WHERE categories_id = @id) AS parent_ID
FROM
(SELECT @id := 0, @lastid := 0, @rownum := 0) AS vars,
(SELECT categories_id FROM ".TABLE_CATEGORIES.") AS myloop,
(SELECT categories_id FROM ".TABLE_CATEGORIES.") AS mylist
) AS t
WHERE
pathid = '".$categories_id."')
)");
}
?>
Ich gehe ja mal davon aus, dass dafür die Funktion auch gedacht war. Eine Abfrage, ob nun rekursiv oder nicht, sollte dann nicht von der Funktion abhängen.
Vielleicht wäre das Vorgehen in dem nächsten Update richtig!
Generell glaube ich, das die SQL Funktionalitäten mit in die Funktionen vom Shop mit übernommen werden können/sollten, oder?