Creating multidimentional arrays that loop through database info

Question

I feel really stupid asking this and I've searched around before and had a bit of help but not really. I really want to understand how to do this.

Basically I want the output to be something like this

The problem I am having is I am not sure how to make sure the type is not a child type and to loop through infinitely if needed for subtypes... or at least 3, possibly 4 times.

Here's an SQL dump.
Here's my not working code.
Here's what it's outputting.
Here's what I want it to output (note the type IDs in the subtypes is being used instead of starting from 1 again, note also that if sonnet had a sub type it would do the same thing for sonnet).

If anyone could help me at all that would be fantastic. I really want to learn and understand how to do this correctly but I'm very lost!

Sorry for all the links I keep getting "your post has improperly formatted code" even using pre tags on that first link....it's been rough posting this :(.

Answer 1

I finally got it to work with this :)

function getCategoriesTypes($workIndexOptions)
{
 global $smcFunc, $scripturl, $user_info, $modSettings, $txt;
 global $settings, $options, $context, $sourcedir;

  $result_work_cats_and_types = $smcFunc['db_query']('workindex_fetch_cats_and_types' , '
  SELECT
   c.id_cat,
   c.cat_order,
   c.cat_name,
   c.cat_desc,
   t.id_type,
   t.id_cat AS parent_cat,
   t.id_parent,
   t.child_level,
   t.type_order,
   t.type_name,
   t.type_desc,
   t.num_works,
   t.num_comments,
   t.unapproved_comments
  FROM
   {db_prefix}works_categories AS c,
   {db_prefix}works_types AS t
  WHERE
   c.id_cat = t.id_cat
  ORDER BY
   c.cat_order, t.id_parent
 ');

 // Start with an empty array.
 $work_categories = array();
 
 while ($row = $smcFunc['db_fetch_assoc']($result_work_cats_and_types))
 {
  if (!isset($work_categories[$row['id_cat']]))
  {
   $work_categories[$row['id_cat']] = array(
    'id' => $row['id_cat'],
    'order' => $row['cat_order'],
    'name' => $row['cat_name'],
    'description' => $row['cat_desc'],
    'href' => $scripturl . '#c' . $row['id_cat'],
    'types' => array() ,
   );
  }

  
  if (($work_categories[$row['child_level']]) == 0)
  {
  $work_categories[$row['id_cat']]['types'][$row['id_type']] = array(
   'id' => $row['id_type'],
   'order' => $row['type_order'],
   'parent_cat' => $row['parent_cat'],
   'parent' => $row['id_parent'],
   'child_level' => $row['child_level'],
   'name' => $row['type_name'],
   'description' => $row['type_desc'],
   'works' => $row['num_works'],
   'comments' => $row['num_comments'],
   'href' => $scripturl . '?type=' . $row['id_type'] . '.0',
   'link' => '<a href="' . $scripturl . '?type=' . $row['id_type'] . '.0">' . $row['type_name'] . '</a>',
   'types' => array() ,
  );
  }
 
  if (($work_categories[$row['child_level']]) > 0)
  {
  $work_categories[$row['id_cat']]['types'][$row['id_parent']]['types'][$row['id_type']] = array(
   'id' => $row['id_type'],
   'order' => $row['type_order'],
   'parent_cat' => $row['parent_cat'],
   'parent' => $row['id_parent'],
   'child_level' => $row['child_level'],
   'name' => $row['type_name'],
   'description' => $row['type_desc'],
   'works' => $row['num_works'],
   'comments' => $row['num_comments'],
   'href' => $scripturl . '?type=' . $row['id_type'] . '.0',
   'link' => '<a href="' . $scripturl . '?type=' . $row['id_type'] . '.0">' . $row['type_name'] . '</a>',
  );
  }
 }
 $smcFunc['db_free_result']($result_work_cats_and_types);
 
 // Let's return something....
 return $work_categories;


}

Answer 2

With such a big code sample, it's hard to do any kind of testing, but this might get you a bit closer to your goal:

<?php
function getCategoriesTypes($workIndexOptions)
    {
        global $smcFunc, $scripturl, $user_info, $modSettings, $txt;
        global $settings, $options, $context, $sourcedir;

        $result_work_cats_and_types = $smcFunc['db_query']('workindex_fetch_cats_and_types' , '
                SELECT
                        c.id_cat,
                        c.cat_order,
                        c.cat_name,
                        c.cat_desc,
                        t.id_type,
                        t.id_cat AS parent_cat,
                        t.id_parent,
                        t.child_level,
                        t.type_order,
                        t.type_name,
                        t.type_desc,
                        t.num_works,
                        t.num_comments,
                        t.unapproved_comments
                FROM
                        {db_prefix}works_categories AS c,
                        {db_prefix}works_types AS t
                WHERE
                        c.id_cat = t.id_cat

        ');

        // Start with an empty array.
        $work_categories = array();

        while ($row = $smcFunc['db_fetch_assoc']($result_work_cats_and_types)) {
                if (!isset($work_categories[$row['id_cat']])) {
                        $work_categories[$row['id_cat']] = array(
                                'id' => $row['id_cat'],
                                'order' => $row['cat_order'],
                                'name' => $row['cat_name'],
                                'description' => $row['cat_desc'],
                                'href' => $scripturl . '#c' . $row['id_cat'],
                                'types' => array()
                            );
                    }

                $idCat      =   $row['id_cat'];
                $idType     =   $row['id_type'];
                $idOrder    =   $row['order'];
                // You shouldn't generate these two arrays at the same time on every loop
                // Try doing if for the first, else for the rest.
                // Identify a unique element like order and child.
                // Assuming `child_level` will always be 0 for parent and signify new
                // array when `order` changes (I am not sure, it's hard to tell how you
                // are signifying organizing triggers. The order seems common to child and parent arrays...??)
                if($row['child_level'] == 0 && !isset($work_categories[$idCat]['types'][$idOrder])) {
                        // I put $row['order'] here as the identifying array key because I don't 
                        // see any other difference from your other keys that indicate new array
                        $work_categories[$idCat]['types'][$idOrder] = array(
                                'id' => $row['id_type'],
                                'order' => $row['type_order'],
                                'parent_cat' => $row['parent_cat'],
                                'child_level' => $row['child_level'],
                                'name' => $row['type_name'],
                                'description' => $row['type_desc'],
                                'works' => $row['num_works'],
                                'comments' => $row['num_comments'],
                                'href' => $scripturl . '?type=' . $row['id_type'] . '.0',
                                'link' => '<a href="' . $scripturl . '?type=' . $row['id_type'] . '.0">' . $row['type_name'] . '</a>',
                                'types' => array());
                    }
                else {
                        // Use the `order` key here (I am assuming)
                        $work_categories[$idCat]['types'][$idOrder]['types'][$idType] = array(
                            'id' => $row['id_type'],
                            'order' => $row['type_order'],
                            'parent_cat' => $row['parent_cat'],
                            'child_level' => $row['child_level'],
                            'name' => $row['type_name'],
                            'description' => $row['type_desc'],
                            'works' => $row['num_works'],
                            'comments' => $row['num_comments'],
                            'href' => $scripturl . '?type=' . $row['id_type'] . '.0',
                            'link' => '<a href="' . $scripturl . '?type=' . $row['id_type'] . '.0">' . $row['type_name'] . '</a>'); 
                    }
            }
    } ?>