Contact me if you need to use this.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Unlimited Categories 1.3</title>
</head>
<!--
Author: Jim Michaels
Date: Feb 24, 2006
Abstract: A set of code that implements an unlimited categories display routine. it can also delete subtrees of categories.
CREATE TABLE `dbo`.`categories` (
`cat_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`category` VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'your category here',
PRIMARY KEY(`cat_id`),
UNIQUE KEY `ix_ccat_id` (`cat_id`),
INDEX `ix_ccat`(`category`)
) ENGINE=InnoDB;
CREATE TABLE `dbo`.`subcategories` (
`sub_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'used in case we need to delete a specific row',
`cat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'ref to cat_id from categories table',
`subcat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'cat_id of subcategories',
FOREIGN KEY (`cat_id`) REFERENCES `categories`(`cat_id`) ON DELETE CASCADE ON UPDATE NO ACTION,
-- FOREIGN KEY (`subcat_id`) REFERENCES `subcategories`(`cat_id`) ON DELETE CASCADE ON UPDATE NO ACTION,
PRIMARY KEY(`sub_id`),
INDEX `ix_sccat_id`(`cat_id`),
INDEX `ix_scsubcat_id`(`subcat_id`)
) ENGINE=InnoDB;
CREATE TABLE `dbo`.`categoryroots` (
`cat_id` int(10) unsigned NOT NULL default '0' COMMENT 'ref to cat_id from categories table',
PRIMARY KEY (`cat_id`),
FOREIGN KEY (`cat_id`) REFERENCES `categories`(`cat_id`) ON DELETE CASCADE ON UPDATE NO ACTION,
UNIQUE KEY `ix_crcat_id` (`cat_id`)
) ENGINE=InnoDB;
/*
e
f-b
g |
h |
i-c-a
j |
k |
l-d
m
----n
*/
INSERT INTO categories(cat_id,category) VALUES
(1,'a'),
(2,'b'),
(3,'c'),
(4,'d'),
(5,'e'),
(6,'f'),
(7,'g'),
(8,'h'),
(9,'i'),
(10,'j'),
(11,'k'),
(12,'l'),
(13,'m'),
(14,'n');
INSERT INTO categoryroots(cat_id) VALUES
(1),
(14);
INSERT INTO subcategories(cat_id,subcat_id) VALUES
(1,2),
(1,3),
(1,4),
(2,5),
(2,6),
(2,7),
(3,8),
(3,9),
(3,10),
(4,11),
(4,12),
(4,13);
-->
<?php
include "database_inc.php";
//-----------this code has not been debugged as of 1/30/2006-------------------
/*
function: traverse_tree
abstract: recursive subtree display of subcategories.
inputs:
level function should be initially called with this set to 1.
cat_id cat_id of tree to traverse
urlbase URL prefix for generated links. link will be {$urlbase}cat=$id
collapse default:true removes <br> and padding from output, replaces with a space. everything comes out on one line.
show_brackets default:true shows square brackets in the ouptut when entering or leaving a subtree
pad_string default:" " string to place before category in multiples of $level
*/
function traverse_tree($level=1,$cat_id,$urlbase,$collapse=true,$show_brackets=true,$pad_string=" ") {
global $link;
$qr2=mysql_query("SELECT c.category,sc.subcat_id FROM categories AS c,subcategories AS sc WHERE c.cat_id=sc.subcat_id AND sc.cat_id=$cat_id ORDER BY c.category", $link);
//make indenting for categories
if (0!=mysql_num_rows($qr2)) {
$pad=""; for ($i=1; $i<=$level; $i++) {$pad .= $pad_string;}
if ($show_brackets) {
if ($collapse) {echo " ";} else {echo $pad;}
echo '['; //start of category set
if ($collapse) {echo " ";} else {echo "<br>";}
}
echo "\n";
while ($rowr2=mysql_fetch_assoc($qr2)) {
if ($collapse) {echo " ";} else {echo $pad;}
echo "<a href=\"{$urlbase}cat=$rowr2[subcat_id]\">$rowr2[category]</a>";
if ($collapse) {echo " ";} else {echo "<br>";}
echo "\n";
traverse_tree($level+1,$rowr2['subcat_id'],$urlbase,$collapse,$show_brackets,$pad_string);
}
if ($show_brackets) {
if ($collapse) {echo " ";} else {echo $pad;}
echo ']'; //end of category set
if ($collapse) {echo " ";} else {echo "<br>";}
}
echo "\n";
}
//mysql_free_result($qr2);
}
/*
function: display_categories
abstract: recursive tree display of categories, starting with roots.
inputs:
urlbase URL prefix for generated links. link will be {$urlbase}cat=$id
collapse default:true removes <br> and padding from output, replaces with a space. everything comes out on one line.
show_brackets default:true shows square brackets in the ouptut when entering or leaving a subtree
pad_string default:" " string to place before category in multiples of $level
*/
function display_categories($urlbase,$collapse=true,$show_brackets=true,$pad_string=" ") {
//function will append cat=12 (the id number of the category)
//and generate a link
global $link;
$q=mysql_query("SELECT cr.cat_id,c.category FROM categoryroots AS cr,categories AS c WHERE c.cat_id=cr.cat_id ORDER BY c.category", $link);
if ($show_brackets) {
echo '['; //start of category set
if ($collapse) {echo " ";} else {echo "<br>";}
}
echo "\n";
while ($row=mysql_fetch_assoc($q)) {
echo "<a href=\"{$urlbase}cat=$row[cat_id]\">$row[category]</a>";
if ($collapse) {echo " ";} else {echo "<br>";}
echo "\n";
traverse_tree(1, $row['cat_id'],$urlbase,$collapse,$show_brackets,$pad_string);
}
if ($show_brackets) {
echo ']'; //end of category set
if ($collapse) {echo " ";} else {echo "<br>";}
}
echo "\n";
//mysql_free_result($q);
}
/*
function: traverse_tree_select
abstract: recursive subtree display of subcategories in the form of <option> elements.
inputs:
level function should be initially called with this set to 1.
cat_id cat_id of tree to traverse
text prefix to category name. should be parent category name.
*/
function traverse_tree_select($level=1,$cat_id,$text) {
//just surround the call to this function with <select> tags and you're done.
global $link;
$qr2=mysql_query("SELECT sc.subcat_id,c.cat_id,c.category FROM categories AS c,subcategories AS sc WHERE c.cat_id=sc.subcat_id AND sc.cat_id=$cat_id ORDER BY c.category", $link);
if ($text) {$text .= ":";} //category separator
while ($rowr2=mysql_fetch_assoc($qr2)) {
echo "<option value=\"$rowr2[subcat_id]\">$text$rowr2[category]</option>\n";
traverse_tree_select($level+1,$rowr2['subcat_id'], $text . $rowr2['category']);
}
//mysql_free_result($qr2);
}
/*
function: display tree of categories as <select> element.
abstract: recursive subtree display of subcategories.
inputs:
name name of <select> element.
id optional id <select> element.
*/
function display_categories_select($name,$id=0) {
global $link;
if (0==$id) {$id="";} else {$id="id=\"$id\"";}
echo "<select size=\"1\" $id name=\"$name\">\n";
$q=mysql_query("SELECT cr.cat_id,c.category FROM categoryroots AS cr,categories AS c WHERE c.cat_id=cr.cat_id ORDER BY c.category", $link);
while ($row=mysql_fetch_assoc($q)) {
echo "<option value=\"$row[cat_id]\">$row[category]</option>\n";
traverse_tree_select(1, $row['cat_id'], "$row[category]");
}
echo "</select>\n";
//mysql_free_result($q);
}
/*
function: delete_tree
abstract: delete whole trees/subtrees based on cat_id.
inputs:
cat_id cat_id of tree to traverse
*/
function delete_tree($cat_id, $level=0) {
//do not call with the level parameter. only for internal use.
global $link;
if (0==$level) {
//delete primary root, if matches.
$q=mysql_query("SELECT cat_id FROM categoryroots WHERE cat_id=$cat_id", $link);
if ($row=mysql_fetch_assoc($q)) {
mysql_query("DELETE FROM categoryroots WHERE cat_id=$cat_id", $link);
}
mysql_free_result($q);
}
//delete subtree
$qr1=mysql_query("SELECT * FROM subcategories WHERE cat_id=$cat_id", $link);
while ($rowr1=mysql_fetch_assoc($qr1)) {
if ($rowr1['subcat_id']) { //not 0?
delete_tree($rowr1['subcat_id'],$level+1);
}
mysql_query("DELETE FROM categories WHERE cat_id=$rowr1[cat_id]", $link);
}
mysql_query("DELETE FROM subcategories WHERE cat_id=$cat_id", $link);
//mysql_free_result($qr1);
}
/*
function: zap_categories
abstract: delete entire set of categories.
inputs:
*/
function zap_categories() {
global $link;
$q=mysql_query("SELECT cat_id FROM categoryroots", $link);
while ($row=mysql_fetch_assoc($q)) {
delete_tree(1, $row['cat_id']);
}
mysql_query("DELETE FROM categoryroots", $link);
//mysql_free_result($q);
}
?>
<body>
<?php
//note - this code is untested.
display_categories("u.php?", true, true); //url,collapse,show_brackets,pad_string. not a good idea to collapse and not show brackets.
echo "<p> </p>";
display_categories("u.php?", false, true,"-");
echo "<p> </p>";
display_categories("u.php?", false, false); //also looks good in a menu.
?>
<form action="u.php" method="post">
<?php display_categories_select("category"); ?>
</form>
</body>
</html>