A better way to do this?

[dannyallen34]

Can somebody help me with the following problem:

I want to select all the entries (in this case, categories) from one table. I can do this fine with this code:

$select = @("SELECT * FROM category_kb");
while($fetch = @mysql_fetch_array($select)) {

Now, in category_kb table there are 3 fields: cid, name, and parent. The problem I have has to do with the parents. Let's say that there are 5 categories without parents, and the rest each have a parent.

Cat 1
-- SubCat 1
----SubCat 2
----SubCat 2
------Sub Cat 3
--------And even deeper (infinetely)
--Subcat 2
----Subcat 1
Cat2
etc..

So, I want to be able to show something like that given just one query. I want it to calculate the depth and the put ---- depending on how deep it is just from this one query. Can someone show me how to do this?

If this cannot be done, please tell me how to do it with the least amount of queries possible.

Thanks.

[dewen]

a little confuse with the question: what is the def of "parent" field? How do you get the subcat?

[Drakla]

// recursive function to show children
function show_branch($catid=0)
{
	static $depth=-1;

require("adminwis/db-connect-inc.php");

$depth++;

$padder='';	
for($i=0;$i<$depth;$i++) $padder.="         ";

$query="SELECT * from cats WHERE pid=$catid ORDER BY name";	
$db->safe_query($query);

while( $row=$db->fetch_assoc() )
{
	$name="<a href='".SEARCHPAGE."?catid=$row[id]'>$row[name]</a>";
	echo $padder.$name.'<br>';

	show_branch( $row['id'] );
	//find mah chillun
}

$db->free_result();
$db->close();

$depth--;
}

It's old, it works - adapt it to your scenario.

I'm cringing at the require in the function, but it was one of the first things I ever wrote in PHP and gets you a new copy of the $db database object every time, as a seperate result handle will be needed for each recursion into the function.

[dewen]

follow Drakla's method, if use array(may not efficient for big array):

$dep = 0;
function array_show_branch($cid = 0, $aCat)
{
	global $dep;
	$dep++;
	$resultCat = array();
	while (list($key, $thisCat) =  each($aCat)){
		if ($thisCat['pid'] == $cid) {
			$resultCat[$thisCat['cid']] = array_show_branch($thisCat['cid'], $aCat);
		}
	}
	$dep--; 
	return $resultCat;
}

$aCat = array(
	array('cid'=>100, 'pid'=>0),	
	array('cid'=>101, 'pid'=>100),	
	array('cid'=>102, 'pid'=>103),	
	array('cid'=>103, 'pid'=>100),	
	array('cid'=>104, 'pid'=>103),	
	array('cid'=>105, 'pid'=>104),	
	array('cid'=>106, 'pid'=>100),	
	array('cid'=>107, 'pid'=>101),	
	array('cid'=>108, 'pid'=>101)	
);
echo '<PRE>';
print_r(array_show_branch(0, $aCat));
echo '</PRE>';

[code=php]