Read 760 times | Created 2013-09-11 09:39:25 | Updated 2013-09-11 10:23:49 | | |
<?php /* -- DATABASE : MYSQL -- CODE NAME : NESTED SET -- CREATE BY : Cahya DSN -- CREATE ON : 2013-09-11 -- -- tbl_categories to store categories data DROP TABLE IF EXISTS `tbl_categories`; CREATE TABLE IF NOT EXISTS `tbl_categories`( id_cat SMALLINT UNSIGNED AUTO_INCREMENT, name VARCHAR(20) NOT NULL, lft SMALLINT UNSIGNED NOT NULL, rgt SMALLINT UNSIGNED NOT NULL, PRIMARY KEY(id_cat) )ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO tbl_categories VALUES (1, 'Hewan', 1,6), (2, 'Karnivora', 2,5), (3, 'Berkaki 4',3, 4), (4, 'Mobil', 7,10), (5, 'Honda', 8,9), (6, 'Buah', 11,12); -- tbl_items to store items data DROP TABLE IF EXISTS `tbl_items`; CREATE TABLE IF NOT EXISTS `tbl_items`( id_item INT UNSIGNED AUTO_INCREMENT, name VARCHAR(40), id_cat SMALLINT UNSIGNED NOT NULL, PRIMARY KEY(id_item) )ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO tbl_items VALUES (1, 'Harimau',3), (2, 'Singa',3), (3, 'Ular',2), (4, 'Civic',5), (5, 'Pisang',6); */ $dbhost='localhost'; $dbuser='root'; $dbpass=''; $dbname='test'; $db=new mysqli($dbhost,$dbuser,$dbpass,$dbname); ?> <!DOCTYPE html> <html> <head> <title>Nested Set Data</title> </head> <body> <pre> <?php /*** Show Categories Tree ***/ $sql="SELECT CONCAT(REPEAT('---',COUNT(parent.name) - 1), node.name) AS name FROM tbl_categories AS node, tbl_categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt GROUP BY node.name ORDER BY node.lft"; echo "n#CATEGORIES TREEn"; if($result=$db->query($sql)){ while($record=$result->fetch_object()) { echo $record->name."n"; } $result->close(); } /*** Show name and it's level depth with 'Hewan' on top level ***/ $sql="SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth FROM tbl_categories AS node, tbl_categories AS parent, tbl_categories AS sub_parent, ( SELECT node.name, (COUNT(parent.name) - 1) AS depth FROM tbl_categories AS node, tbl_categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.name = 'Hewan' GROUP BY node.name ORDER BY node.lft )AS sub_tree WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt AND sub_parent.name = sub_tree.name GROUP BY node.name ORDER BY node.lft"; echo "nn#CATEGORIES NAME AND IT'S LEVEL DEPTHn"; if($result=$db->query($sql)){ while($record=$result->fetch_object()) { echo str_pad($record->name,10)."|".$record->depth."n"; } $result->close(); } /*** Show number of item within each categories ***/ $sql="SELECT parent.name, COUNT(tbl_items.name) AS num FROM tbl_categories AS node , tbl_categories AS parent, tbl_items WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.id_cat = tbl_items.id_cat GROUP BY parent.name ORDER BY node.lft"; echo "nn#NUMBER OF ITEMS FOR EACH CATEGORIESn"; if($result=$db->query($sql)){ while($record=$result->fetch_object()) { echo str_pad($record->name,10)."|".$record->num."n"; } $result->close(); } ?> </pre> </body> </html>