将Array与相同值组合，但将另一个数组字典添加到组合数组中

Question

I have this PHP code to fetch the mysql data:

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        // create an array for the results 
        $menuItems['menu_items'][] = array(
            'item_id' => $row['item_id'],
            'rest_id' => $row['rest_id'],
            'item_name' => $row['item_name'],
            'item_genre' => $row['item_genre'],
            'item_price' => $row['item_price'],
            'item_descript' => $row['item_descript'],
            'wait_time' => $row['wait_time'],
            'ingredients' => array_filter(array(// do not display ingredients if null
                'ingredient_id' => $row['ingredient_id'],
                'ingredient_name' => $row['ingredient_name'],
                'ingredient_price' => $row['ingredient_price'],
                'ingredient_default' => $row['ingredient_default']
            ))
        );
        // print success or no error
        $menuItems['error'] .= '';
    }
    // check if any menu items exists
    if( $menuItems != null ) {
        echo json_encode($menuItems);
        print_r($menuItems);
    }             
    else {
        echo json_encode(array('error' => 'There are currently no menu items for this location'));
    }

This is the JSON output array, but it's outputting the same menu item for each ingredient associated with it:

Array
(
[menu_items] => Array
    (
        [0] => Array
            (
                [item_id] => 102
                [rest_id] => 67
                [item_name] => Tilapia And Rice 
                [item_genre] => Fish %26 Seafood
                [item_price] => 9.50
                [item_descript] => Tilapia and rice 
                [wait_time] => 45
                [ingredients] => Array
                    (
                        [ingredient_id] => 1
                        [ingredient_name] => herbs
                        [ingredient_price] => 0.50
                    )

            )

        [1] => Array
            (
                [item_id] => 102
                [rest_id] => 67
                [item_name] => Tilapia And Rice 
                [item_genre] => Fish %26 Seafood
                [item_price] => 9.50
                [item_descript] => Tilapia and rice 
                [wait_time] => 45
                [ingredients] => Array
                    (
                        [ingredient_id] => 2
                        [ingredient_name] => lemon
                        [ingredient_price] => 0.00
                        [ingredient_default] => 1
                    )

            )

        [2] => Array
            (
                [item_id] => 105
                [rest_id] => 67
                [item_name] => Ninja Roll
                [item_genre] => Japanese
                [item_price] => 8.00
                [item_descript] => Sushi roll. 6-8 pieces. Captain's orders!
                [wait_time] => 30
                [ingredients] => Array
                    (
                    )

            )

        [3] => Array
            (
                [item_id] => 106
                [rest_id] => 67
                [item_name] => Sushi
                [item_genre] => Japanese
                [item_price] => 8.00
                [item_descript] => Menu item description (optional)
                [wait_time] => 30
                [ingredients] => Array
                    (
                    )

            )

    )

[error] => 
)

I NEED the following though. Merge the ingredients into its associated menu item

Array
(
[menu_items] => Array
    (
        [0] => Array
            (
                [item_id] => 102
                [rest_id] => 67
                [item_name] => Tilapia And Rice 
                [item_genre] => Fish %26 Seafood
                [item_price] => 9.50
                [item_descript] => Tilapia and rice 
                [wait_time] => 45
                [ingredients] => Array
                [0] => Array
                    (
                        [ingredient_id] => 1
                        [ingredient_name] => herbs
                        [ingredient_price] => 0.50
                    )
                [1] => Array
                    (
                        [ingredient_id] => 2
                        [ingredient_name] => lemon
                        [ingredient_price] => 0.00
                        [ingredient_default] => 1
                    )
            )

        [1] => Array
            (
                [item_id] => 105
                [rest_id] => 67
                [item_name] => Ninja Roll
                [item_genre] => Japanese
                [item_price] => 8.00
                [item_descript] => Sushi roll. 6-8 pieces. Captain's orders!
                [wait_time] => 30
                [ingredients] => Array
                    (
                    )

            )

        [2] => Array
            (
                [item_id] => 106
                [rest_id] => 67
                [item_name] => Sushi
                [item_genre] => Japanese
                [item_price] => 8.00
                [item_descript] => Menu item description (optional)
                [wait_time] => 30
                [ingredients] => Array
                    (
                    )

            )

    )

[error] => 
)

How can I create the desired output array? I am so confused and am getting no where with this!

Answer 1

Make use of associative arrays. When you control the index life becomes much easier. This is especially true when dealing with one-to-many database results, as you are.

For example (and I'm obviously making assumptions/guesses about your model):

if ($stmt = $dbh->prepare($query)) {
    // initialise an array for the results 
    $menuItems = array();
    if ( $stmt->execute(array($rest_id)) ) {
        while ($row = $stmt->fetchAll(PDO::FETCH_ASSOC)) {
            // Have we seen this menu before? If not, add it to the array
            if ( !isset($menuItems['menu_items'][$row['id']]) ) {
                $menuItems['menu_items'][$row['id']] = array(ingredients => array());
            }
            // Add the ingredient.
            $menuItems['menu_items'][$row['id']]['ingredients'][$row['ingred_id']] = $row['ingred_id'];
            $menuItems['error'] .= '';
        }
        if( $menuItems != null ) {
            echo json_encode($menuItems);
            //print_r($menuItems);
        }             
        else {
            echo json_encode(array('error' => 'There are currently no menu items for this location'));
        }
    }
}

In the example above you create a list of menus where menu.id is the array key. Each menu has a value 'ingredients', which likewise is an array. In my example I use ingred_id as the key and value. Obviously you'd modify the code to use ingred_id as the key plus an array of other ingredient info as the value.

Note that depending on the size of the database it might end up being more efficient to run multiple queries. As it is now, you're pulling an awful lot of redundant data for each row.