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]."";

}

?>