[RESOLVED] populating multi dimensional array from queries

[laurus]

I am trying to populate a multidimensional array with a simple (categories > sub_categories) I get the categories into the array just fine but I can't figure out how to populate the sub_categories as array belonging to the first categories array; I am close.. here is the code. (the problem is about 7 lines from the bottom $cat[$i][$i2] = $subcat😉

	// get top categories from database
$query = "select tc.top_category_name from top_categories tc order by top_category_name";
$result = mysql_query($query);
$numberOfRows = mysql_num_rows($result);
$i = 0;
$cat = array(); // define the array
	if ($numberOfRows>0) 
		{
		while ($i<$numberOfRows) 
			{
				$topcat = mysql_result($result,$i,"top_category_name");
				// poulate array with first element 'top category'
				$cat[$i] = $topcat;
				// get subcategories from database
				$query ="select c.category_name from categories c 
left join cat2top_cat c2t on c2t.category_id = c.category_id
left join top_categories tc on tc.top_category_id = c2t.top_category_id
where tc.top_category_name = '".$topcat."'";
				$resultsub = mysql_query($query);
				$numberOfRows2 = mysql_num_rows($resultsub);
				$i2 = 0;
				while ($i2<$numberOfRows2) 
					{
					$subcat = mysql_result($resultsub,$i2,"category_name");
					// I need to add each subcategory to array here.
					echo "<br>".$topcat."-".$i2.$subcat."<br>";
					/*********************************************/
					// this part is not working
					$cat[$i][$i2] = $subcat;
					/**********************************************/
					$i2++;
					}
				$i++;
			}
		}

Any help is appreciated

[scrupul0us]

please use PHP tags instead of quote tags

[leatherback]

Why do you have the nested query? Why not do it in one go? I am 99.9% sure this can be done much easier, so.. Before answering your question, and wasting both our times (does that take an s?).. Please explain what you are trying to achieve, and we'll see how to best do that.

[laurus]

I want to do is populate an array with top_categories, and the sub_categories that belong to each top_category.

It needs to be dynamically populated because the categories will change frequently.
It is for the site navigation.

[leatherback]

Hi, quick 'n dirty, but should work. Please check whether I have all the variables right though..

$query =" 
   select c.category_name, tc.top_category_name
     from categories c
left join cat2top_cat c2t on c2t.category_id = c.category_id
left join top_categories tc on tc.top_category_id = c2t.top_category_id
order by tc.top_category_name";
$resultsub = mysql_query($query);
$cat = array();
$lastcat = '';
while($row = mysql_fetch_array($resultsub))
  {
  if($lastcat <> $row['top_category_name'])
    {
	$cat[$row['top_category_name']] = array();
	$lastcat = $row['top_category_name'];
	}
  $cat[$row['top_category_name']][] = $row['category_name'];
  }

[laurus]

Awesome! I even had to simplify my query to make it work but,, It works! thanks a milliion!

new query;

SELECT tc.top_category_name, c.category_name
FROM top_categories tc, categories c, cat2top_cat c2t
WHERE tc.top_category_id = c2t.top_category_id
AND c.category_id = c2t.category_id
ORDER BY tc.top_category_name, c.category_name