help to automatically generate a sitemap

[spartan7]

hi there

 

I have a table that stores all my categories--

 

categories

----------

id

parentid

category

 

Basically, I have one table that has all the categories, and I link them through the parentid field. So if I add a Subcategory it would be id=9 and parentid=4 referencing Category 1 (id=4).

 

Now, I can get a list of all the entries using that particular parentid no problem (where parentid = x). But how would I write a script to output an entire sitemap that runs on multiple levels? Say for example I have a situation as follows:

 

Entry ID Parent

---------------

Category (id=1) (parentid=0)

Subcategory (id=2) (parentid=1)

SubSubcategory (id=3) (parentid=2)

SubSubSubcategory (id=4) (parentid=3)

 

How could I in a single script output this table data with the child categories coming under their respective parent categories?

 

Keeping in mind that the categories levels could run 1 level or 50 levels depending on how the user has set things up.

 

Can somebody point me in the right direction or help with me with the logic? Thanks

 

[Xurion]

Probably some while loops like so:

 

$category_result = mysql_query("SELECT * FROM categories WHERE parentid=0");
while($category_row = mysql_fetch_assoc($category_result)){
echo $row['page_name'];
$subcategory_result = mysql_query("SELECT * FROM categories WHERE parentid=1");
while($subcategory_row = mysql_fetch_assoc($subcategory_result)){
	echo $row['page_name'];
	$subsubcategory_result = mysql_query("SELECT * FROM categories WHERE parentid=2");
	while($subsubcategory_row = mysql_fetch_assoc($subsubcategory_result)){
		echo $row['page_name'];
		$subsubsubcategory_result = mysql_query("SELECT * FROM categories WHERE parentid=3");
		while($subsubsubcategory_row = mysql_fetch_assoc($subsubsubcategory_result)){
			echo $row['page_name'];
		}
	}
}
}

[spartan7]

hey Xurion thanks for the reply.

 

That works but the problem is if a particular user wants 5 levels to his categories I would need to write the functionality for that depending on each users needs. Some might want 2 levels (caters for), some might want 10 or whatever. So I thought there might be some way to pull the sitemap without having to write loops inside loops for as many times as there are levels. This does work though I just thought there might be some other way to do this that I was missing.

 

Probably some while loops like so:

 

$category_result = mysql_query("SELECT * FROM categories WHERE parentid=0");
while($category_row = mysql_fetch_assoc($category_result)){
echo $row['page_name'];
$subcategory_result = mysql_query("SELECT * FROM categories WHERE parentid=1");
while($subcategory_row = mysql_fetch_assoc($subcategory_result)){
	echo $row['page_name'];
	$subsubcategory_result = mysql_query("SELECT * FROM categories WHERE parentid=2");
	while($subsubcategory_row = mysql_fetch_assoc($subsubcategory_result)){
		echo $row['page_name'];
		$subsubsubcategory_result = mysql_query("SELECT * FROM categories WHERE parentid=3");
		while($subsubsubcategory_row = mysql_fetch_assoc($subsubsubcategory_result)){
			echo $row['page_name'];
		}
	}
}
}