Recursive mysql drop down list

thesavior · 08-25-2006, 02:53 AM

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

marek_mar · 08-25-2006, 03:24 AM

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(1 => '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.

thesavior · 08-25-2006, 03:32 AM

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?

marek_mar · 08-25-2006, 04:18 AM

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.

thesavior · 08-30-2006, 12:59 AM

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

marek_mar · 08-30-2006, 12:30 PM

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(1 => '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) / 2 );

}

?>

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

thesavior · 08-30-2006, 03:35 PM

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

marek_mar · 08-30-2006, 05:56 PM

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(1 => 'maincat', 'subcat'), 'element' => array(1 => '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) / 2 ); 

} 

?>

Should do it...

thesavior · 08-30-2006, 09:29 PM

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?

marek_mar · 08-30-2006, 10:15 PM

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(1 => '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'] + 1 == $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.

thesavior · 08-31-2006, 12:33 AM

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.

marek_mar · 08-31-2006, 12:51 AM

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.

thesavior · 08-31-2006, 02:00 AM

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.