1

I am going straight to the question.

I have a table, with 3 columns: "id", "name" and "parent". Each id represents categories, and the parent is the id which references the subcategory.

I need to build a menu, so, an unordered list and nested unordered lists. I came to the conclusion I have to transform that in an array, is there another way using just mysql; and if not could you indicate me the technique to build the multidimensional array in php?

Yogesh Suthar
  • 30,424
  • 18
  • 72
  • 100
il_maniscalco
  • 161
  • 1
  • 11

3 Answers3

2

I came up with another code that doesn't use recursivity:

<?php
//Let's say the DB returns:
$categories = array(
    array( 'id' => 1, 'name' => 'Category 1', 'parent' => null ),
    array( 'id' => 2, 'name' => 'Category 2', 'parent' => null ),
    array( 'id' => 3, 'name' => 'Category 3', 'parent' => 1 ),
    array( 'id' => 4, 'name' => 'Category 4', 'parent' => 3)
    );


$sortedCategories = assignChildren( $categories );

function assignChildren( &$categories )
{
    $sortedCategories = array();
    foreach( $categories as &$category )
    {
        if ( !isset( $category['children'] ) )
        {
            // set the children
            $category['children'] = array();
            foreach( $categories as &$subcategory )
            {
                if( $category['id'] == $subcategory['parent'] )
                {
                    $category['children'][] = &$subcategory;
                }
            }
        }

        if ( is_null( $category['parent'] ) )
        {
            $sortedCategories[] = &$category;
        }

    }

    return $sortedCategories;
}

var_dump( $sortedCategories );

Outputs:

array(2) {
  [0]=>
  &array(4) {
    ["id"]=>
    int(1)
    ["name"]=>
    string(10) "Category 1"
    ["parent"]=>
    NULL
    ["children"]=>
    array(1) {
      [0]=>
      &array(4) {
        ["id"]=>
        int(3)
        ["name"]=>
        string(10) "Category 3"
        ["parent"]=>
        int(1)
        ["children"]=>
        array(1) {
          [0]=>
          &array(4) {
            ["id"]=>
            int(4)
            ["name"]=>
            string(10) "Category 4"
            ["parent"]=>
            int(3)
            ["children"]=>
            array(0) {
            }
          }
        }
      }
    }
  }
  [1]=>
  &array(4) {
    ["id"]=>
    int(2)
    ["name"]=>
    string(10) "Category 2"
    ["parent"]=>
    NULL
    ["children"]=>
    array(0) {
    }
  }
}
Bgi
  • 2,513
  • 13
  • 12
1

One of the method is to prepare your multi-dimensional array as below ... it maynot be the perfect but it has worked well for me ...

$result_category = mysql_query('select all records query here ...');
    $categoryData = array(
    'items' => array(),
    'parents' => array()
);

while ($categoryItem = mysql_fetch_assoc($result_category))
{
    $categoryData['items'][$categoryItem['category_id']] = $categoryItem;
    $categoryData['parents'][$categoryItem['parent_id']][] = $categoryItem['category_id'];
}
TigerTiger
  • 10,590
  • 15
  • 57
  • 72
  • Yes but what if children have children themselves? how is (s)he going to render his list ? – Bgi Sep 18 '12 at 10:34
  • @Bgi 'parents' array will contain all references to all parent-child relationships – TigerTiger Sep 18 '12 at 10:36
  • Ok, but how do you render the list ? Using array_search ? – Bgi Sep 18 '12 at 10:37
  • You can render list, build drop-downs, un-ordered lists or however you want to present the data... it'll be a 2 steps process 1. you prepare above array 2. you loop through each item, best to do in a separate function/method, and build your list. – TigerTiger Sep 18 '12 at 10:45
0

You must make a Database call to get once the list of all the categories.

Then you must use a recursive function to assign to every category its subcategories, and to each subcategory its subcategories and again and again (thanks to recursivity this is "easy")....

Bgi
  • 2,513
  • 13
  • 12