Home > Archive > PHP Language > January 2006 > unlimeied cattegories,sub categories
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
unlimeied cattegories,sub categories
|
|
| Beshoo 2006-01-10, 7:01 pm |
| Heeeeey:
I have a problem in making photo gallery so that I can add unlimeied
cattegories,sub categories ??????
I Tooke a look at "4 IMAGE scrtipt" the use ( id and P_id ) idea
BUT, I cant get it ,so plz if you havr any wey out TEL ME
THAAAAAANX in advance
| |
| Jim Michaels 2006-01-16, 7:56 am |
| CREATE TABLE `dbo`.`categories` (
`cat_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`category` VARCHAR(100) NOT NULL DEFAULT '',
PRIMARY KEY(`cat_id`)
);
CREATE TABLE `dbo`.`subcategories` (
`sub_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`cat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
`subcat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY(`sub_id`)
);
I think you'll need a recursive function to traverse the tree. if you need
it, I can write one.
"Beshoo" <basheermoro@gmail.com> wrote in message
news:1136923318.502352.55300@z14g2000cwz.googlegroups.com...
> Heeeeey:
> I have a problem in making photo gallery so that I can add unlimeied
> cattegories,sub categories ??????
> I Tooke a look at "4 IMAGE scrtipt" the use ( id and P_id ) idea
> BUT, I cant get it ,so plz if you havr any wey out TEL ME
>
> THAAAAAANX in advance
>
| |
| Beshoo 2006-01-18, 7:56 am |
| Thank you very much Jim Michaels;
but i encounted of this problem you have mentiond " traverse the tree?"
if you have the code plz write it 4 me !!!! thaaaaaaaanx!!!!
| |
| Jim Michaels 2006-01-18, 9:56 pm |
| 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`)
);
CREATE TABLE `dbo`.`subcategories` (
`sub_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ignore',
`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 ,
PRIMARY KEY(`sub_id`)
);
CREATE TABLE `dbo`.`categoryroots` {
`root_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ignore',
`cat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'copy of cat_id from
categories table',
PRIMARY KEY(`root_id`)
);
c
f-b
g |
h |
i-c-a
j |
k |
l-d
m
----n
categoryroots
cat_id
-----------------
1
14
categories
cat_id,category
----------------
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
subcategories
cat_id,subcat_id
-------------------------
1,2
1,3
1,4
2,5
2,6
2,7
3,8
3,9
3,10
4,11
4,12
4,13
the reason for cat_grp is you need some way of finding the root(s) of the
category tree. for that you do a
and grab the first record and send it to traverse_tree. that's if you are
looking for a tree with a single root. you're probably not, so use
traverse_tree_grp().
<?php
//display tree-----------------------------------------------
function traverse_tree($level=1,$cat_id) {
global $link;
$qr2=mysql_query("SELECT c.categories,sc.subcat_id FROM categories AS
c,subcategories AS sc WHERE c.cat_id=$cat_id AND sc.cat_id=$cat_id", $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 . $rowr1['c.categories'] . "<br>\n";
traverse_tree($level+1,$rowr2['sc.subcat_id']);
}
echo "$pad\]<br>\n"; //end of category set
}
function display_categories() {
global $link
$q=mysql_query("SELECT cat_id FROM categoryroots", $link);
while ($row=mysql_fetch_array($q)) {
traverse_tree(1, $row['cat_id']);
}
}
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 dategoryroots", $link);
}
?>
"Beshoo" <basheermoro@gmail.com> wrote in message
news:1137578335.749473.190250@o13g2000cwo.googlegroups.com...
> Thank you very much Jim Michaels;
>
> but i encounted of this problem you have mentiond " traverse the tree?"
>
> if you have the code plz write it 4 me !!!! thaaaaaaaanx!!!!
>
| |
| Jim Michaels 2006-01-18, 9:56 pm |
| with the code I just submitted (and the changed tables), you will probably
want to display your categories in a different form. this gets slightly
tricky navigating through the recursive routine. for instance, if you just
want to display a <select></select> tag, then the traverse_tree() function
should have an extra argument, where the current category parent (the
current node) is passed to the next call, and also used in the echo
routines, maybe something like echo "<option
value=$row[cat_id]>$parent:$row[category]</option>";
I didn't quite have the code. I rewrote some code I wrote for a forum.
If you are not familiar with recursion, it is a function which calls itself.
it's often used in solving certain sticky problems like the towers of hanoi,
the 8 queens problem, and traversing tree-like structures. In a tree, you
recurse to travel down every trunk of the tree. it makes a difference where
you choose to put your display code (before or after the recurse call).
in the delete_tree(), I chose to make sure I had travelled down the entire
tree trunks and visited every node (first) before deleting the leaves rather
than looping off the trunk first and leaving a bunch of stranded data and
having no way to clean it up. when the call is finished, each trunk becomes
"leaves" to the parent that called it. and the delete happens again as an
afterthought. and so on until we rach root, where the root is deleted as an
afterthought. do you get the idea behind recursion?
the traverse_tree() routine puts square brackets around groups/levels of
items.
I haven't tested the code, but it should work.
"Beshoo" <basheermoro@gmail.com> wrote in message
news:1137578335.749473.190250@o13g2000cwo.googlegroups.com...
> Thank you very much Jim Michaels;
>
> but i encounted of this problem you have mentiond " traverse the tree?"
>
> if you have the code plz write it 4 me !!!! thaaaaaaaanx!!!!
>
| |
| Beshoo 2006-01-19, 7:55 am |
| Thank you very much
Thank you very much
Thank you very much
Thank you very much
I HAVE SOLVED THE PROBLEM.
Your friend Basheer
| |
| Jim Michaels 2006-01-19, 6:58 pm |
| I forgot to mention you should create indexes on these tables for speed.
CREATE UNIQUE INDEX ix_ccat_id ON `dbo`.`categories`(cat_id);
CREATE UNIQUE INDEX ix_ccat ON `dbo`.`categories`(categories);
CREATE UNIQUE INDEX ix_crcat_id ON `dbo`.`categoryroots`(cat_id);
CREATE UNIQUE INDEX ix_sccat_id ON `dbo`.`subcategories`(cat_id);
CREATE UNIQUE INDEX ix_scsubcat_id ON `dbo`.`subcategories`(subcat_id);
I'm no expert on indexes. but all of these fields are used except
categories, which I suppose you'll want to put an ORDER BY on in
traverse_tree(), so I made an index for it anyway just in case.
I had a bug in traverse_tree(). the select statement should be
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
you can append the ORDER BY c.category if you want.
"Jim Michaels" <jmichae3@yahoo.com> wrote in message
news:OZCdncoTat6oclPeRVn-hg@comcast.com...
> 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`)
> );
>
> CREATE TABLE `dbo`.`subcategories` (
> `sub_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ignore',
> `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 ,
> PRIMARY KEY(`sub_id`)
> );
>
> CREATE TABLE `dbo`.`categoryroots` {
> `root_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ignore',
> `cat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'copy of cat_id from
> categories table',
> PRIMARY KEY(`root_id`)
> );
>
> c
> f-b
> g |
> h |
> i-c-a
> j |
> k |
> l-d
> m
> ----n
>
> categoryroots
> cat_id
> -----------------
> 1
> 14
>
> categories
> cat_id,category
> ----------------
> 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
>
> subcategories
> cat_id,subcat_id
> -------------------------
> 1,2
> 1,3
> 1,4
> 2,5
> 2,6
> 2,7
> 3,8
> 3,9
> 3,10
> 4,11
> 4,12
> 4,13
>
> the reason for cat_grp is you need some way of finding the root(s) of the
> category tree. for that you do a
> and grab the first record and send it to traverse_tree. that's if you are
> looking for a tree with a single root. you're probably not, so use
> traverse_tree_grp().
>
>
> <?php
>
> //display tree-----------------------------------------------
> function traverse_tree($level=1,$cat_id) {
> global $link;
> $qr2=mysql_query("SELECT c.categories,sc.subcat_id FROM categories AS
> c,subcategories AS sc WHERE c.cat_id=$cat_id AND sc.cat_id=$cat_id",
> $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 . $rowr1['c.categories'] . "<br>\n";
> traverse_tree($level+1,$rowr2['sc.subcat_id']);
> }
> echo "$pad\]<br>\n"; //end of category set
> }
>
> function display_categories() {
> global $link
> $q=mysql_query("SELECT cat_id FROM categoryroots", $link);
> while ($row=mysql_fetch_array($q)) {
> traverse_tree(1, $row['cat_id']);
> }
> }
>
>
> 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 dategoryroots", $link);
> }
>
>
> ?>
>
> "Beshoo" <basheermoro@gmail.com> wrote in message
> news:1137578335.749473.190250@o13g2000cwo.googlegroups.com...
>
>
| |
| Jim Michaels 2006-01-19, 6:58 pm |
| alas, one more bug fix.
I noticed that I put in c.categories in multiple places instead of
c.category. plus, I added 2 new functions for <select> tags. I also put in
the ORDER BY.
I took out $level=1 from the arguments. it's was a hint on how to use the
function but I am not sure it's syntactically correct. you could get some
bad results if you were to put in only 2 parameters into the function at
call time. the problem with a gigantic <select></select> is people have to
go through it.
//start with $level=1
function traverse_tree($level,$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
}
//start with $level=1
function traverse_tree_select($level,$cat_id,$tex
t) {
//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) {
global $link;
echo "<select size=\"1\" 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 dategoryroots", $link);
}
"Jim Michaels" <jmichae3@yahoo.com> wrote in message
news:OZCdncoTat6oclPeRVn-hg@comcast.com...
> 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`)
> );
>
> CREATE TABLE `dbo`.`subcategories` (
> `sub_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ignore',
> `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 ,
> PRIMARY KEY(`sub_id`)
> );
>
> CREATE TABLE `dbo`.`categoryroots` {
> `root_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ignore',
> `cat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'copy of cat_id from
> categories table',
> PRIMARY KEY(`root_id`)
> );
>
> c
> f-b
> g |
> h |
> i-c-a
> j |
> k |
> l-d
> m
> ----n
>
> categoryroots
> cat_id
> -----------------
> 1
> 14
>
> categories
> cat_id,category
> ----------------
> 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
>
> subcategories
> cat_id,subcat_id
> -------------------------
> 1,2
> 1,3
> 1,4
> 2,5
> 2,6
> 2,7
> 3,8
> 3,9
> 3,10
> 4,11
> 4,12
> 4,13
>
> the reason for cat_grp is you need some way of finding the root(s) of the
> category tree. for that you do a
> and grab the first record and send it to traverse_tree. that's if you are
> looking for a tree with a single root. you're probably not, so use
> traverse_tree_grp().
>
>
> <?php
>
> //display tree-----------------------------------------------
> function traverse_tree($level=1,$cat_id) {
> global $link;
> $qr2=mysql_query("SELECT c.categories,sc.subcat_id FROM categories AS
> c,subcategories AS sc WHERE c.cat_id=$cat_id AND sc.cat_id=$cat_id",
> $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 . $rowr1['c.categories'] . "<br>\n";
> traverse_tree($level+1,$rowr2['sc.subcat_id']);
> }
> echo "$pad\]<br>\n"; //end of category set
> }
>
> function display_categories() {
> global $link
> $q=mysql_query("SELECT cat_id FROM categoryroots", $link);
> while ($row=mysql_fetch_array($q)) {
> traverse_tree(1, $row['cat_id']);
> }
> }
>
>
> 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 dategoryroots", $link);
> }
>
>
> ?>
>
> "Beshoo" <basheermoro@gmail.com> wrote in message
> news:1137578335.749473.190250@o13g2000cwo.googlegroups.com...
>
>
| |
| Jim Michaels 2006-01-19, 6:58 pm |
| alas, one more bug fix.
I noticed that I put in c.categories in multiple places instead of
c.category. plus, I added 2 new functions for <select> tags. I also put in
the ORDER BY.
I took out $level=1 from the arguments. it's was a hint on how to use the
function but I am not sure it's syntactically correct. you could get some
bad results if you were to put in only 2 parameters into the function at
call time. the problem with a gigantic <select></select> is people have to
go through it.
also, the INDEX creation statements were wrong. it's your choice whether or
not you want your categories to be UNIQUE or not.
ALTER TABLE `dbo`.`categories` CREATE UNIQUE INDEX `ix_ccat_id`(`cat_id`);
ALTER TABLE `dbo`.`categories` CREATE INDEX `ix_ccat`(`category`);
ALTER TABLE `dbo`.`categoryroots` CREATE UNIQUE INDEX
`ix_crcat_id`(`cat_id`);
ALTER TABLE `dbo`.`subcategories` CREATE INDEX `ix_sccat_id`(`cat_id`);
ALTER TABLE `dbo`.`subcategories` CREATE INDEX
`ix_scsubcat_id`(`subcat_id`);
//start with $level=1
function traverse_tree($level,$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
}
//start with $level=1
function traverse_tree_select($level,$cat_id,$tex
t) {
//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) {
global $link;
echo "<select size=\"1\" 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 dategoryroots", $link);
}
"Jim Michaels" <jmichae3@yahoo.com> wrote in message
news:OZCdncoTat6oclPeRVn-hg@comcast.com...
> 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`)
> );
>
> CREATE TABLE `dbo`.`subcategories` (
> `sub_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ignore',
> `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 ,
> PRIMARY KEY(`sub_id`)
> );
>
> CREATE TABLE `dbo`.`categoryroots` {
> `root_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ignore',
> `cat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'copy of cat_id from
> categories table',
> PRIMARY KEY(`root_id`)
> );
>
> c
> f-b
> g |
> h |
> i-c-a
> j |
> k |
> l-d
> m
> ----n
>
> categoryroots
> cat_id
> -----------------
> 1
> 14
>
> categories
> cat_id,category
> ----------------
> 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
>
> subcategories
> cat_id,subcat_id
> -------------------------
> 1,2
> 1,3
> 1,4
> 2,5
> 2,6
> 2,7
> 3,8
> 3,9
> 3,10
> 4,11
> 4,12
> 4,13
>
> the reason for cat_grp is you need some way of finding the root(s) of the
> category tree. for that you do a
> and grab the first record and send it to traverse_tree. that's if you are
> looking for a tree with a single root. you're probably not, so use
> traverse_tree_grp().
>
>
> <?php
>
> //display tree-----------------------------------------------
> function traverse_tree($level=1,$cat_id) {
> global $link;
> $qr2=mysql_query("SELECT c.categories,sc.subcat_id FROM categories AS
> c,subcategories AS sc WHERE c.cat_id=$cat_id AND sc.cat_id=$cat_id",
> $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 . $rowr1['c.categories'] . "<br>\n";
> traverse_tree($level+1,$rowr2['sc.subcat_id']);
> }
> echo "$pad\]<br>\n"; //end of category set
> }
>
> function display_categories() {
> global $link
> $q=mysql_query("SELECT cat_id FROM categoryroots", $link);
> while ($row=mysql_fetch_array($q)) {
> traverse_tree(1, $row['cat_id']);
> }
> }
>
>
> 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 dategoryroots", $link);
> }
>
>
> ?>
>
> "Beshoo" <basheermoro@gmail.com> wrote in message
> news:1137578335.749473.190250@o13g2000cwo.googlegroups.com...
>
>
| |
| Jim Michaels 2006-01-19, 6:58 pm |
| alas, one more bug fix.
I noticed that I put in c.categories in multiple places instead of
c.category. plus, I added 2 new functions for <select> tags. I also put in
the ORDER BY.
I took out $level=1 from the arguments. it's was a hint on how to use the
function but I am not sure it's syntactically correct. you could get some
bad results if you were to put in only 2 parameters into the function at
call time. the problem with a gigantic <select></select> is people have to
go through it.
also, the INDEX creation statements were wrong. it's your choice whether or
not you want your categories to be UNIQUE or not.
ALTER TABLE `dbo`.`categories` CREATE UNIQUE INDEX `ix_ccat_id`(`cat_id`);
ALTER TABLE `dbo`.`categories` CREATE INDEX `ix_ccat`(`category`);
ALTER TABLE `dbo`.`categoryroots` CREATE UNIQUE INDEX
`ix_crcat_id`(`cat_id`);
ALTER TABLE `dbo`.`subcategories` CREATE INDEX `ix_sccat_id`(`cat_id`);
ALTER TABLE `dbo`.`subcategories` CREATE INDEX
`ix_scsubcat_id`(`subcat_id`);
//start with $level=1
function traverse_tree($level,$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
}
//start with $level=1
function traverse_tree_select($level,$cat_id,$tex
t) {
//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) {
global $link;
echo "<select size=\"1\" 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 dategoryroots", $link);
}
"Jim Michaels" <jmichae3@yahoo.com> wrote in message
news:OZCdncoTat6oclPeRVn-hg@comcast.com...
> 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`)
> );
>
> CREATE TABLE `dbo`.`subcategories` (
> `sub_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ignore',
> `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 ,
> PRIMARY KEY(`sub_id`)
> );
>
> CREATE TABLE `dbo`.`categoryroots` {
> `root_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ignore',
> `cat_id` INTEGER UNSIGNED NOT NULL DEFAULT 0 COMMENT 'copy of cat_id from
> categories table',
> PRIMARY KEY(`root_id`)
> );
>
> c
> f-b
> g |
> h |
> i-c-a
> j |
> k |
> l-d
> m
> ----n
>
> categoryroots
> cat_id
> -----------------
> 1
> 14
>
> categories
> cat_id,category
> ----------------
> 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
>
> subcategories
> cat_id,subcat_id
> -------------------------
> 1,2
> 1,3
> 1,4
> 2,5
> 2,6
> 2,7
> 3,8
> 3,9
> 3,10
> 4,11
> 4,12
> 4,13
>
> the reason for cat_grp is you need some way of finding the root(s) of the
> category tree. for that you do a
> and grab the first record and send it to traverse_tree. that's if you are
> looking for a tree with a single root. you're probably not, so use
> traverse_tree_grp().
>
>
> <?php
>
> //display tree-----------------------------------------------
> function traverse_tree($level=1,$cat_id) {
> global $link;
> $qr2=mysql_query("SELECT c.categories,sc.subcat_id FROM categories AS
> c,subcategories AS sc WHERE c.cat_id=$cat_id AND sc.cat_id=$cat_id",
> $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 . $rowr1['c.categories'] . "<br>\n";
> traverse_tree($level+1,$rowr2['sc.subcat_id']);
> }
> echo "$pad\]<br>\n"; //end of category set
> }
>
> function display_categories() {
> global $link
> $q=mysql_query("SELECT cat_id FROM categoryroots", $link);
> while ($row=mysql_fetch_array($q)) {
> traverse_tree(1, $row['cat_id']);
> }
> }
>
>
> 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 dategoryroots", $link);
> }
>
>
> ?>
>
> "Beshoo" <basheermoro@gmail.com> wrote in message
> news:1137578335.749473.190250@o13g2000cwo.googlegroups.com...
>
>
|
|
|
|
|