Creating Multidimensional Nested Array from MySQL Result with Duplicate Values (PHP)

Question

I currently am pulling menu data out of our database using the PDO fetchAll() function. Doing so puts each row of the query results into an array in the following structure:

Array ( 
        [0] => Array ( 
                       [MenuId] => mmnlinlm08l6r7e8ju53n1f58 
                       [MenuName] => Main Menu 
                       [SectionId] => eq44ip4y7qqexzqd7kjsdwh5p 
                       [SubmenuName] => Salads & Appetizers
                       [ItemName] => Tomato Salad
                       [Description] => Cucumbers, peppers, scallions and cured tuna
                       [Price] => $7.00) 
        [1] => Array ( 
                       [MenuId] => mmnlinlm08l6r7e8ju53n1f58 
                       [MenuName] => Main Menu 
                       [SectionId] => xlkadsj92laada9082lkas 
                       [SubmenuName] => Entrees 
                       [ItemName] => Portabello Carpaccio
                       [Description] => Dried tomatoes, pin nuts, mahon cheese
                       [Prices] => $18.00)
 )                      
                       ...etc.

For parsing reasons, I would like to have the array appear in a nested structure with no duplicate values:

Array ( 
          [MenuName] => Main Menu
          [MenuId] => mmnlinlm08l6r7e8ju53n1f58 
          [Section] =>  Array ( 
                                [SectionId] => eq44ip4y7qqexzqd7kjsdwh5p 
                                [SubmenuName] => Salads & Appetizers
                                [Items] => Array (
                                                    [ItemName] => Tomato Salad
                                                    [Description] => Cucumbers, peppers, scallions and cured tuna
                                                    [Prices] => Array (
                                                                        [PriceId] => xksjslkajiak1
                                                                        [Price] => $7.00)
                                                  ) 

                               [SectionId] => xlkadsj92laada9082lkas
                               [SubmenuName] => Entrees
                               [Items] => Array (                           
                                                   [ItemName] => Portabello Carpaccio
                                                   [Description] => Dried tomatoes, pin nuts, mahon cheese
                                                   [Prices] => Array (
                                                                        [PriceId => alkadh29189s09
                                                                        [Price] = $8.00)
                                                  )
                               )
)

As a n00b programmer, I've been racking my brain for the last day trying to figure out how to create this new, multidimensional array. It seems like I may have to use a couple of nested foreach statements and references, but I've had a hard time getting anything to work.

Does anyone know how I can go about doing so?

is your data strictly hierarchical? In other words Items are always grouped in a submenu and submenus are grouped in a section, and sections are grouped in a menu? If so, in your SQL query, sort by those (menu, section, submenu), then as you iterate through your results, check to see that each level has not changed and add your array. — Aerik
– Aerik, Commented Apr 6, 2012 at 21:11
Hi Aerik, yes, each item is always grouped into a submenu and each submenu is grouped into a menu. My SQL results currently are ordered in this manner, so all items in Main Menu > Appetizers are grouped together, for example. — idubs11
– idubs11, Commented Apr 6, 2012 at 21:36

Baba · Accepted Answer · 2012-04-06 21:33:29Z

2

This looks like what you are looking for

$array = array() ;

while ($row = PDO::fetchAll()) // Replace with relevant Information
{
    $section = array();
    $items  = array();
    $prices  = array();

    if(!array_key_exists($row['MenuId'], $array))
    {
        $array[$row['MenuId']] = array();
        $array[$row['MenuId']]['MenuName'] = $row['MenuName'] ;
        $array[$row['MenuId']]['MenuId'] = $row['MenuId'] ;
        $array[$row['MenuId']]['Section'] = array();
    }

    if(!array_key_exists($row['SectionId'], $array[$row['MenuId']]['Section']))
    {
        $array[$row['MenuId']]['Section'][$row['SectionId']] = array();
        $array[$row['MenuId']]['Section'][$row['SectionId']]['SectionId'] = $row['SectionId'] ;
        $array[$row['MenuId']]['Section'][$row['SectionId']]['SubmenuName'] = $row['SubmenuName'] ; 
        $array[$row['MenuId']]['Section'][$row['SectionId']]['Items'] = array() ;

    }

    $items['ItemName'] = $row['ItemName'] ;
    $items['Description'] = $row['Description'] ;

    $prices['PriceId']  = $row['PriceId'] ;
    $prices['Price']  = $row['Price'] ;

    $items['Prices'] = $prices ;
    $section['Items'] = $items ;

    $array[$row['MenuId']]['Section'][$row['SectionId']]['Items'][] = $items ;

}


var_dump($array);

With just minor changes you can make it what you want

answered Apr 6, 2012 at 21:33

Baba

95.2k29 gold badges172 silver badges222 bronze badges

Thanks for that baba. It looks like your solution presents a significant drain on resources ("Fatal error: Maximum execution time of 10 seconds exceeded").

idubs11
– idubs11

04/06/2012 21:51:08
Commented Apr 6, 2012 at 21:51
@idubs11 i was working with limited resources .... not sure of the size of data you where dealing with ... what you want to use the format for .. if you give more detailed information i think i can help improve the answer

Baba
– Baba

04/06/2012 21:56:40
Commented Apr 6, 2012 at 21:56
Typical queries result in about 50-150 rows. The formatted data is then going to be parsed and presented in the phtml.

idubs11
– idubs11

04/06/2012 22:06:48
Commented Apr 6, 2012 at 22:06
1

@idubs11 - I ran Baba's solution with the dataset that you posted and it executed in 0.0027s. Maybe there is a problem in your view file.

user1191247
– user1191247

04/07/2012 02:14:28
Commented Apr 7, 2012 at 2:14
1

Are you using native PDO? Try using while ($row = $db->fetch()) where $db is the handle to the prepared statement/executed query.

user1191247
– user1191247

04/07/2012 02:54:02
Commented Apr 7, 2012 at 2:54

| Show 5 more comments

Collectives™ on Stack Overflow

Creating Multidimensional Nested Array from MySQL Result with Duplicate Values (PHP)

1 Answer 1

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Linked

Related