N level Category Tree using php and mysql

PHP
1

Can anyone help me in getting an N level category teer done??

id - title - parent
1 - cat1 - 0
2 - cat2 - 0
3 - cat3 - 0
4 - cat4 - 1
5 - cat5 - 1
6 - cat6 - 3
7 - cat7 - 4
8 - cat8 - 7
9 - cat9 - 8

How to fetch the items from database using mysql. I must use some sort of function! But I am too confused…,
I can do it by writing multiple queries but then it would not be done for N level.

2

There is an article on this site that should help.
Unfortunately I cannot post a direct link because I don’t have enough posts, but if you use the search at the top and enter “Storing Hierarchical Data in a Database”.

Hope this helps.

3

You can doit with php like this:

<?php
	
	function get_all_categories($parent, $indent = 0){
		$output		= '';
		$sqlResult 	= mysql_query("SELECT * Categories WHERE parent=".$parent." ORDER BY title ASC");
		$num_rows 	= mysql_num_rows($sqlResult);
		
		if ($num_rows > 0) {
			while($row = mysql_fetch_assoc($sqlResult)) {
				$output .= $row['title'] . '<br>';
				if (has_sub($row['id'])) {
					$output .= get_all_categories($row['id'], $indent++);
				}
			}
		}
		
		return $output;
	}
	
	function has_sub($id){
		$sqlResult 	= mysql_query("SELECT id FROM Categories WHERE parent=".$id);
		$num_rows 	= mysql_num_rows($sqlResult);
		
		return $num_rows >= 1 ? true : false;
	}
?>

I have not tested the script but expect to work!

Let us know if you need more help

4

For an unlimited number of levels, you will need to use a recursive function, such as the one oxodesign has posted.

5

Maybe this is the link - an article on storing such data

6

Thanks for the replies guys.
I have tried to get that done using this code.

I am using this class


$sql = mysql_query("select * from testcategories where parent_id=0 order by id asc ");
while($rs = mysql_fetch_array($sql))
{
	echo "<B>-".$name = $rs['catname']."</b><br>";
	$id = $rs['id'];
	$data.= $this->GetSubCat($id);
	echo $data;
}

var $dash = 0;
function GetSubCat($pid)
{
	$this->dash++;
	$subsql = "select * from testcategories where parent_id=".$pid;
	$rs = mysql_query($subsql);
	for($i=0;$i<=$this->dash;$i++)
	{
		$DashDisp  .= "-";
	}
	while($res = mysql_fetch_array($rs))
	{
		echo "<br>".$DashDisp.$name= $res['catname']."<br>";
		$id = $res['id'];
		$dataSub .= $this->GetSubCat($id);
	}
	$this->dash = 0;
	return $dataSub;
}

But still it is not showing properly like a tree.