SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Member
    Join Date
    Oct 2012
    Posts
    4
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    PHP category and subcategory

    Hi all,

    how to show category and subcategory to unlilmited level in dropdown.

    Plz help me
    thanks u

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    How are they stored at the moment?

  3. #3
    SitePoint Member
    Join Date
    Oct 2012
    Posts
    4
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    the data are stored in the datebase like this

    cat_id cat_pid cat_name
    1 0 Movies
    2 0 coutry
    3 1 English Movies
    4 1 Indian Movies
    5 1 pakistani Movies
    6 3 Action movie
    7 3 Love Move

    to unlimited level
    My code is


    <?php

    $sql="SELECT * FROM tbl_category where cat_pid=0 ORDER BY cat_id asc";
    //print "sql is ".$sql."";
    $base_cat = mysql_query($sql) or die($sql);

    print '<select name="pid" id="pid">';

    while($cat_list=mysql_fetch_array($base_cat))
    {
    //---fetch subcat for this cat
    ?>
    <option value="<?php echo $cat_list['cat_id'];?>"<?php if($cat_list['cat_id']==$res['cat_id']) { ?> selected="selected" <?php } ?>><?php echo $cat_list['cat_name'];?></option>
    <?php
    $query = "select * from tbl_category where cat_pid='".$cat_list['cat_id']."' ";
    $ret = mysql_query($query);
    while($row=mysql_fetch_assoc($ret))
    {
    print "<option value='".$row['cat_id']."'>-".$row['cat_name']."</option>";
    $subsctegory="select * from tbl_category where cat_pid= '".$row['cat_id']."'";
    $sub_res=mysql_query($subsctegory);
    while($sub=mysql_fetch_array($sub_res))
    {
    print "<option value='".$sub['cat_id']."'>--".$sub['cat_name']."</option>";

    }
    }

    }
    print'</select>';
    ?>

    but it show the data in the dropdown to level 2 but i want to unlliminted level

    thanks

  4. #4
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    64 Post(s)
    Tagged
    2 Thread(s)
    cat_id cat_pid cat_name
    1
    Movies
    2
    Music
    3 1 English Movies
    4 1 Indian Movies
    5 1 Pakistani Movies
    6 2 Heavy Metal
    7 2 Hip Hop

    Code MySQL:
    SELECT C1.cat_id, C1.cat_name, C1.cat_pid, C2.cat_id AS CHILD_ID, C2.cat_name AS CHILD_NAME
    FROM tbl_category AS C1 LEFT JOIN tbl_category AS C2 ON C1.cat_id = C2.cat_pid
    ORDER BY C1.cat_id, C1.cat_pid;
    cat_id cat_name cat_pid CHILD_ID CHILD_NAME
    1 Movies
    5 Pakistani Movies
    1 Movies
    4 Indian Movies
    1 Movies
    3 English Movies
    2 Music
    7 Hip Hop
    2 Music
    6 Heavy Metal
    3 English Movies 1

    4 Indian Movies 1

    5 Pakistani Movies 1

    6 Heavy Metal 2

    7 Hip Hop 2


    This is how'd I'd arrange the data before a PHP loop being done. I'm currently thinking my way through that part

    P.S. I love the fact that I can copy and paste a table out of MS Access into this forum!

  5. #5
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    I think this is termed a nested set model, which might help you turn up some more information on the different ways of dealing with them.

    This search of SP might work for you: http://www.sitepoint.com/forums/sear...earchid=406383 though I am not sure if the link will work, or for how long.

    If it doesn't then use Advanced Search, the term "nested set" constrained to Forums, and specifically "PHP" and "Databases & Mysql" and you should turn up some interesting conversations about how best to tackle this problem.

  6. #6
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    64 Post(s)
    Tagged
    2 Thread(s)
    Code MySQL:
    SELECT tbl_category.cat_id AS CAT_ID, tbl_category.cat_name AS CAT_NAME, tbl_category_1.cat_id AS PARENT_ID, tbl_category_1.cat_name AS PARENT_NAME
    FROM tbl_category LEFT JOIN tbl_category AS tbl_category_1 ON tbl_category.cat_pid = tbl_category_1.cat_id;

    CAT_ID CAT_NAME PARENT_ID PARENT_NAME
    1 Movies

    2 Music

    3 English Movies 1 Movies
    4 Indian Movies 1 Movies
    5 Pakistani Movies 1 Movies
    6 Heavy Metal 2 Music
    7 Hip Hop 2 Music
    8 Action 3 English Movies
    9 Adventure 3 English Movies

    After assigning record set to an array..
    For each $row, echo Cat Name, use array_filter() inside a while loop to find rows from PARENT_ID == to current $row[CAT_ID], and echo those, close the while loop once array_filter for that id returns null. Thoughts?

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    You also might want to read this: http://sqllessons.com/categories.html

  8. #8
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,178
    Mentioned
    64 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by guido2004 View Post
    You also might want to read this: http://sqllessons.com/categories.html
    I don't think that approach allows for an unlimited dynamic subcategory system. You have to create a new join for each subcategory. If my table has a tree 4 subcategories deep but I've only written in 3 joins, the 4th category would be lost.

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    I don't think that approach allows for an unlimited dynamic subcategory system. You have to create a new join for each subcategory. If my table has a tree 4 subcategories deep but I've only written in 3 joins, the 4th category would be lost.
    That's true. It was more to give some info about category and subcategory handling. To do unlimited depth, you'll have to extract all data from the table and loop through it using PHP, like you were showing in your replies.

    Something like this (@kundi ; I used mysqli_ instead of mysql_ functions, but the logic stays the same):
    PHP Code:
    $sql "
      SELECT 
          cat_id
        , cat_pid
        , cat_name
      FROM tbl_category 
      ORDER BY 
          cat_pid
        , cat_id
    "
    ;
    $result mysqli_query($link$sql) or die("mysql error " mysqli_error($link) . " in query " $sql);

    // load results in a query
    $catArray = array();
    while (
    $row mysqli_fetch_assoc($result)) {
      
    $catArray[$row['cat_pid']][$row['cat_id']] = $row['cat_name'];
    }

    // recursive function
    function loopTroughArray($cat_pid$array) {
      foreach (
    $catArray[$cat_pid] as $cat_id => $cat_name {
        echo 
    "cat id: " $cat_id " cat name: " $cat_name " - ";
        
    loopTroughArray($cat_id$array);
      }
    }

    // call the recursive function with cat_pid = 0
    loopTroughArray(0$array); 

  10. #10
    SitePoint Member
    Join Date
    Dec 2010
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This, I believe is the adjacency list model. I wrote about it recently: Traversing Hierarchy Tree Using PHP: Adjacency List Model.

    Here is an excerpt of code from the article that displays categories/subcategories stored in this manner:

    PHP Code:
    <?php
    /*
     * PHP code to traverse hierarchical data (adjacency list model)
     * http://911-need-code-help.blogspot.com/2012/08/php-adjacency-list-hierarchy-tree-traversal.html
     */
    $data = array();
    $index = array();
    $query mysql_query("SELECT id, parent_id, name FROM categories ORDER BY name");
    while (
    $row mysql_fetch_assoc($query)) {
        
    $id $row["id"];
        
    $parent_id $row["parent_id"] === NULL "NULL" $row["parent_id"];
        
    $data[$id] = $row;
        
    $index[$parent_id][] = $id;
    }
    /*
     * Recursive top-down tree traversal example:
     * Indent and print child nodes
     */
    function display_child_nodes($parent_id$level)
    {
        global 
    $data$index;
        
    $parent_id $parent_id === NULL "NULL" $parent_id;
        if (isset(
    $index[$parent_id])) {
            foreach (
    $index[$parent_id] as $id) {
                echo 
    str_repeat("-"$level) . $data[$id]["name"] . "\n";
                
    display_child_nodes($id$level 1);
            }
        }
    }
    display_child_nodes(NULL0);
    ?>
    PS: I am afraid there is no "one query solution" for this data structure, at least no "one query solution with unlimited depth".

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    apologies for my late entrance into this thread, i was only just now made aware of it by kyle who linked to it in another thread

    here is what i think is the critical point --
    Quote Originally Posted by kundi View Post
    how to show category and subcategory to unlilmited level in dropdown.
    my answer to that question is -- you don't

    as i tried to explain in my sqllessons article (thanks for the link, guido ), if you go below more than three or four subcategories deep, you should rethink how you want that information displayed

    i mean, really.... unlimited subcategories in a dropdown???

    next question, pls
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •