Category Sub Category tree with PHP and mysql

Category Sub Category recursive function, To display category sub category on page.


Table for Category:

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.

Example
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);

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>

Create Slug

If you want to make a string seo friendly slug in joomla then copy and paste the following function


public function createSlug($name) {
$jinput = JFactory::getApplication()->input;
$template = $jinput->get('template', null, null);

// Transliterate
$lang = new JLanguage($template->get('language', 'general', '', null, 0, false));
$str = $lang->transliterate($name);

// Trim white spaces at beginning and end of alias and make lowercase
$str = trim(JString::strtolower($str));

// Remove any duplicate whitespace, and ensure all characters are alphanumeric
$str = preg_replace('/(\s|[^A-Za-z0-9\-])+/', '-', $str);

// Trim dashes at beginning and end of alias
$str = trim($str, '-');

// If we are left with an empty string, make a date with random number
if (trim(str_replace('-', '', $str)) == '') {
$jdate = JFactory::getDate();
$str = $jdate->format("Y-m-d-h-i-s").mt_rand();
}
return $str;
}