$check_func_exists_qry = xtc_db_query("SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE' AND ROUTINE_SCHEMA='".DB_DATABASE."' AND ROUTINE_NAME = 'getcategorieslevel'");
if (xtc_db_num_rows($check_func_exists_qry, true) == 0) {
xtc_db_multi_query("DROP PROCEDURE IF EXISTS getcategorieslevel;
CREATE PROCEDURE getcategorieslevel(IN cat_id INT, OUT level INT)
BEGIN
DECLARE catname VARCHAR(20);
DECLARE temppath INT;
DECLARE tempparent INT;
SET max_sp_recursion_depth = 255;
SELECT categories_id, parent_id FROM categories WHERE categories_id=cat_id INTO catname, tempparent;
IF tempparent IS NULL
THEN
SET level = 0;
ELSE
CALL getcategorieslevel(tempparent, temppath);
SET level = temppath + 1;
END IF;
END");
}
$check_func_exists_qry = xtc_db_query("SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='FUNCTION' AND ROUTINE_SCHEMA='".DB_DATABASE."' AND ROUTINE_NAME = 'getcategorieslevel'");
if (xtc_db_num_rows($check_func_exists_qry, true) == 0) {
// create sql-procedure
xtc_db_multi_query("DROP FUNCTION IF EXISTS getcategorieslevel;
CREATE FUNCTION getcategorieslevel(cat_id INT) RETURNS INT DETERMINISTIC
BEGIN
DECLARE res INT;
CALL getcategorieslevel(cat_id, res);
RETURN res;
END");
}
$check_func_exists_qry = xtc_db_query("SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE' AND ROUTINE_SCHEMA='".DB_DATABASE."' AND ROUTINE_NAME = 'getcategoriespath'");
if (xtc_db_num_rows($check_func_exists_qry, true) == 0) {
// create sql-procedure
xtc_db_multi_query("DROP PROCEDURE IF EXISTS getcategoriespath;
CREATE PROCEDURE getcategoriespath(IN cat_id INT, OUT path TEXT)
BEGIN
DECLARE catname VARCHAR(20);
DECLARE temppath TEXT;
DECLARE tempparent INT;
SET max_sp_recursion_depth = 255;
SELECT categories_id, parent_id FROM categories WHERE categories_id=cat_id INTO catname, tempparent;
IF (tempparent IS NULL OR tempparent = 0)
THEN
SET path = CAST(cat_id AS CHAR);
ELSE
CALL getcategoriespath(tempparent, temppath);
SET path = CONCAT(CAST(temppath AS CHAR), '_', CAST(cat_id AS CHAR));
END IF;
END");
}
$check_func_exists_qry = xtc_db_query("SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='FUNCTION' AND ROUTINE_SCHEMA='".DB_DATABASE."' AND ROUTINE_NAME = 'getcategoriespath'");
if (xtc_db_num_rows($check_func_exists_qry, true) == 0) {
// create sql-procedure
xtc_db_multi_query("DROP FUNCTION IF EXISTS getcategoriespath;
CREATE FUNCTION getcategoriespath(cat_id INT) RETURNS TEXT DETERMINISTIC
BEGIN
DECLARE res TEXT;
CALL getcategoriespath(cat_id, res);
RETURN res;
END");
}
$categories_query = xtDBquery("SELECT c.categories_id,
cd.categories_name,
c.parent_id, getcategorieslevel(c.categories_id) AS level, getcategoriespath(c.categories_id) AS path FROM ".TABLE_CATEGORIES." c
JOIN ".TABLE_CATEGORIES_DESCRIPTION." cd
ON c.categories_id = cd.categories_id
AND cd.language_id='".(int)$_SESSION['languages_id']."'
AND trim(cd.categories_name) != ''
WHERE c.categories_status = '1'
".CATEGORIES_CONDITIONS_C."
ORDER BY c.parent_id, level, c.sort_order, cd.categories_name");