create cateogy nd sub category's tutorial?

[nuttycoder]

Hi I've been looking through google to try and find a tutorial that explains how to make category's and sub category's so far I haven't had any look.

 

Does anyone know of any good tutorials for this?

 

I've managed to make category's with articles relative to each category but it would be much better to be able to make sub category's.

 

Thanks in advance.

[Link]

I always used a database to store my categories.  This may seem a little over kill but it was something like:

 

id int(11)

parent_id int(11)

name varchar(50)

 

and then I just stored the categories and if they had a parent, I put them under it by use the parent_id approach.  Then you can recursively or with a loop pull out all parents.

 

Does that make sense/help?

[nuttycoder]

yeah it sort of does,

 

don't supose you could show an example could you?

 

Cheers

[Link]

Sure can!

 

 

MySQL Code:

-- 
-- Table structure for table `category`
-- 

CREATE TABLE `category` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `name` varchar(100) NOT NULL default '',
  `parent_id` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=68 ;

-- 
-- Dumping data for table `category`
-- 

INSERT INTO `category` VALUES (1, 'Party', 0);
INSERT INTO `category` VALUES (2, 'Causes', 0);
INSERT INTO `category` VALUES (3, 'Education', 0);
INSERT INTO `category` VALUES (4, 'Meetings', 0);
INSERT INTO `category` VALUES (5, 'Music/Arts', 0);
INSERT INTO `category` VALUES (6, 'Sports', 0);
INSERT INTO `category` VALUES (7, 'Trips', 0);
INSERT INTO `category` VALUES (8, 'Other', 0);
INSERT INTO `category` VALUES (9, 'Birthday Party', 1);
INSERT INTO `category` VALUES (10, 'Cocktail Party', 1);
INSERT INTO `category` VALUES (11, 'Club Party', 1);
INSERT INTO `category` VALUES (12, 'Fraternity/Sorority Party', 1);
INSERT INTO `category` VALUES (13, 'Barbecue', 1);
INSERT INTO `category` VALUES (14, 'Card Night', 1);
INSERT INTO `category` VALUES (15, 'Dinner Party', 1);
INSERT INTO `category` VALUES (16, 'Holiday Party', 1);
INSERT INTO `category` VALUES (17, 'Movie Night', 1);
INSERT INTO `category` VALUES (18, 'TV Night', 1);
INSERT INTO `category` VALUES (19, 'Drinking Games', 1);
INSERT INTO `category` VALUES (20, 'Bar Night', 1);
INSERT INTO `category` VALUES (21, 'LAN Party', 1);
INSERT INTO `category` VALUES (22, 'Mixer', 1);
INSERT INTO `category` VALUES (23, 'Slumber Party', 1);
INSERT INTO `category` VALUES (24, 'Benefit', 1);
INSERT INTO `category` VALUES (25, 'Goodbye Party', 1);
INSERT INTO `category` VALUES (26, 'House Party', 1);
INSERT INTO `category` VALUES (27, 'Reunion', 1);
INSERT INTO `category` VALUES (28, 'Concert', 5);
INSERT INTO `category` VALUES (29, 'Audition', 5);
INSERT INTO `category` VALUES (30, 'Exhibit', 5);
INSERT INTO `category` VALUES (31, 'Jam Session', 5);
INSERT INTO `category` VALUES (32, 'Listening Party', 5);
INSERT INTO `category` VALUES (33, 'Opening', 5);
INSERT INTO `category` VALUES (34, 'Performance', 5);
INSERT INTO `category` VALUES (35, 'Preview', 5);
INSERT INTO `category` VALUES (36, 'Recital', 5);
INSERT INTO `category` VALUES (37, 'Rehearsal', 5);
INSERT INTO `category` VALUES (38, 'Business Meeting', 4);
INSERT INTO `category` VALUES (39, 'Club Meeting', 4);
INSERT INTO `category` VALUES (40, 'Group Meeting', 4);
INSERT INTO `category` VALUES (41, 'Convention', 4);
INSERT INTO `category` VALUES (42, 'Dorm Meeting', 4);
INSERT INTO `category` VALUES (43, 'House Meeting', 4);
INSERT INTO `category` VALUES (44, 'Informational Meeting', 4);
INSERT INTO `category` VALUES (45, 'Study Group', 3);
INSERT INTO `category` VALUES (46, 'Class', 3);
INSERT INTO `category` VALUES (47, 'Lecture', 3);
INSERT INTO `category` VALUES (48, 'Office Hours', 3);
INSERT INTO `category` VALUES (49, 'Workshop', 3);
INSERT INTO `category` VALUES (50, 'Review Session', 3);
INSERT INTO `category` VALUES (51, 'Fundraiser', 2);
INSERT INTO `category` VALUES (52, 'Protest', 2);
INSERT INTO `category` VALUES (53, 'Rally', 2);
INSERT INTO `category` VALUES (54, 'Pep Rally', 6);
INSERT INTO `category` VALUES (55, 'Pick-Up', 6);
INSERT INTO `category` VALUES (56, 'Sporting Event', 6);
INSERT INTO `category` VALUES (57, 'Sports Practice', 6);
INSERT INTO `category` VALUES (58, 'Tournament', 6);
INSERT INTO `category` VALUES (59, 'Camping Trip', 7);
INSERT INTO `category` VALUES (60, 'Daytrip', 7);
INSERT INTO `category` VALUES (61, 'Ground Trip', 7);
INSERT INTO `category` VALUES (62, 'Group Trip', 7);
INSERT INTO `category` VALUES (63, 'Road Trip', 7);
INSERT INTO `category` VALUES (64, 'Carnival', ;
INSERT INTO `category` VALUES (65, 'Ceremony', ;
INSERT INTO `category` VALUES (66, 'Festival', ;
INSERT INTO `category` VALUES (67, 'Flea Market', ;

 

Now, if you notice, "Cocktail Party" has a parent_id of 1, and therefore is under the Party category.  So if you did something like:

 

$result = mysql_query("SELECT * FROM category WHERE name='Cocktail Party'");
$info = mysql_fetch_array($result);
$str = $info['name'];

while ($info['parent_id'] != 0) {
$info = mysql_fetch_array(mysql_query("SELECT * FROM category WHERE id='".$info['parent_id']."'"));
$str = $info['name']."=>".$str;
}

print "Category: ".$str;

 

If that doesn't work, there is probably any error somewhere as I just typed it up.  Using the while loop means you could have categories that even point to sub categories and it would pull them all:

 

i.e.  Sporting Event=>Scrimmage=>Soccer

 

Or any combination you want.  An interface to edit this uses the same logic as the above code.  You just simply think of a tree and addthing things below, always storing the parent_id.

[nuttycoder]

Thanks very much this looks great!

 

just 1 thing I don't understand a root category has a parent_Id of 0 then sub category's have a parent_id of something higher which makes perfect sense.

 

but what I don't get how is the higher numbers relate to a certain category in my head it looks like any sub-category that has a parent_id of 1 would relate to all category's with a parent_id of 0

 

which am sure is not the case just not sure how the relations work

 

could you explain this if possible?

 

Thanks for your help

[Link]

That's the while ($info['parent_id'] != 0)

 

In the logic and algorithms just do a simple check to make sure that the parent_id is over 1.  Then anything with a parent_id of 0 is just a main category that is sub to nothing.

 

Does that answer you question?

[nuttycoder]

what I mean is if you have a sub-category with a parent_id of 2 and another sub-category with a parent_id of 3,

 

with 2 category with a parent_id of 0

 

how does you know which category each sub-category belong to?

 

sorry if these seems obvious i just don't see it  :-\

[premiso]

http://www.sitepoint.com/article/hierarchical-data-database

 

Good article on categories.

[nuttycoder]

Just been reading the article you suggested and I can say most of it went right over my head  ???

 

I'll try and work with the example Link suggested looks fairly easy to understand and simple to use just need to know how a the sub category's relate to their root category's.

[Barand]

what I mean is if you have a sub-category with a parent_id of 2 and another sub-category with a parent_id of 3,

 

with 2 category with a parent_id of 0

 

how does you know which category each sub-category belong to?

 

sorry if these seems obvious i just don't see it  :-\

 

A category with parent_id of 0 has NO parent - ie a root category.

 

A subcat with a parent_id of X belongs to category X

 

A subsubcat with parent_id of Y belongs to subcat Y

[nuttycoder]

A category with parent_id of 0 has NO parent - ie a root category.

 

right I get that part but say I have 2 category's

 

root1 & root 2 both have parent_id of 0 to make root category's

 

then each root category has a subcategory's

 

sub1 & sub2

 

example : root1 > sub1

 

root2 > sub2

 

also say I wanted sub2 to belong to root2 and sub1 to belong to root1

 

if both sub1 & sub2 had a parent_id of 1 am not sure which category it would belong to or would it belong to all category's with a parent_id of 0?

 

 

[Barand]

if both sub1 & sub2 had a parent_id of 1 am not sure which category it would belong to or would it belong to all category's with a parent_id of 0?

 

 

 

They would both belong to the category whose id is 1

[hcdarkmage]

ust 1 thing I don't understand a root category has a parent_Id of 0 then sub category's have a parent_id of something higher which makes perfect sense.

 

but what I don't get how is the higher numbers relate to a certain category in my head it looks like any sub-category that has a parent_id of 1 would relate to all category's with a parent_id of 0

 

which am sure is not the case just not sure how the relations work

 

Let me have a crack at it.  In Link's code you see that everything has two numbers.  The first number is the assignment number.  That means that 1-8 are parent categories because the second number is 0.  9-27 belong into the parent category of 'Party' so you place the second number as 1.  In the example, if you wanted to expand on the 'birthday' subcategory, your first number would be next in sequence, then the title of the new category and last the number that references the subcategory, in this case 9. 

You just simply think of a tree and add things below, always storing the parent_id.

 

Does this clear it up for you?

[nuttycoder]

Thanks very much I think I finally understand it,

 

So with links example if I wanted to create a sub-category of the sub category tv night then the first number would be designated automatically then give the category a name say comedy then the next number would be which parent it relates to so it would be 18 like:

 

68, 'comedy',18

 

then paths would be party > tv night > comedy

 

this correct?

[Barand]

If you run this with Link's data (plus "68,Comedy,18" and "69,Drama,18" it will list the dat and the tree structure

 

<?php
mysql_connect('localhost') ;
mysql_select_db('test3') ;

function list_subcats ($parent, $level=0)
{
    $sql = "SELECT id, name, parent_id
            FROM category
            WHERE parent_id = $parent
            ORDER BY id";
    $res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");
    
    while (list($id, $nm, $pid) = mysql_fetch_row($res))
    {
        $indent = str_repeat('---',$level);
        echo "<tr>
                <td>($id, $nm, $pid)</td>
                <td>$indent $nm</td>
              </tr>";
        list_subcats ($id, $level+1);                   // list subcats of this category
    }
}

echo '<table border="1">';
list_subcats(0);
echo '</table>';
?>

[hcdarkmage]

then paths would be party > tv night > comedy

 

this correct?

 

It looks like you are getting the hang of it now.  So my answer would have to be yes.

[nuttycoder]

Thanks very much I created a db and input some category's and made some sub category's with the example from link and using the code you provided I can see clearly which sub's belong to a root category in fact I went 5 cats deep to see if I understaood how it was working/relating and I do finally.

 

I can't thank you all enough for helping me with this one, I've been trying to learn it for ages just could never grasp it.

 

For those ppl who use this method do you manually assign parent_ids to your cats or use php to do it on the fly?

[Link]

I made a script (or, rather, will make) to do it for me.  Like, just make an interface to do it for you.