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!
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.