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?
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) {
}
}
}
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'];
}
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")....
