Get category and subcategory tree in PHP MySql

 How to Get category and subcategory tree in PHP MySql

1. How to get recursive category subcategory with  OOP PHP

*Display the categories in a dropdown menu or select box

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;

}

To display the category use this snippet below.

<?php

$categoryList = fetchCategoryTree();

?>

<select>

<?php foreach($categoryList as $cl) { ?>

  <option value="<?php echo $cl["id"] ?>"><?php echo $cl["name"]; ?></option>

<?php } ?>

</select>

*Display This Categories in a list

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;

}

To display the category use this snippet below.

Display category in list format

PHP

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

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

2. How to get recursive category subcategory with  CORE PHP

Create a table with catgory_id,name,parent,sort_order field

<?php
$g_link = mysql_connect( 'localhost', 'root', '');
        mysql_select_db('your_database_name');
      
      
$result = mysql_query("SELECT category_id,name,parent,sort_order FROM category ORDER BY parent,sort_order,name");


$category = array('categories' => array(),'parent_cats' => array());

        while ($row = mysql_fetch_assoc($result)) {
           
            $category['categories'][$row['category_id']] = $row;
           
            $category['parent_cats'][$row['parent']][] = $row['category_id'];
        }
        ?>


/*This function used for show add category (in select box for select parent category) in menu*/


<select name="cat">
<option value="0"></option>
<?php

function buildCategory($parent, $category,$name) {
            $html = "";
            if (isset($category['parent_cats'][$parent])) {
               
                foreach ($category['parent_cats'][$parent] as $cat_id) {
                    if (!isset($category['parent_cats'][$cat_id])) {
                        $html .= "<option value='".$category['categories'][$cat_id]['category_id']."'>" . trim($name."->". $category['categories'][$cat_id]['name'],"->") . "</option>";
                    }
                    if (isset($category['parent_cats'][$cat_id])) {
                        $html .= "<option value='".$category['categories'][$cat_id]['category_id']."'>" . trim($name."->".$category['categories'][$cat_id]['name'],"->") . "</option>";
                        $html .= buildCategory($cat_id, $category,trim($name."->".$category['categories'][$cat_id]['name'],"->"));
                        $html .= "</option>";
                    }
                }
               
            }
            return $html;
        }
        echo buildCategory(0, $category ,'');
?>
</select>
<?php
        echo '<br><br>';
        /*--------------------------------end-------------------------------------------------------------------------------------------------------------------------*/

/*This function used for show all categories in menu*/


function buildCategory1($parent, $category) {
            $html = "";
            if (isset($category['parent_cats'][$parent])) {
                $html .= "<ul>\n";
                foreach ($category['parent_cats'][$parent] as $cat_id) {
                    if (!isset($category['parent_cats'][$cat_id])) {
                        $html .= "<li>\n  <a href='#'>" .$category['categories'][$cat_id]['name'] . "</a>\n</li> \n";
                    }
                    if (isset($category['parent_cats'][$cat_id])) {
                        $html .= "<li>\n  <a href='#'>" .$category['categories'][$cat_id]['name'] . "</a> \n";
                        $html .= buildCategory1($cat_id, $category);
                        $html .= "</li> \n";
                    }
                }
                $html .= "</ul> \n";
            }
            return $html;
        }
        echo buildCategory1(0, $category);
/*--------------------------------end-------------------------------------------------------------------------------------------------------------------------*/

?>

3. How to show all categories with nth subcategories in PHP

<?php
//connect to mysql and select db
$conn = mysqli_connect('localhost', 'rootuser', 'rootpwd','corephp');

if( !empty($conn->connect_errno)) die("Error " . mysqli_error($conn));

//call the recursive function to print category listing
category_tree(0);

//Recursive php function
function category_tree($catid){
global $conn;

$sql = "select * from category where parent_id ='".$catid."'";
$result = $conn->query($sql);

while($row = mysqli_fetch_object($result)):
$i = 0;
if ($i == 0) echo '<ul>';
 echo '<li>' . $row->cat_name;
 category_tree($row->id);
 echo '</li>';
$i++;
 if ($i > 0) echo '</ul>';
endwhile;
}
//close the connection
mysqli_close($conn);
?>