Tábla tartalmaz egy 0-val paddingolt ordered oszlopot, mely vesszővel elválasztva mutatja az utat a gyerekig.
Beszúrásnál átveszi a szülő ordered mezőjét és hozzá bigyeszti saját id-jét.
Előnye eszerint lehet rendezni. Illetve ez az sql megadja a mélység szintjét.
Valamint alul egy lekérdezés, mely legyűjti az összes gyermek terméket.
LENGTH(c.ordered) - LENGTH(REPLACE(c.ordered, ',', '')) AS depth
CREATE TABLE IF NOT EXISTS `category` (
`cat_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`cat_name` varchar(64) COLLATE utf8_hungarian_ci NOT NULL,
`parent_id` int(10) unsigned DEFAULT NULL,
`ordered` varchar(255) COLLATE utf8_hungarian_ci DEFAULT NULL,
`cat_color` int(10) unsigned NOT NULL,
PRIMARY KEY (`cat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci AUTO_INCREMENT=43 ;
Az új kategória beszúrása:
if($_POST['cat'] == "") {
//main category
$sql = "INSERT INTO category (cat_name, parent_id, cat_color)
VALUES ('".$name."', 0,'".$_POST['color']."')";
mysql_query($sql) or die ("Nem sikerült új kategória hozzáadása!");
print_r($sql);
$last_idd = mysql_insert_id();
$padding_lastid = str_pad("$last_idd", 4, "0", str_pad_left);
$sql3 = "UPDATE category
SET ordered = '".$padding_lastid."'
WHERE cat_id = ".$last_idd;
$result3 = mysql_query($sql3) or die ("Update problem!".$last_idd);
print_r($sql3);
}
else {
//sub category
$sql2 = "SELECT *
FROM category
WHERE cat_id = ".$_POST['cat'];
$result2 = mysql_query($sql2);
$row2 = mysql_fetch_assoc($result2);
$ordered = $row2['ordered'];
$sql = "INSERT INTO category (cat_name, parent_id)
VALUES ('".$name."', '".$_POST['cat']."')";
mysql_query($sql) or die ("Nem sikerült új kategória hozzáadása!");
$last_idd = mysql_insert_id();
$padding_lastid = str_pad("$last_idd", 4, "0", str_pad_left);
$sql3 = "UPDATE category
SET ordered='".$ordered.",".$padding_lastid."'
WHERE cat_id=".$last_idd;
$result3 = mysql_query($sql3) or die ("Update problem!".$ordered);
}
SELECT coalesce(c2.cat_name,'-') AS parent_name,
c.cat_name AS child_name,
c.cat_id, c.ordered, color.col_class,
LENGTH(c.ordered) - LENGTH(REPLACE(c.ordered, ',', '')) AS occurence
FROM category c
LEFT JOIN category c2
on c.parent_id=c2.cat_id
LEFT JOIN color
ON c.cat_color = color.col_id
ORDER BY ordered ASC;
Az alábbi bekezdés pedig legyűjti a szülő gyerek kapcsolatokat 2 oszlopos formátumban.
//sub category
$sql2 = "SELECT getChildLst(".$cat_id.") AS cat_list";
$result2 = mysql_query($sql2);
$row2 = mysql_fetch_assoc($result2);
$cat_idd .= $row2['cat_list'];
//products
$sql = "SELECT *
FROM product
INNER JOIN prod_cat
ON product.pro_id = prod_cat.pro_id
INNER JOIN price
ON product.pro_id = price.pro_id
WHERE cat_id IN (".$cat_idd.")";
És a getChildLst függvény mint tárolt függvény.
DROP FUNCTION `getChildLst`//
CREATE FUNCTION `getChildLst`(rootId INT) RETURNS varchar(1000) CHARSET latin1
DETERMINISTIC
BEGIN
DECLARE Stemp VARCHAR (1000);
DECLARE sTempChd VARCHAR (1000);
DECLARE checkChd VARCHAR (1000);
SET sTemp = '$';
SET sTempChd = cast(rootId as CHAR);
SET checkChd = cast(rootId as CHAR);
WHILE sTempChd is not null DO
IF( sTempChd != checkChd) THEN
SET sTemp = concat(sTemp, ',', sTempChd);
ELSE
SET sTemp = sTempChd;
END IF;
SELECT group_concat(cat_id) INTO sTempChd FROM category where FIND_IN_SET(parent_id, sTempChd)> 0;
END while;
RETURN sTemp;
END