Creating Cateories With Unlimited Sub-categories

This tutorial will show you how to create a script so that you can have multiple categories with unlimited sub-categories in each. They can be as deep as you want. This is very useful for organising data/items. A prime example of this is a link directory which has multiple categories.

Ok first of all I will just briefly explain how this will work - the logic behind it. I will then move on to writing the actual script which will manage the categories and put specific data/information into its category.
The Logic Behind Categories
If youve got a fairly decent amount of experience with the php coding language then you more than likely know that it is a very logical language. Because of this you have to try to understand how a script runs in a logical manner. So here is basically how the categories, sub-categories and data is arranged.

Say for example there is one parent (top/main/root) category which has got a catid of 1. Then there is another category which has got its own catid of 2. The second category however has got an extra value which is a parentid. The value of this is 1. This means that the scond category belongs to whichever category has got a catid of 1.

Thats basically it - It may seem a bit confusing but it should all become a lot clearer as we go through the code itself... So lets begin...
The Database

In this tutorial we will be using a mysql database to store all of the category and item information.

If you havent already got a mysql database available then you will need to create one. If you do not know how either take a look at some of our other tutorials or contact your webhost admin.

Once youve got a database sorted you will need to create the tables. There are two. Create them by simply copying and pasting the following SQL queries into phpMyAdmin

CREATE TABLE `categories` (

`catid` INT( 11 ) NOT NULL AUTO_INCREMENT ,

`parentid` INT( 11 ) NOT NULL ,

`name` VARCHAR( 255 ) NOT NULL ,

`description` TEXT NOT NULL ,

PRIMARY KEY ( `catid` )

);

The above will create the table to hold the categories. It has got the following fields:

catid

parentid

name

description

Now for the item table:

CREATE TABLE `items` (

`itemid` INT( 11 ) NOT NULL AUTO_INCREMENT ,

`itemparentid` INT( 11 ) NOT NULL ,

`firstname` VARCHAR( 255 ) NOT NULL ,

`lastname` VARCHAR( 255 ) NOT NULL ,

PRIMARY KEY ( `itemid` )

);

The above will create a table called items with the following fields:

itemid

itemparentid

firstname

lastname

Ok thats the database sorted. Now onto the code for managing the categories...

Managaing & Organising Categories

The first step we need to do is to connect to the database. This can be done by using the following code:

mysql_connect('localhost','username','password');

mysql_select_db('databasename');

?>

You will need to edit the above code and replace the values with your database host, username, password and database name.

Once done the next step is to show all of the main or root categories. Firstly however we need to insert some categories into our database so that we actually have something to output. Run this SQL query in phpMyAdmin just like before

INSERT INTO `categories` ( `catid` , `parentid` , `name` , `description` )

VALUES (

'', '0', 'Main Category', 'This is the description for the main category'

);

You can also create other root categories. To do this simply use the above query and just change the name and description. For it to be a root category however it must have a parentid of 0.

Now for the script itself...

$query = "SELECT * FROM 'categories' WHERE 'parentid' = 0 ORDER BY 'catid' ASC";

$result = mysql_query($query);

while($cat=mysql_fetch_array($result)){

echo "$cat[catname]";

}

?>

The above will simply output all of the parent categories which will have a parentid of 0. Save the above as main.php

Working With Sub-Categories

The next step now is to be able to display the subcategories of a particular category when clicked. This is done by using the following code:

$query = "SELECT * FROM 'categories' WHERE 'parentid' = $c ORDER BY 'catid' ASC";

$result = mysql_query($query);

while($cat=mysql_fetch_array($result)){

echo "$cat[catname]";

}

?>

Save the above as sub.php

Basically what happens is main.php will show you all of the main categories which are linked to sub.php. They also pass the value of c over from main.php to sub.php - This value is the catid which you want to get the subcategories for. So when a link is clicked it will go to sub.php and will display all of the categories which have got a parentid of whatever the clicked catid was.

Ouputting The Correct Items

Now the whole point of categories is to organise stuff right? Well in this example I have just used peoples names as items which are to be organised into categories. You however can of course use something else.

What the script now needs to do is get the items from the database which belong to the selected category.
Here is the code:

$query = "SELECT * FROM `items` WHERE `itemparentid` = $c";

$result = mysql_query($query);

while($item=mysql_fetch_array($result)){

echo $item[firstname]."
".$item[lastname]."";

}

?>

The above code basically selects all items which belong to the selected category whos value is held within the variable $c. It then outputs these to the browser.

The Final Script

Thats pretty much it. We have covered the basics for creating categories with unlimited sub-categories. You should play around a bit with it and customize it to suit your needs. Here is the entire scripts files as a whole:

Firstly index.php

// Connect to database

mysql_connect('localhost','username','password');

mysql_select_db('databasename');

// Output main categories

$query = "SELECT * FROM 'categories' WHERE 'parentid' = 0 ORDER BY 'catid' ASC";

$result = mysql_query($query);

while($cat=mysql_fetch_array($result)){

echo "$cat[catname]";

}

?>

And now sub.php

// Connect to database

mysql_connect('localhost','username','password');

mysql_select_db('databasename');

// Ouput sub-categories

$query = "SELECT * FROM 'categories' WHERE 'parentid' = $c ORDER BY 'catid' ASC";

$result = mysql_query($query);

while($cat=mysql_fetch_array($result)){

echo "$cat[catname]";

}

// Output Category Items

$query = "SELECT * FROM `items` WHERE `itemparentid` = $c";

$result = mysql_query($query);

while($item=mysql_fetch_array($result)){

echo $item[firstname]."
".$item[lastname]."";

}

?>

And thats it. Thats all you need. Good luck and hope it helped!


Edited August 28, 2005 by BuffaloHELP (see edit history)

ok copied this from http://forums.xisto.com/no_longer_exists/ and you didnt even bother putting the code in the code tags? sheesh.

Unlimited CategoriesCreating Cateories With Unlimited Sub-categoriesI've found it surprising that pretty much every example of categories,sub-categories, unlimited nesting of categories is using recurrsion inpretty much all the wrong places.Software engineering design dictates that one look at not only the task at hand but where its being performed.Most category management lay withing an administrative area of a website be that a eCommerce store, directory etc.Yet...Literally every recursive example I see wants to deal with output ofthe category structure. This is exactly the opposite of what is realityin most enterprise type environments.Instead... The goal is to use recursion to order categories when changes are made in the BACKEND.A category DB structure for this looks like:CREATE TABLE `categories` (`catid` INT( 11 ) NOT NULL AUTO_INCREMENT ,'catorder' INT( 11 ) NOT NULL,'suborder' INT( 11 ) NOT NULL,'level' INT( 11 ) NOT NULL,`parentid` INT( 11 ) NOT NULL ,`name` VARCHAR( 255 ) NOT NULL ,`description` TEXT NOT NULL ,PRIMARY KEY ( `catid` ));Theykeyword here is to KEEP the categories ORDERED by usage of the'catorder', the 'level' integer stores the nesting level and finally'suborder' contains the subordinate ordering of the categories, aka aroot or level 0 category such as "cooking"will have a ordering of say '1', a sub-category of it like 'cookierecipes' would has a subordering of '1', and 'drink recipes' asubordering of '2'... This provides the functionality to order, thenext level 0 category might be 'grills' and its ordering would be 2.This structure allows you to order all levels of categories, I can move"drinks" above 'cookie recipes'. This is the LOGICAL ordering.Thecatorder or category ordering is an integer list of the DISPLAYPOSITIONS. Thus when you do a 'SELECT * FROM categories ORDER BYcatoder' you are handed a recordset that is READY TO be directly parsedthrough for display FAST.Recursion is used in the BACKENDadministration when categories are added, moved, deleted to REORDER therecordset so as it can be output on the front end with NO NEED forrecursion which is 1. Slow, 2. A memory sucker and 3. Inefficient(especially in PHP) when considering how many users might be accessinga categorical menu arrangement in the front end. You instead limitthe recursion being done in the administrative backend using it toessentially build a structure that can be rapidly output with verylittle overhead.Sites that endure extremely high loads onapplications servers that rely on a heirarchial category mechanism inthe administrative backend usually go a step farther than this.Sincethe categories again are really only altered in the admin backend theywill as category additions, deletions, re-organizations occur again userecursive code to essentially rebuild the category structure as notedabove, but in addition to it being store in the database they willbuild out the front end menu HTML to be cached so essentially noprocessing is necessary. When changed in the backend, the DB and cacheare refreshed accordingly.It limits the recursive andprocessing intensive code to the BACKEND perhaps to a handfull ofpeople who do the admin work. On the sites end users side of things,they get a snappier performance, better system loading, etc.Essentially the database record breaks down all aspects, Logical ordering so the site administrator(s) can see, "This comes after that",physical ordering maintained by the catorder field... 1, 2, 3, 4, 5...And the 'level' field letting us know our indentation characterstics.It offloads the category "building"to the backend when categories are altered and provides max performancein the front end where 10,000 people might be surfing pages with thatcategory heirarchy on each and every page. 

I am getting this error:

mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /index.php on line 15

can you help me fix it please?

Thank you.

I am the same person bumping this thread...I am sure 6 months later should give enough time for a reply. No?