...

View Full Version : Recursive mysql drop down list



thesavior
08-25-2006, 03: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:



<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, 04:24 AM
If you'd decide to go with the scheme (http://www.codingforums.com/showthread.php?t=79773) suggested by me before you could do it like this:
(mysql extension example)


<?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, 04: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.



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, 05: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, 01: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:



<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:


<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:



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, 01:30 PM
Now I made sure that this works and can handle the select menu generation to any depth.


<?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.


--
-- 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, 04: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, 06:56 PM
<?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, 10: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, 11:15 PM
$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, 01: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, 01:51 AM
I don't really know what you mean...
The code makes this:


<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, 03:00 AM
Yah, I want it to show up like this:



<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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum