+88 01797-506292

Post Date: October 19, 2017
Posted By: admin

Create multilevel category tree with PHP and mysql

Creating a mysql table with parent and child columns

CREATE TABLE IF NOT EXISTS `category` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `parent` int(11) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Insert some dummy records. Parent columns with zero value denotes its the base/root category.

INSERT INTO `category` (`cid`, `name`, `parent`) VALUES
(1, 'Hardware', 0),
(2, 'Software', 0),
(3, 'Movies', 0),
(4, 'Clothes', 0),
(5, 'Printers', 1),
(6, 'Monitors', 1),
(7, 'Inkjet printers', 5),
(8, 'Laserjet Printers', 5),
(9, 'LCD monitors', 6),
(10, 'TFT monitors', 6),
(11, 'Antivirus', 2),
(12, 'Action movies', 3),
(13, 'Comedy Movies', 3),
(14, 'Romantic movie', 3),
(15, 'Thriller Movies', 3),
(16, 'Mens', 4),
(17, 'Womens', 4),
(18, 'Shirts', 16),
(19, 'T-shirts', 16),
(20, 'Shirts', 16),
(21, 'Jeans', 16),
(22, 'Accessories', 16),
(23, 'Tees', 17),
(24, 'Skirts', 17),
(25, 'Leggins', 17),
(26, 'Jeans', 17),
(27, 'Accessories', 17),
(28, 'Watches', 22),
(29, 'Tie', 22),
(30, 'cufflinks', 22),
(31, 'Earrings', 27),
(32, 'Bracelets', 27),
(33, 'Necklaces', 27),
(34, 'Pendants', 27);

Creating category Tree using recursion

function fetchCategoryTree($parent = 0, $spacing = '', $user_tree_array = '') {
 
  if (!is_array($user_tree_array))
    $user_tree_array = array();
 
  $sql = "SELECT `cid`, `name`, `parent` FROM `category` WHERE 1 AND `parent` = $parent ORDER BY cid ASC";
  $query = mysql_query($sql);
  if (mysql_num_rows($query) > 0) {
    while ($row = mysql_fetch_object($query)) {
      $user_tree_array[] = array("id" => $row->cid, "name" => $spacing . $row->name);
      $user_tree_array = fetchCategoryTree($row->cid, $spacing . '  ', $user_tree_array);
    }
  }
  return $user_tree_array;
}

Display the category tree in a dropdown list.

<?php 
$categoryList = fetchCategoryTree();
?>
<select>
<?php foreach($categoryList as $cl) { ?>
  <option value="<?php echo $cl["id"] ?>"><?php echo $cl["name"]; ?></option>
<?php } ?>
</select>

Displaying Category tree in list format

function fetchCategoryTreeList($parent = 0, $user_tree_array = '') {
 
    if (!is_array($user_tree_array))
    $user_tree_array = array();
 
  $sql = "SELECT `cid`, `name`, `parent` FROM `category` WHERE 1 AND `parent` = $parent ORDER BY cid ASC";
  $query = mysql_query($sql);
  if (mysql_num_rows($query) > 0) {
     $user_tree_array[] = "<ul>";
    while ($row = mysql_fetch_object($query)) {
	  $user_tree_array[] = "<li>". $row->name."</li>";
      $user_tree_array = fetchCategoryTreeList($row->cid, $user_tree_array);
    }
	$user_tree_array[] = "</ul>";
  }
  return $user_tree_array;
}

<ul>
<?php
  $res = fetchCategoryTreeList();
  foreach ($res as $r) {
    echo  $r;
  }
?>
</ul>

Leave a Reply

Your email address will not be published. Required fields are marked *