need help with recursive category loop

[schwim]

Hi there folks!

I'm dealing with a category system that has parent categories. If the parent is listed as 0, it's top level, otherwise parent represents the id of the parent category.

So:

Wheels id = 1, parent = 0
Wheel Accessories = 2, parent = 1

and I don't really know how many levels it will eventually use. My problem is that I'm not smart enough to figure out how to build a loop regardless, of the number of child levels. I had to write each subquery into it's parent's loop, so I've written the ability for two children levels:

$cat_list = '';
/* Build the categories */
$q_cats = "SELECT * FROM categories WHERE parent = '0' ORDER BY name ASC";
$r_cats = mysqli_query ($link, $q_cats) or die('Catastrophic failure [Super secret code 595]');
$num_cats=mysqli_num_rows($r_cats);
if($num_cats == 0){
	$cat_list = '<option>No Categories</option>';
}else{

while ($row_cats = mysqli_fetch_assoc ($r_cats)) {
	$cat_id = $row_cats['id'];
	$name = $row_cats['name'];
	$safename = $row_cats['safename'];
	$parent = $row_cats['parent'];
	if($type == $safename){
		$selected = ' selected';
	}else{
		$selected = '';
	}
	$cat_list = $cat_list . '
	<option value="'.$safename.'"'.$selected.'>'.$name.'</option>
	';
	/* Before we can continue this loop, check for child categories. */
	$q_cats2 = "SELECT * FROM categories WHERE parent = '$cat_id' ORDER BY name ASC";
	$r_cats2 = mysqli_query ($link, $q_cats2) or die('Catastrophic failure [Super secret code 998776]');
	$num_cats2=mysqli_num_rows($r_cats2);
	if($num_cats2 != 0){
		while ($row_cats2 = mysqli_fetch_assoc ($r_cats2)) {
			$cat_id = $row_cats2['id'];
			$name = $row_cats2['name'];
			$safename = $row_cats2['safename'];
			$parent = $row_cats2['parent'];
			if($type == $safename){
				$selected = ' selected';
			}else{
				$selected = '';
			}
			$cat_list = $cat_list . '
			<option value="'.$safename.'"'.$selected.'>&middot; '.$name.'</option>
			';

			/* Before we can continue this loop, check for child categories. */
			$q_cats3 = "SELECT * FROM categories WHERE parent = '$cat_id' ORDER BY name ASC";
			$r_cats3 = mysqli_query ($link, $q_cats3) or die('Catastrophic failure [Super secret code 478926]');
			$num_cats3=mysqli_num_rows($r_cats3);
			if($num_cats3 != 0){
				while ($row_cats3 = mysqli_fetch_assoc ($r_cats3)) {
					$cat_id = $row_cats3['id'];
					$name = $row_cats3['name'];
					$safename = $row_cats3['safename'];
					$parent = $row_cats3['parent'];
					if($type == $safename){
						$selected = ' selected';
					}else{
						$selected = '';
					}
					$cat_list = $cat_list . '
					<option value="'.$safename.'"'.$selected.'>&middot; &middot; '.$name.'</option>
					';
				}
			}

		}
	}
}
}

Could someone explain how I might build it so it will grab any available children and stop only when it's reached the end of the nested categories?

Thanks for your time!

[Bonesnap]

Recursion can always be a noodle scratcher.

For this kind of approach it may be best to extract it out into a function that takes a category ID. The function uses the category ID to perform the query you have (it's the same query each time so you can plug the category ID into it). After the query does its thing and your loop has completed, you can check if the parent is 0 or not. If it's not then call the function again and pass the parent ID. Otherwise return and/or do any clean up.

[dalecosp]

This is actually a problem I may run into some day.

I've got quite a bit of experience with "categories", but for the most part we don't have "great-grandchildren" cats, only Parent, child, and the child's child.

We do have a "has_children($id)" function that simply returns whether a query for children returns results.

It could be that creating a multi-dimensional array by means of an increment variable, inside a while loop, would get you close to what you want:

[laserlight]

Since your category tree should be rather flat, recursion should be okay since you will not run into problems due to excessive recursion depth. If you really want to use iteration, then you need an explicit stack as it is not tail recursive.

Bonesnap's idea sounds right, except that you may also need to pass the recursion depth so you can generate the "·" formatting accordingly.