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>