CODEWITHSUNDEEP
phpmysqlarraysjsonmultidimensional-array
Gcid
Gcid

Reputation: 1

How to create this Multidimensional Array with this query (That eventually turns into JSON)

I have done my research. But after 2 days I'm going crazy. I really want to avoid sending multiple queries to MYSQL. I understand that the solution is iterating and inserting the values in the new array. But honestly I does not know where to start. this Multidimensional array I want to create is out of my league. Thanks!

I need to turn the following MYSQL Result:

    categories  subcategories   items
    ----------  -------------   ------
    ART       painter        picasso
    ART       painter        VanGogh
    ART       sculpture      Rodin
    ART       sculpture      Serra
    MUSIC     Rock           Lennon
    MUSIC     Rock           Mercury
    MUSIC     Classic        Mozart
    THEATRE   Drama          Shakespeare
    THEATRE   Drama          Moliere

Into This PHP Array:

 $Data = array (
    name=>'ARTISTS', 
    children=>array(
        array('name'=>'ART', 
                children=>array(
        array('name'=>'Painting', children=>array(array('name'=>'picasso'),array('name'=>'Van Gogh'))),
        array('name'=>'Sculpture', children=>array(array('name'=>'Rodin'),array('name'=>'Serra')))
        )),
        array('name'=>'MUSIC', 
                children=>array(
        array('name'=>'Rock', children=>array(array('name'=>'Lennon'),array('name'=>'Mercury'))),
        array('name'=>'Classic', children=>array(array('name'=>'Mozart')))
        )),
        array('name'=>'THEATRE', 
                children=>array(
        array('name'=>'Drama', children=>array(array('name'=>'Shakespeare'),array('name'=>'Moliere')))
        ))
    )
);

The only thing I know is that this PHP array (written manually) generate a perfect JSON once you pass it through json_encode. So this last part is not the problem. I just put it here to explain where all this is going.

$json = json_encode($Data);

Generates:

{"name":"ARTISTS","children":[{"name":"ART","children":[{"name":"Painting","children":[{"name":"picasso"},{"name":"Van Gogh"}]},{"name":"Sculpture","children":[{"name":"Rodin"},{"name":"Serra"}]}]},{"name":"MUSIC","children":[{"name":"Rock","children":[{"name":"Lennon"},{"name":"Mercury"}]},{"name":"Classic","children":[{"name":"Mozart"}]}]},{"name":"THEATRE","children":[{"name":"Drama","children":[{"name":"Shakespeare"},{"name":"Moliere"}]}]}]}

Upvotes: 0

Views: 194

Answers (2)

Mike Brant
Mike Brant

Reputation: 71384

I would scrap the notion of applying all those unneccesary name and children labels. When you get your database result set, just iterate through it and set all array index items directly.

$data = array();
while ($row = /*  your DB row fetch here */) {
    $data[$row['categories']][$row['subcategories']][] = $row[$items];
}

$json_data = json_encode($data);

Upvotes: 1

sybear
sybear

Reputation: 7784

As Mike Brant said, you should use names of your categories as indexes. But if you still need a solution that would give a result like you gave, here is some code:

$formatted = array() ;

foreach($data as $part){
    if (!array_key_exists($part['categories'], $formatted)){
        $formatted[$part['categories']] = array(
            "name" => $part['categories'],
            "children" => array()
        );
    }

    $sub = &$formatted[$part['categories']]['children'] ;

    if (!array_key_exists($part['subcategories'], $sub)){
        $sub["name"] = $part['subcategories'] ;
        $sub["children"] = array() ;
    }

    $items = &$sub['children'] ;

    $items[$part['items']] = array("name"=>$part['items']) ;
}

print_r($formatted);

Upvotes: 0

Related Questions