Read 167 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>