|
|
Posted by Jim Michaels on 02/23/06 10:11
"TintedS" <ausmanov@gmail.com> wrote in message
news:1137290126.401821.246300@z14g2000cwz.googlegroups.com...
> Hi.
>
> my db has
>
> categories(CatID,ParentID,CatName)
> items(ItemID,CatID,ItemName)
>
> I need to retrieve particular items (Ex: ItemID=1,5,19,36). The problem
> is I cannot make it that way that the item is under necessary
that sentence in english?
> categories and subcategories.
>
> Ex:
> ParentID=0
> ParentID=4
> ParentID=10
> ...
> ItemID=1
>
> Thanx in advance
>
perhaps you should try recursion.
a tree_roots table, a subcategories table, and a categories table to hold
strings is what I did.
if a root has no subcategories, nothing goes in the subcategories table.
this code is untested.
<?php
?><!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>Untitled Document</title>
</head>
<!--
CREATE TABLE `jmichae3`.`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 `jmichae3`.`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 'copy of cat_id from
categories table',
`subcat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'cat_id of
subcategory',
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 `jmichae3`.`categoryroots` (
`cat_id` int(10) unsigned NOT NULL default '0' COMMENT 'copy of 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;
-- ALTER TABLE `jmichae3`.`categories` CREATE UNIQUE INDEX
`ix_ccat_id`(`cat_id`);
-- ALTER TABLE `jmichae3`.`categories` CREATE INDEX `ix_ccat`(`category`);
-- ALTER TABLE `jmichae3`.`categoryroots` CREATE UNIQUE INDEX
`ix_crcat_id`(`cat_id`);
-- ALTER TABLE `jmichae3`.`subcategories` CREATE INDEX
`ix_sccat_id`(`cat_id`);
-- ALTER TABLE `jmichae3`.`subcategories` CREATE INDEX
`ix_scsubcat_id`(`subcat_id`);
/*
c
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'; //sets $link
//-----------this code has not been debugged as of
1/30/2006-------------------
function traverse_tree($level=1,$cat_id) {
global $link;
$qr2=mysql_query("SELECT c.category,sc.subcat_id FROM categories AS
c,subcategories AS sc WHERE c.cat_id=$cat_id AND sc.cat_id=$cat_id ORDER BY
c.category", $link);
//make indenting for categories
$pad=""; for ($i=1; $i<=$level; $i++) {$pad .= " ";}
echo "$pad\[<br>\n"; //start of category set
while ($rowr2=mysql_fetch_array($qr2)) {
echo $pad . $rowr2['c.category'] . "<br>\n";
traverse_tree($level+1,$rowr2['sc.subcat_id']);
}
echo "$pad\]<br>\n"; //end of category set
}
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 c.cat_id,c.category,sc.subcat_id FROM categories
AS c,subcategories AS sc WHERE c.cat_id=$cat_id AND sc.cat_id=$cat_id ORDER
BY c.category", $link);
if ($text) {$text .= ":";} //category separator
while ($rowr2=mysql_fetch_array($qr2)) {
echo "<option value=$rowr2[c.cat_id]>$text$rowr2[c.category]</option>\n";
traverse_tree($level+1,$rowr2['sc.subcat_id'], $text .
$rowr2['c.category']);
}
}
function display_categories() {
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);
while ($row=mysql_fetch_array($q)) {
traverse_tree(1, $row['cr.cat_id']);
}
}
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_array($q)) {
traverse_tree_select(1, $row['cr.cat_id'], "");
}
echo "</select>\n";
}
function delete_tree($cat_id) {
global $link;
$qr1=mysql_query("SELECT * FROM subcategories WHERE cat_id=$cat_id",
$link);
while ($rowr1=mysql_fetch_array($qr1)) {
if ($rowr1['subcat_id']) { //not 0?
delete_tree($rowr1['subcat_id']);
}
mysql_query("DELETE FROM categories WHERE cat_id=$rowr1[cat_id]", $link);
}
mysql_query("DELETE FROM subcategories WHERE cat_id=$cat_id", $link);
}
function zap_categories() {
global $link;
$q=mysql_query("SELECT cat_id FROM categoryroots", $link);
while ($row=mysql_fetch_array($q)) {
delete_tree(1, $row['cat_id']);
}
mysql_query("DELETE FROM categoryroots", $link);
}
?>
<body>
<?php
//note - this code is untested.
display_categories();
display_categories_select("category");
?>
</body>
</html>
[Back to original message]
|