SQL challenge

I'm building a small category based system for a CMS and I've been trying to add sub categories so the users can select them in a select option menu.

So far the code I've created is as follows;

	for($i=0; $i<count($parent_arr); $i++){

		print "<option>" . $parent_arr[$i] . "</option>";

		$ite_res = mysql_query("SELECT * FROM categories WHERE parent = '" . $parent_arr[$i] . "'");
		while($ite_arr = mysql_fetch_array($ite_res)){

			print "<option value='" . $ite_arr['id'] . "'>_" . $ite_arr['id'] . "</option>";

		}

	}

PHP:

This currently displays

2
_3
4

as 2 and 4 are parent categories and 3 is a subcategory of 2.

What I'd like to do is build an iterative structure to the algorithm so that it will display as many subcategories extending from each sub category until there are no more subcategories without building 5 while loops into the code.

Lets say we added another 3 categories 5,6,7 which all were sub categories of each other and where 5 was a sub category of 4 so the desired result would be;

2
_3
4
_5
__6
___7

My inclination is that I would have to use a do while loop and set the while to check whether there are any more results for a particular category but my head is just not quite getting round the Maths.

I'll be quite interested to hear the solutions which the SQL engineers can come up with at Digital Point.

Who can create a solution in the least amount of code?

 

Considering all of them will be shown anyway, why even do multiple SQL queries and not just a SELECT * FROM categories and let PHP's arrays functions do the sorting. Much less strain on the database with the same results. Probably a lot faster too.

 

ok lets say the result of the query brings out the following results where the format of the array is (category=>parent) and parent = 1 means that the category is the parent.

$result_array = array((2=>1),(3=>2),(4=>1),(5=>4),(6=>5),(7=>6));

How would I turn this into the desired result above? (Even if the order of the list was jumbled up)

 

Can a MOD move this to the PHP section as it has changed from an SQL problem to a PHP problem.

Here is my solution to the problem. I have not gone over the code yet to see if there are any inefficiences but I'd like to see if someone can reduce the code substantially or whether this is the best solution.

I'm sure there are probably some built in functions for my executions on the arrays as well.

<?php

// parents array
$parents = array("2,1","4,1");
// non-parents array
$num_ar = array("3,2","6,3","7,6","8,7");


$i = 0;
// the first parent is added to the front of the list
$end_array[] = $parents[$i];


do{

	// set the first position to the id of the category of the parent
	$pos_arr = explode(',',$parents[$i]);
	$pos = $pos_arr[0];


	// ret match returns the pairing if a categories parent matches the id of the current parent
	$ret_match=check_for_match($pos,$num_ar);

	// if we don't find a matching parent we increment i which in turn increments the parent
	// which we are checking. We also add the next parent to the list.
	if($ret_match==-1){
		$i++;
		$end_array[] = $parents[$i];
	}
	else
	{
		// if we've found a returning match we now add this to the list
		$end_array[] = $ret_match;

		// this match now becomes a parent and we must squeeze this in between the current parent and
		// the remaining parents so that we inadvertantly tag the matching pair onto the previous parent
		// and can tag matches for the new parent onto the list.
		unset($temp_start);
		for($l=0;$l<$i;$l++){
			$temp_start[] = $parents[$l];
		}
		$temp_start[] = $ret_match;
		for($m=($i+1);$m<count($parents);$m++){
			$temp_start[] = $parents[$m];
		}
		unset($parents);
		$parents = $temp_start;

		// We must also remove the matching category from the other category list as it has now been
		// added to the stack and we no longer have to match this up with the list of parents.
		for($k=0; $k<count($num_ar);$k++){
			if($num_ar[$k]==$ret_match){
			}
			else
			{
				$new_ar[] = $num_ar[$k];
			}
		}
		unset($num_ar);
		$num_ar = $new_ar;
		unset($new_ar);

	}

// while we have parents to check with other categories keep the loop going
} while ($i<count($parents));	

// this function will do a pattern match on the values in the num_ar 
// array to check whether any of the categories parent matches up with the 
// current parent which we are checking.
function check_for_match($pos, $num_ar){


	for($j=0; $j<count($num_ar); $j++){

		$this_cat_arr = explode(",",$num_ar[$j]);
		$this_cat = $this_cat_arr[0];
		$this_parent = $this_cat_arr[1];

		if($this_parent==$pos){
			return $num_ar[$j];
		}
	}

	return -1;

}

?>

PHP:

 

You want to look into recursive sql table walking - check out how web directory scripts work (just go download a free one and examine it's code)

It's pretty slick

 

Thanks ccoonen.

The actual solution I ended up with involved sorting the info as I added each category rather than trying to sort a list of unsorted categories.

I will definitely look into how the script works though because I'm sure their way will be far more efficient on the server resources.


Thanks!