Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13
  1. #1
    Senior Coder
    Join Date
    Aug 2005
    Posts
    1,119
    Thanks
    2
    Thanked 1 Time in 1 Post

    Recursive mysql drop down list

    My database is set up as shown here: http://sqllessons.com/categories.html

    What I want to do, is run a query and some php code and turn it into something formatted like this:

    Code:
    <select class="select" name="id">
    	<optgroup class="maincat" label="Digital Art">
    		<option class="sub first" value="3">3d Art</option>
    		<option class="sub" value="4">Drawings</option>
    		<option class="sub" value="5">Miscellaneous</option>
    		<option class="sub" value="6">Photo_Manipulation</option>
    		<option class="sub" value="10">Wallpapers</option>
    		<optgroup class="subcat" label="Internet Use">
    			<option class="subsub first" value="12">Signatures</option>
    			<option class="subsub" value="13">Avatars</option>
    		</optgroup>
    	</optgroup>
    	<optgroup class="maincat" label="Photography">
    		<option class="sub first" value="7">Landscape</option>
    		<option class="sub" value="8">Portrait</option>
    		<option class="sub" value="9">Architecture</option>
    	</optgroup>
    </select>
    The parts of that I want to make sure really happen, is that it can handle an infinite amount of subcategories.

    If the thing doesn't have a parent category, it will say its class is "maincat" and it will be up at top. If it is a subcategory, but doesn't have categories under that, it will be a class "sub". If there is a sub category under the original...well, just look at the above code and youll figure it out. The above code looks like the link below, which is why I need it formatted like the above.

    http://dev.powerwd.com/dropdown.html

  • #2
    Senior Coder
    Join Date
    Aug 2003
    Location
    One step ahead of you.
    Posts
    2,815
    Thanks
    0
    Thanked 3 Times in 3 Posts
    If you'd decide to go with the scheme suggested by me before you could do it like this:
    (mysql extension example)
    PHP Code:
    <?php
    // You know how to connect.
    $sql 'SELECT id, level, other_columns FROM table ORDER BY left_id';
    $result mysql_query($sql) or die(mysql_error());
    $level 0;
    $classes = array(=> 'maincat''sub''subsub');
    $first false;

    print 
    '<select class="select" name="id">';
    while(
    $row mysql_retch_assoc($result))
    {
        if(
    $row['level'] > $level)
        {
            print 
    str_repeat("\t"$row['level']) . '<optgroup class="' $classes[$row['level']] . '" label="' $row['name'] . '">';
            
    $first true;
            
    $level $row['level'];
            continue;
        }
        if(
    $row['level'] < $level)
        {
            print 
    str_repeat("\t"$row['level']) . '</optgroup>';
            
    $level $row['level'];
        }
        print 
    str_repeat("\t"$row['level']) . '<option class="' $classes[$row['level']] . (($first) ? ' first' '') . '" value="' $row['id'] . '">' $row['name'] . '</option>';
        
    $first false;
    }
    print 
    '</select>';
    ?>
    I have no PHP to test it. The class selection may not work as expected. You could think about generating that part "on the fly" if it isn't too complex. Try a few things and it should work.

    BTW that method you chose doesn't allow infinete subcategories in terms of depth.
    I'm not sure if this was any help, but I hope it didn't make you stupider.

    Experience is something you get just after you really need it.
    PHP Installation Guide Feedback welcome.

  • #3
    Senior Coder
    Join Date
    Aug 2005
    Posts
    1,119
    Thanks
    2
    Thanked 1 Time in 1 Post
    With your method, what are the column names:

    `left_id` int(10) NOT NULL,
    `right_id` int(10) NOT NULL,
    `level` mediumint(8) unsigned NOT NULL,

    and what would i put in each?

    This is my sql for my table currently.

    Code:
    CREATE TABLE `categories` (
      `id` int(11) NOT NULL auto_increment,
      `name` varchar(37) NOT NULL,
      `parentid` int(11) default NULL,
      PRIMARY KEY  (`id`),
      KEY `parentid_fk` (`parentid`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;
    
    -- 
    -- Dumping data for table `categories`
    -- 
    
    INSERT INTO `categories` VALUES (1, 'Digital_Art', NULL);
    INSERT INTO `categories` VALUES (2, 'Photography', NULL);
    INSERT INTO `categories` VALUES (3, '3d_Art', 1);
    INSERT INTO `categories` VALUES (4, 'Drawings', 1);
    INSERT INTO `categories` VALUES (5, 'Miscellaneous', 1);
    INSERT INTO `categories` VALUES (6, 'Photo_Manipulation', 1);
    INSERT INTO `categories` VALUES (7, 'Landscape', 2);
    INSERT INTO `categories` VALUES (8, 'Portrait', 2);
    INSERT INTO `categories` VALUES (9, 'Architecture', 2);
    INSERT INTO `categories` VALUES (10, 'Wallpapers', 1);
    INSERT INTO `categories` VALUES (11, 'Internet_Use', 1);
    INSERT INTO `categories` VALUES (12, 'Signatures', 11);
    INSERT INTO `categories` VALUES (13, 'Avatars', 11);
    What would i do to change it to being your method?

  • #4
    Senior Coder
    Join Date
    Aug 2003
    Location
    One step ahead of you.
    Posts
    2,815
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Well there should a big block of code below the sql that is supposed to fill these for you (the MPTT class). Inserting the left and right id's is not that simple and thatsa why I wrote that class.
    The process should be described in that article I linked to last time if you really want to know (there is an image that explains the left/right id numbering best).

    BTW you could test if the select is created correctly with the sample data.
    I'm not sure if this was any help, but I hope it didn't make you stupider.

    Experience is something you get just after you really need it.
    PHP Installation Guide Feedback welcome.

  • #5
    Senior Coder
    Join Date
    Aug 2005
    Posts
    1,119
    Thanks
    2
    Thanked 1 Time in 1 Post
    I was able to enter my data successfully with your form. However using your code above to create a drop down menu did not work very well. Here is what your code returned:

    Code:
    <select class="select" title="id">
    	<optgroup class="maincat" label="Photography">
    		<optgroup class="sub" label="Architecture">
    			<option class="sub first" value="12">Portrait</option>
    			<option class="sub" value="11">Landscape</option>
    		</optgroup>
    		<option class="maincat" value="1">Digital Art</option>
    		<optgroup class="sub" label="3D Art">
    			<option class="sub first" value="3">Drawings</option>
    			<option class="sub" value="4">Miscellaneous</option>
    			<option class="sub" value="5">Photo Manipulation</option>
    			<option class="sub" value="6">Wallpapers</option>
    			<option class="sub" value="7">Internet Use</option>
    			<optgroup class="subsub" label="Signatures">
    				<option class="subsub first" value="9">Avatars</option>
    </select>
    It should look something like this:
    Code:
    <select class="select" name="id">
    	<optgroup class="maincat" label="Digital Art">
    		<option class="sub first" value="3">3d Art</option>
    		<option class="sub" value="4">Drawings</option>
    		<option class="sub" value="5">Miscellaneous</option>
    		<option class="sub" value="6">Photo_Manipulation</option>
    		<option class="sub" value="10">Wallpapers</option>
    		<optgroup class="subcat" label="Internet Use">
    			<option class="subcatsub first" value="12">Signatures</option>
    			<option class="subcatsub" value="13">Avatars</option>
    		</optgroup>
    	</optgroup>
    	<optgroup class="maincat" label="Photography">
    		<option class="sub first" value="7">Landscape</option>
    		<option class="sub" value="8">Portrait</option>
    		<option class="sub" value="9">Architecture</option>
    	</optgroup>
    </select>
    You can see them side by side here:
    http://dev.powerwd.com/dropdown.html

    Here is the mysql data for mine:

    Code:
    CREATE TABLE `testcat` (
      `id` mediumint(8) unsigned NOT NULL auto_increment,
      `title` varchar(200) NOT NULL,
      `left_id` int(10) NOT NULL,
      `right_id` int(10) NOT NULL,
      `level` mediumint(8) unsigned NOT NULL,
      UNIQUE KEY `id` (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;
    
    -- 
    -- Dumping data for table `testcat`
    -- 
    
    INSERT INTO `testcat` VALUES (1, 'Digital Art', 9, 20, 1);
    INSERT INTO `testcat` VALUES (2, '3D Art', 10, 11, 2);
    INSERT INTO `testcat` VALUES (3, 'Drawings', 12, 13, 2);
    INSERT INTO `testcat` VALUES (4, 'Miscellaneous', 14, 15, 2);
    INSERT INTO `testcat` VALUES (5, 'Photo Manipulation', 16, 17, 2);
    INSERT INTO `testcat` VALUES (6, 'Wallpapers', 18, 19, 2);
    INSERT INTO `testcat` VALUES (7, 'Internet Use', 20, 25, 2);
    INSERT INTO `testcat` VALUES (8, 'Signatures', 21, 22, 3);
    INSERT INTO `testcat` VALUES (9, 'Avatars', 23, 24, 3);
    INSERT INTO `testcat` VALUES (10, 'Photography', 1, 8, 1);
    INSERT INTO `testcat` VALUES (11, 'Landscape', 6, 7, 2);
    INSERT INTO `testcat` VALUES (12, 'Portrait', 4, 5, 2);
    INSERT INTO `testcat` VALUES (13, 'Architecture', 2, 3, 2);
    Last edited by thesavior; 08-30-2006 at 01:03 AM.

  • #6
    Senior Coder
    Join Date
    Aug 2003
    Location
    One step ahead of you.
    Posts
    2,815
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Now I made sure that this works and can handle the select menu generation to any depth.
    PHP Code:
    <?php
    $sql 
    'SELECT id, left_id, right_id, level, title FROM data ORDER BY left_id';
    $result mysql_query($sql) or die(mysql_error());
    $level 1;
    $classes = array(=> 'maincat''sub''subsub');
    $first false;

    print 
    '<select class="select" name="id">' "\n";
    while(
    $row mysql_fetch_assoc($result))
    {
        while(
    $level $row['level'])
        {
            
    $level--;
            print 
    str_repeat("\t"$level) . '</optgroup>' "\n";
        }
        if(
    has_children($row['left_id'], $row['right_id']))
        {
            print 
    str_repeat("\t"$row['level']) . '<optgroup class="' $classes[$row['level']] . '" label="' $row['title'] . '">' "\n";
            
    $level++;
            
    $first true;
            continue;
        }
        print 
    str_repeat("\t"$row['level']) . '<option class="' $classes[$row['level']] . (($first) ? ' first' '') . '" value="' $row['id'] . '">' $row['title'] . '</option>' "\n";
        
    $first false;
    }
    while(
    $level 1)
    {
        
    $level--;
        print 
    str_repeat("\t"$level) . '</optgroup>' "\n";
    }

    print 
    '</select>' "<br />\n";

    function 
    has_children($left_id$right_id)
    // MPTT is good!
        
    return (bool) ( ($right_id $left_id 1) / );
    }
    ?>
    Your tree wasn't ordered the way your HTML menu was ordered (and had an inconsistency - not sure why) so I fixed that and ordered for you.
    Code:
    -- 
    -- Table structure for table `data`
    -- 
    
    CREATE TABLE `data` (
      `id` mediumint(8) unsigned NOT NULL auto_increment,
      `title` varchar(200) NOT NULL,
      `left_id` int(10) NOT NULL,
      `right_id` int(10) NOT NULL,
      `level` mediumint(8) unsigned NOT NULL,
      UNIQUE KEY `id` (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;
    
    -- 
    -- Dumping data for table `data`
    -- 
    
    INSERT INTO `data` (`id`, `title`, `left_id`, `right_id`, `level`) VALUES (1, 'Digital Art', 1, 18, 1),
    (2, '3D Art', 2, 3, 2),
    (3, 'Drawings', 4, 5, 2),
    (4, 'Miscellaneous', 6, 7, 2),
    (5, 'Photo Manipulation', 8, 9, 2),
    (6, 'Wallpapers', 10, 11, 2),
    (7, 'Internet Use', 12, 17, 2),
    (8, 'Signatures', 13, 14, 3),
    (9, 'Avatars', 15, 16, 3),
    (10, 'Photography', 19, 26, 1),
    (11, 'Landscape', 24, 25, 2),
    (12, 'Portrait', 22, 23, 2),
    (13, 'Architecture', 20, 21, 2);
    I'm not sure if this was any help, but I hope it didn't make you stupider.

    Experience is something you get just after you really need it.
    PHP Installation Guide Feedback welcome.

  • #7
    Senior Coder
    Join Date
    Aug 2005
    Posts
    1,119
    Thanks
    2
    Thanked 1 Time in 1 Post
    Okay. That works, but now the problem is that you are echoing out the level, but if it is a cat, it still echoes:
    <optgroup class="sub" label="Internet Use">
    <option class="subcatsub first" value="8">Signatures</option>
    <option class="subcatsub" value="9">Avatars</option>
    </optgroup>

    it should have the class: "subcat".

    However only categories should have subcat.

    By the way, the updated version side by side is here:
    http://dev.powerwd.com/dropdown.html

  • #8
    Senior Coder
    Join Date
    Aug 2003
    Location
    One step ahead of you.
    Posts
    2,815
    Thanks
    0
    Thanked 3 Times in 3 Posts
    PHP Code:
    <?php 
    $sql 
    'SELECT id, left_id, right_id, level, title FROM data ORDER BY left_id'
    $result mysql_query($sql) or die(mysql_error()); 
    $level 1
    $classes = array('cat' => array(=> 'maincat''subcat'), 'element' => array(=> 'sub''subcatsub')); 
    $first false

    print 
    '<select class="select" name="id">' "\n"
    while(
    $row mysql_fetch_assoc($result)) 

        while(
    $level $row['level']) 
        { 
            
    $level--; 
            print 
    str_repeat("\t"$level) . '</optgroup>' "\n"
        } 
        if(
    has_children($row['left_id'], $row['right_id'])) 
        { 
            print 
    str_repeat("\t"$row['level']) . '<optgroup class="' $classes['cat'][$row['level']] . '" label="' $row['title'] . '">' "\n"
            
    $level++; 
            
    $first true
            continue; 
        } 
        print 
    str_repeat("\t"$row['level']) . '<option class="' $classes['element'][$row['level']] . (($first) ? ' first' '') . '" value="' $row['id'] . '">' $row['title'] . '</option>' "\n"
        
    $first false

    while(
    $level 1

        
    $level--; 
        print 
    str_repeat("\t"$level) . '</optgroup>' "\n"


    print 
    '</select>' "<br />\n"

    function 
    has_children($left_id$right_id
    // MPTT is good! 
        
    return (bool) ( ($right_id $left_id 1) / ); 

    ?>
    Should do it...
    I'm not sure if this was any help, but I hope it didn't make you stupider.

    Experience is something you get just after you really need it.
    PHP Installation Guide Feedback welcome.

  • #9
    Senior Coder
    Join Date
    Aug 2005
    Posts
    1,119
    Thanks
    2
    Thanked 1 Time in 1 Post
    Awsome. I had to change that a little, but I got it to work. Thanks alot.

    Just out of curiosity, how would you make it label the last <option> of every group to have the class "last" as well?
    Last edited by thesavior; 08-30-2006 at 09:32 PM.

  • #10
    Senior Coder
    Join Date
    Aug 2003
    Location
    One step ahead of you.
    Posts
    2,815
    Thanks
    0
    Thanked 3 Times in 3 Posts
    PHP Code:
    $sql 'SELECT id, left_id, right_id, level, title FROM data ORDER BY left_id';
    $result mysql_query($sql) or die(mysql_error());
    $level 1;
    $classes = array(=> 'maincat''sub''subsub');
    $parents = array();
    $first false;

    print 
    '<select class="select" name="id">' "\n";
    while(
    $row mysql_fetch_assoc($result))
    {
        while(
    $level $row['level'])
        {
            
    $level--;
            
    array_pop($parents);
            print 
    str_repeat("\t"$level) . '</optgroup>' "\n";
        }
        if(
    has_children($row['left_id'], $row['right_id']))
        {
            print 
    str_repeat("\t"$row['level']) . '<optgroup class="' $classes[$row['level']] . '" label="' $row['title'] . '">' "\n";
            
    $parents[] = $row['right_id'];
            
    $level++;
            
    $first true;
            continue;
        }
        
    $pos '';
        if(
    $first)
        {
            
    $pos ' first';
        }
        if(
    $row['right_id'] + == $parents[count($parents) - 1])
        {
            
    $pos ' last';
        }
        print 
    str_repeat("\t"$row['level']) . '<option class="' $classes[$row['level']] . $pos '" value="' $row['id'] . '">' $row['title'] . '</option>' "\n";
        
    $first false;
    }
    while(
    $level 1)
    {
        
    $level--;
        print 
    str_repeat("\t"$level) . '</optgroup>' "\n";
    }
    unset(
    $parents); 
    Actually I think some of the code could be simplified if you would take a slightly different attempt for the whole... I'm too lazy to even check it.
    Last edited by marek_mar; 08-30-2006 at 10:24 PM.
    I'm not sure if this was any help, but I hope it didn't make you stupider.

    Experience is something you get just after you really need it.
    PHP Installation Guide Feedback welcome.

  • #11
    Senior Coder
    Join Date
    Aug 2005
    Posts
    1,119
    Thanks
    2
    Thanked 1 Time in 1 Post
    Actually, that didn't really work at all? The previous one still laid it out correctly, this one just kind of messed everything up. It wasnt changing classes for the cats and options, and it just kind of fell apart.

    Im guess Im happy with what I had before If its easier to just leave it the way it was.

  • #12
    Senior Coder
    Join Date
    Aug 2003
    Location
    One step ahead of you.
    Posts
    2,815
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I don't really know what you mean...
    The code makes this:
    Code:
    <select class="select" name="id">
    	<optgroup class="maincat" label="Digital Art">
    		<option class="sub first" value="2">3D Art</option>
    		<option class="sub" value="3">Drawings</option>
    		<option class="sub" value="4">Miscellaneous</option>
    		<option class="sub" value="5">Photo Manipulation</option>
    		<option class="sub" value="6">Wallpapers</option>
    		<optgroup class="sub" label="Internet Use">
    			<option class="subsub first" value="8">Signatures</option>
    			<option class="subsub last" value="9">Avatars</option>
    		</optgroup>
    	</optgroup>
    	<optgroup class="maincat" label="Photography">
    		<option class="sub first" value="13">Architecture</option>
    		<option class="sub" value="12">Portrait</option>
    		<option class="sub last" value="11">Landscape</option>
    	</optgroup>
    </select>
    on my server.
    I'm not sure if this was any help, but I hope it didn't make you stupider.

    Experience is something you get just after you really need it.
    PHP Installation Guide Feedback welcome.

  • #13
    Senior Coder
    Join Date
    Aug 2005
    Posts
    1,119
    Thanks
    2
    Thanked 1 Time in 1 Post
    Yah, I want it to show up like this:

    Code:
    <select class="select" name="id">
    	<optgroup class="maincat" label="Digital Art">
    		<option class="sub first" value="2">3D Art</option>
    		<option class="sub" value="3">Drawings</option>
    		<option class="sub" value="4">Miscellaneous</option>
    		<option class="sub" value="5">Photo Manipulation</option>
    		<option class="sub last" value="6">Wallpapers</option>
    		<optgroup class="subcat" label="Internet Use">
    			<option class="subsub first" value="8">Signatures</option>
    			<option class="subsub last" value="9">Avatars</option>
    		</optgroup>
    	</optgroup>
    	<optgroup class="maincat" label="Photography">
    		<option class="sub first" value="13">Architecture</option>
    		<option class="sub" value="12">Portrait</option>
    		<option class="sub last" value="11">Landscape</option>
    	</optgroup>
    </select>
    Its messing up the "sub cat" and it isn't adding "last" on the first grouping.


  •  

    Posting Permissions

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