Page 1 of 3 123 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    392
    Rep Power
    76

    Adding sub category to list


    My script currently will display a list of categories as links for navigation. Now I am being tasked to create sub categories. It looks like the previous developer set up a table for this. I think if I manually enter id number for cat_parent then I might be able to display them. Just not sure how to go about it.

    category table has these columns: cat_id, cat_name, cat_parent and cat_order

    Category_assoc table has cat_assoc_id, product_id and cat_id
    This is part of the display class. Would I add to this method in the display class?
    PHP Code:
    function displayCategories()
        {
            global 
    $wpdb;
            
    $output "";
            
    $query "SELECT DISTINCT(`cat`.`cat_name`), `cat`.`cat_id` FROM my_categories as cat
                            LEFT JOIN my_category_assoc as assoc
                            ON assoc.cat_id = cat.cat_id
                            LEFT JOIN my_products as pr
                            ON pr.prod_id = assoc.prod_id
                            WHERE `"
    $this->company "` = '1'
                            ORDER BY cat.cat_name ASC"
    ;
            
            
    $cats $wpdb->get_results($wpdb->prepare($query), ARRAY_A);        
            if(
    count($cats) > 0)
            {
                
    $output .= "<ul>\n";
                foreach(
    $cats as $cat)
                {
                    
    $output .= "<li><a href='?page_id="$this->curCompanyPage ."&category="$cat['cat_id'] ."'";
                    if(
    $_REQUEST['category'] == $cat['cat_id']) $output .= " class='cur_category'";
                    
    $output .= ">{$cat['cat_name']}</a></li>\n";                
                }
                
    $output .= "</ul>\n";
            }
            
            echo 
    $output;
        } 
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    how deeply can the subcategories be nested? If you simply have one level of subcategories for each category, all you need is a LEFT JOIN on the parent ID:

    Code:
    SELECT
    	...
    FROM
    	my_categories AS main_categories
    	LEFT JOIN my_categories AS child_categories ON child_categories.cat_parent = main_categories.cat_id
    ...
    ;
    If the nesting can be arbitrarily deep (i. e. a child category can have its own child categories, which again can have child categories etc.), this is gonna be ugly, because MySQL cannot really do that.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    392
    Rep Power
    76
    Just one level deep.

    Fruit
    >Apple
    >Orange
    >Peach

    Colors
    >Red
    >Green
    >Blue
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Then do what I described.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    392
    Rep Power
    76
    Thanks I tried it and the list is not displayed. Plus I am not sure how to account for the <li> for the sub categories.
    PHP Code:
    function displayCategories()
        {
            global 
    $wpdb;
            
    $output "";
            
    $query "SELECT DISTINCT(`cat`.`cat_name`), `cat`.`cat_id` FROM my_categories AS main_categories
                            LEFT JOIN my_category_assoc as assoc
                            ON assoc.cat_id = cat.cat_id
                            LEFT JOIN my_products as pr
                            ON pr.prod_id = assoc.prod_id
                            LEFT JOIN my_categories AS child_categories ON child_categories.cat_parent = main_categories.cat_id
                            WHERE `"
    $this->company "` = '1'
                            ORDER BY cat.cat_name ASC"
    ;
            
            
    $cats $wpdb->get_results($wpdb->prepare($query), ARRAY_A);        
            if(
    count($cats) > 0)
            {
                
    $output .= "<ul>\n";
                foreach(
    $cats as $cat)
                {
                    
    $output .= "<li><a href='?page_id="$this->curCompanyPage ."&category="$cat['cat_id'] ."'";
                    if(
    $_REQUEST['category'] == $cat['cat_id']) $output .= " class='cur_category'";
                    
    $output .= ">{$cat['cat_name']}</a></li>\n";                
                }
                
    $output .= "</ul>\n";
            }
            
            echo 
    $output;
        } 
    Last edited by mallen; June 17th, 2013 at 02:54 PM.
  10. #6
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Of course you cannot simply add some lines to the query, and that's it. You have to actually write the code for your subcategories.

    First of all, you need to understand the general logic: You have joined the category table with itself to get the child categories. The result will be something like this:

    Code:
    main category | child category
    --------------+---------------
    Fruit         | Apple
    Fruit         | Orange
    Fruit         | Peach
    Colors        | Red
    Colors        | Green
    Colors        | Blue
    And now you wanna generate a nested list from that:

    html Code:
    <ul>
    	<li>
    		<h2>Fruit</h2>
    		<ul>
    			<li>Apple</li>
    			<li>Orange</li>
    			<li>Peach</li>
    		</ul>	
    	</li>
    	<li>
    		<h2>Colors</h2>
    		<ul>
    			<li>Red</li>
    			<li>Green</li>
    			<li>Blue</li>
    		</ul>	
    	</li>
    </ul>

    What you do is loop through the result set, keep track of the current category, and whenever the category changes (and right at the start), you display the heading for the main category.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    392
    Rep Power
    76
    Ok so I will work out the psuedo code on this. Does it matter that I won't be using header tags?

    <ul>
    <li>Fruit</li>
    <ul>
    <li>Apple</li>
    <li>Orange</li>
    <li>Peach</li>
    </ul>
    </li>
    <li>Colors</li>
    <ul>
    <li>Red</li>
    <li>Green</li>
    <li>Blue</li>
    </ul>
    </li>
    </ul>
  14. #8
  15. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    That's not valid HTML. A list can only have li elements as its children.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  16. #9
  17. No Profile Picture
    Dazed&Confused
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2002
    Location
    Tempe, AZ
    Posts
    506
    Rep Power
    128
    Like this:

    Code:
    <ul>
        <li>Fruit
            <ul>
                <li>Apple</li>
                <li>Orange</li>
                <li>Peach</li>
            </ul>   
        </li>
        <li>Colors
            <ul>
                <li>Red</li>
                <li>Green</li>
                <li>Blue</li>
            </ul>   
        </li>
    </ul>
    You almost had it right except you were closing the Fruit and Colors li tags twice; once on the line and once after the child ul.
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    392
    Rep Power
    76
    Thanks. Now I just have to work out the logic to produce the list.
  20. #11
  21. No Profile Picture
    I haz teh codez!
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2003
    Posts
    2,552
    Rep Power
    2337
    It's not hard. Start by writing the logic down on paper using a flow chart.
    I ♥ ManiacDan & requinix

    This is a sig, and not necessarily a comment on the OP:
    Please don't be a help vampire!
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Dec 2004
    Posts
    3,016
    Rep Power
    376
    all you need is a loop, a variable to hold current category and if statement which will start a new <ul> previous </ul></li>...
  24. #13
  25. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by paulh1983
    all you need is a loop, a variable to hold current category and if statement which will start a new <ul> previous </ul></li>...
    Um, didn't we say that already?
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Dec 2004
    Posts
    3,016
    Rep Power
    376
    maybe you did but he did state couple of posts before:

    "Thanks. Now I just have to work out the logic to produce the list." so...
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    392
    Rep Power
    76
    Ok please be easy on me. This I know will take me many attempts to get right. This is what I came up with. Trying to track the $previous and $first variables.

    PHP Code:
    function displayCategories()
        {
            global 
    $wpdb;
            
    $output "";
            
    $query "SELECT DISTINCT(`cat`.`cat_name`), `cat`.`cat_id` FROM my_categories as cat
                      LEFT JOIN my_category_assoc as assoc
                      ON assoc.cat_id = cat.cat_id
                      LEFT JOIN my_products as pr
                      ON pr.prod_id = assoc.prod_id
                      LEFT JOIN my_categories AS child_categories ON child_categories.cat_parent = main_categories.cat_id
                      WHERE `"
    $this->company "` = '1'
                      ORDER BY cat.cat_name ASC"
    ;
            
    $previous '';//
            
    $first true;//
            
    $cats $wpdb->get_results($wpdb->prepare($query), ARRAY_A);        
            while (
    count($cats) > 0)
            if 
    cats != $previous)
              echo 
    '</ul></li>';
        }
        echo <
    li>
         <
    ul>
         
    $previous $wpdb->get_results($wpdb->prepare($query), ARRAY_A)
          
    // display the subcategories
      
    echo '<li>' $wpdb->get_results($wpdb->prepare($query), ARRAY_A)
    '</li>';
      
    // it's no longer the first time
      
    $first false;
      } while (
    $wpdb->get_results($wpdb->prepare($query), ARRAY_A)); ?>
       echo </ul></li></ul>;
         
            {
                $output .= "<ul>\n";
                foreach($cats as $cat)
                {
                    $output .= "<li><a href='?page_id=". $this->curCompanyPage ."&category=". $cat['cat_id'] ."'";
                    if($_REQUEST['category'] == $cat['cat_id']) $output .= " class='cur_category'";
                    $output .= ">{$cat['cat_name']}</a></li>\n";                
                }
                $output .= "</ul>\n";
            }
            
            echo $output;
        } 
Page 1 of 3 123 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo