Skip to main content

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.


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

Comments

  1. Thank you for your post, I look for such article along time, today i find it finally. this post give me lots of advise it is very useful for me.
    web designing course in chennai

    ReplyDelete
  2. Check out the top reasons why businesses should hire a React Development Agency to build their web or mobile application. Zazz is a premier ReactJS Development Company. For more information please visit the website.

    ReplyDelete

Post a Comment

Popular posts from this blog

Run and compile sass scss file to css using node

  Today we learn how to use scss and generate css using node  or Run and compile sass scss file to css using node   So please follow simple  steps :-   Today we will create a project that can read scss file and generates css with it  Note: Make sure you have installed node in your system. If you want to help to install node js based on your system then check our other tutorial or check node js official website. Now create a blank folder and open  terminal(linux) or cmd(windows) and navigate to your current project folder by using cd command Now run below command npm init after enter it will ask you some package info that you can fill according to you or just keep enter until it finished. The above command will generate package.json file Now  we will install npm module that will convert our scss to css Run below command: npm install node-sass So we have installed node-sass package . Now open package.json file in your editor and add below code into it into

jQuery Datatable add date range filter

jQuery Datatable add date range filter Datatable is most useful jQuery plugin that helps to make our html tables more powerful and give powers to user to filter , search, sort, pagination etc, But Data table provides a common filter only and yes we can customize and add filter for each column, but still sometimes we need an advance filter like show results only between a date range, So today we will learn how to create a minimum and maximum date range fields and show date picker on it, and user can fill dates by selecting dates and data table will auto filter records based on it. Keep follow below steps :- I am using Bootstrap if you want to use any other framework then you can use. Create a new index.php file  and paste below code in it, i have used all required CDN like bootstrap, datatable, datepicker etc. <!DOCTYPE html> <html> <head>     <title>Datatable Date Range Filter Example</title>     <link rel="stylesheet" href="https://maxcd

How to retrieve Facebook Likes, share , comment Counts

function facebook_count($url){     // Query in FQL     $fql  = "SELECT share_count, like_count, comment_count ";     $fql .= " FROM link_stat WHERE url = '$url'";     $fqlURL = "https://api.facebook.com/method/fql.query?format=json&query=" . urlencode($fql);     // Facebook Response is in JSON     $response = file_get_contents($fqlURL);     return json_decode($response); } $fb = facebook_count('https://www.facebook.com/BahutHoGyiPadhai'); // facebook share count echo $fb[0]->share_count;  echo "like"; // facebook like count echo $fb[0]->like_count ; echo "comment"; // facebook comment count echo $fb[0]->comment_count;  ?>