category management

[omerbutt]

hi can any one guide me how to control the categories in php as i have to make a vertical menu in php to show all the categories and subcategories inline to their parent
for eg. i have 3 main categories
Ladies
Men
Kid
then in each of the above categories i have to define the subcategories i.e.
tops
hoods
tees
sweat shirts
zipper
i have made the 2 tables in the databases which are as follows
ksl_categories
ksl_categories_ description

ksl_categories
Field Type
cat_id int(11)
cat_image varchar(64)
parent_id int(11)
sort_order int(3)
date_added int(11)
last_modified int(11)

ksl_categories_ description
Field Type
cat_id int(11)
lang_id int(11)
cat_name varchar(32)

can any one guide me in this regard and also that are these tables valid and correct to control the hierarchy
regards,
Omer Aslam

[xaxis]

Perhaps you can be more specific? For instance, what do you mean by "control"?

[omerbutt]

Perhaps you can be more specific? For instance, what do you mean by "control"?

i have to show them in a hierarchical format means i have to make a vertical menu in which all the top level categories must be appearing in the 1st level i.e.
Ladies
Gents
Kids
then if i hover Ladies It must Show the Ladies Subcategories i.e.
Ladies Tees
Ladies Tops
Ladies Hoods
Ladies Zippers
Ladies Sweat Shirts

the client has asked me that there can be any number of subcategories means
he should be able to define another subcategory in the
Ladies Tees lets say he defines 2 more subcategories as
Printed Tees
Plain Tees
and then on clicking the plain tees all the plain tees must be sorted out of the databases

[hoopy]

For display purposes you need to look at a recursive function which Starts at the top and builds all the text throughout the unlimited of sub-categories. So if you had this real basic database design:

(CategoryID - ParentID - Name)

1 - 0 - Animals
2 - 1 - Dogs
3 - 1 - Cats
4 - 2 - Yorkshire Terrier
5 - 2 - Dalmation

So it would look something like this:

Animals
   -- Dogs
     -- Yorkshire Terrier
     -- Dalmation
   -- Cats

There may be another way but I had to do something like this before and I found a recursive function worked OK. Have you ever wrote a recursive function before?

[omerbutt]

For display purposes you need to look at a recursive function which Starts at the top and builds all the text throughout the unlimited of sub-categories. So if you had this real basic database design:

(CategoryID - ParentID - Name)

1 - 0 - Animals
2 - 1 - Dogs
3 - 1 - Cats
4 - 2 - Yorkshire Terrier
5 - 2 - Dalmation

So it would look something like this:

Animals
   -- Dogs
     -- Yorkshire Terrier
     -- Dalmation
   -- Cats

There may be another way but I had to do something like this before and I found a recursive function worked OK. Have you ever wrote a recursive function before?

yeah i do have but i am really stucked at this part can you give me a little hint for that any url or something
regards,
omer

[Dormilich]

must the data for the menu come from a database or will an XML file work too?

[hoopy]

OK I have put something together, I dont think its terribly efficient though. As it performs maybe too many queries if you had a mass of categories and subs. My tables looks like this for testing purposes:

cat_id 	cat_parent 	cat_name
1 	0 	Animals
2 	1 	Cats
3 	1 	Dogs
4 	3 	Yorkshire Terriers
5 	3 	Dalmations
6 	1 	Elephants
7 	0 	Cars
8 	7 	Sports Cars
9 	7 	Classic Cars

Here is the code:

<?
function cat_tree($parent,$level)
{
	$Q = mysql_query("
	  SELECT cat_id, cat_parent, cat_name
	  FROM categories 
      WHERE cat_parent = '$parent' 
      ORDER BY cat_name 
	") or die(mysql_error());
	if(mysql_num_rows($Q) != 0)
	{
		while($R = mysql_fetch_array($Q))
		{
			for($x=0;$x<=$level;$x++) 
               echo("&nbsp;&nbsp;&nbsp;");
			echo('- ' . $R['cat_name'] . "<br />");
			cat_tree($R['cat_id'], $x);
		}
	}
}

cat_tree(0,0);
?>

So it starts with 0 cats i.e mains with no parents. And works its way through to the end. It produces a list like this:

   - Animals
      - Cats
      - Dogs
         - Dalmations
         - Yorkshire Terriers
      - Elephants
   - Cars
      - Classic Cars
      - Sports Cars

As I say there is probably a better way but its a start for you to build on. I guess it would be quicker to pull all the data into an array with one query then sort it out from there rather than multiple database calls. There is a guy who wrote a nice recursive function on some other recent thread so he may have a better solution. My SQL is not too grand so maybe you can achieve all of this with some sort of multiple join infested single query as well.

Good Luck.