PHP While and ForEach not producing desired results

Question

I am currently using a while statement to get each of the associated records for a meal_id but my foreach is not working as desired. My goal is to produce the meal row for each meal found, then display the meal items associated with that meal below it, then loop through to the next meal and repeat.

Currently, the result is showing the correct meal, but then displaying every meal item for every other meal below it. Such as:

Meal
Meal Item
Meal Item
Unrelated Meal Item
Unrelated Meal Item
Unrelated Meal Item
Unrelated Meal Item
Unrelated Meal Item
Unrelated Meal Item
Unrelated Meal Item

When it should be doing:

Meal 1
Meal Item
Meal Item

Meal 2
Meal Item

Meal 3
Meal Item
Meal Item
Meal Item
Meal Item

I would like to also obtain the number of items in that meal also, and display that in the meal row of the table. Here is my code so far and any help you can provide would be amazing! :-)

<?php
$con=mysqli_connect($DBServer,$DBUser,$DBPass,$DBName);
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
    $view = 'empty';
if(isset($_GET["v"]))
    $view = $_GET["v"];

switch($view)
{
    default:
        $result = mysqli_query($con,"SELECT * FROM tblmeals INNER JOIN tblmealitems ON tblmeals.meal_id = tblmealitems.meal_id WHERE DATE(meal_date) = CURDATE() ORDER BY meal_date DESC, meal_time DESC");
        $viewstatus = "Today (default view)";
        break;
    case 'all':
        $result = mysqli_query($con,"SELECT * FROM tblmeals INNER JOIN tblmealitems ON tblmeals.meal_id = tblmealitems.meal_id ORDER BY meal_date DESC, meal_time DESC");
        $viewstatus = "All Records";
        break;
    case 'today':
        $result = mysqli_query($con,"SELECT * FROM tblmeals INNER JOIN tblmealitems ON tblmeals.meal_id = tblmealitems.meal_id WHERE DATE(meal_date) = CURDATE() ORDER BY meal_date DESC, meal_time DESC");
        $viewstatus = "Today";
        break;
    case 'yesterday':
        $result = mysqli_query($con,"SELECT * FROM tblmeals INNER JOIN tblmealitems ON tblmeals.meal_id = tblmealitems.meal_id WHERE DATE(meal_date) = CURDATE() - INTERVAL 1 DAY ORDER BY meal_date DESC, meal_time DESC");
        $viewstatus = "Yesterday";
        break;
    case 'week':
        $result = mysqli_query($con,"SELECT * FROM tblmeals INNER JOIN tblmealitems ON tblmeals.meal_id = tblmealitems.meal_id WHERE meal_date BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE() ORDER BY meal_date DESC, meal_time DESC");
        $viewstatus = "Last 7 Days";
        break;
    case "fortnight":
        $result = mysqli_query($con,"SELECT * FROM tblmeals INNER JOIN tblmealitems ON tblmeals.meal_id = tblmealitems.meal_id WHERE meal_date BETWEEN CURDATE() - INTERVAL 14 DAY AND CURDATE() ORDER BY meal_date DESC, meal_time DESC");
        $viewstatus = "Last 14 Days";
        break;
} ?><br>

<div class="viewdesc">Current View: <strong><?php echo($viewstatus); ?></strong></div><br>
<br>
<?php 

    // Start record fail error checking

    if (!$result) {
    printf("Error: %s\n", mysqli_error($con));
    exit();
}
    // Start record fail error checking */

    if (mysqli_num_rows($result) === 0) {?>
          <div id="resultserror">
No results could found for the selected view.</div>
          <?php } else { ?>
              <table id="results" width="100%" cellpadding="3" cellspacing="0">
<tr>
<th align="center">Meal #</th>
<th align="center">User #</th>
<th align="center">Meal Items</th>
<th align="center">Meal Date</th>
<th align="center">Meal Time</th>
<th align="center">Meal Notes</th>
</tr>
<?php
    while($row = mysqli_fetch_assoc($result))
{
    ?>
<tr>

<td align='center'><?php echo($row['meal_id']);?></td>
<td align='center'><?php echo($row['user_id']);?></td>
<td align='center'><?php echo("Items: " . mysqli_num_rows($result) . "");?></td>
<td align='center'><?php echo(date("d/m/Y", strtotime($row['meal_date'])));?></td>
<td align='center'><?php echo(date("g:i A", strtotime($row['meal_time'])));?></td>
<td align='center'><?php echo($row['meal_notes']);?></td>

</tr>
<?php foreach ($result as $row)
{?>
<tr>
<td align='center' colspan='4'><?php echo($row['meal_item_name']);?></td>
<td align='center'><?php echo($row['meal_item_measure']);?></td>
<td align='center'><?php echo($row['meal_item_measurement']);?></td>
</tr>
<?php
}
}
?>
<tfoot>
<tr>
  <td colspan="8" align="center">- end of report -</td></tr></tfoot>
</table>
<?php
mysqli_close($con);
                     }?>

Answer 1

Refractored your code a bit to use 2 queries instead of a JOIN.

Note that I'm not a fan of interweaving HTML and PHP tags like this - but that's probably personal preference and not the topic of this question:

<?php
$con=mysqli_connect($DBServer,$DBUser,$DBPass,$DBName);
// Check connection
if (mysqli_connect_errno())
{
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$view = 'empty';
if(isset($_GET["v"]))
    $view = $_GET["v"];

switch($view)
{
    case 'all':
        $result = mysqli_query($con,"SELECT * FROM tblmeals ORDER BY meal_date DESC, meal_time DESC");
        $viewstatus = "All Records";
        break;
    case 'today':
        $result = mysqli_query($con,"SELECT * FROM tblmeals WHERE DATE(meal_date) = CURDATE() ORDER BY meal_date DESC, meal_time DESC");
        $viewstatus = "Today";
        break;
    case 'yesterday':
        $result = mysqli_query($con,"SELECT * FROM tblmeals WHERE DATE(meal_date) = CURDATE() - INTERVAL 1 DAY ORDER BY meal_date DESC, meal_time DESC");
        $viewstatus = "Yesterday";
        break;
    case 'week':
        $result = mysqli_query($con,"SELECT * FROM tblmeals WHERE meal_date BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE() ORDER BY meal_date DESC, meal_time DESC");
        $viewstatus = "Last 7 Days";
        break;
    case "fortnight":
        $result = mysqli_query($con,"SELECT * FROM tblmeals WHERE meal_date BETWEEN CURDATE() - INTERVAL 14 DAY AND CURDATE() ORDER BY meal_date DESC, meal_time DESC");
        $viewstatus = "Last 14 Days";
        break;
    default:
        $result = mysqli_query($con,"SELECT * FROM tblmeals WHERE DATE(meal_date) = CURDATE() ORDER BY meal_date DESC, meal_time DESC");
        $viewstatus = "Today (default view)";
        break;
}

if (!$result) {
    printf("Error: %s\n", mysqli_error($con));
    exit();
}

$meals = array();
while($row = mysqli_fetch_assoc($result))
{
    $meal = array(
        "meal_id" => $row["meal_id"],
        "user_id" => $row["user_id"],
        "items" => array(),             // Gets filled next
        "date" => date("d/m/Y", strtotime($row['meal_date'])),
        "time" => date("g:i A", strtotime($row['meal_time'])),
        "notes" => $row['meal_notes']
    );

    $itemResult = mysqli_query($con, "SELECT * FROM tblmealitems WHERE meal_id = ".$row["meal_id"]);
    while($itemRow = mysqli_fetch_assoc($itemResult))
    {
        $meal["items"][] = array(
            "meal_item_name" => $itemRow["meal_item_name"],
            "meal_item_measure" => $itemRow["meal_item_measure"],
            "meal_item_measurement" => $itemRow["meal_item_measurement"],
        );
    }

    // Add it to the meals list
    $meals[] = $meal;
}

?>
<div class="viewdesc">Current View: <strong><?php echo($viewstatus); ?></strong></div><br>
<br>
<?php 
    if (count($meals) === 0) {?>
        <div id="resultserror">
            No results could found for the selected view.
        </div>
    <?php } else { ?>
        <table id="results" width="100%" cellpadding="3" cellspacing="0">
            <tr>
                <th align="center">Meal #</th>
                <th align="center">User #</th>
                <th align="center">Meal Items</th>
                <th align="center">Meal Date</th>
                <th align="center">Meal Time</th>
                <th align="center">Meal Notes</th>
            </tr>
    <?php
        foreach($meals as $meal)
        {
    ?>
            <tr>
                <td align='center'><?php echo($meal['meal_id']);?></td>
                <td align='center'><?php echo($meal['user_id']);?></td>
                <td align='center'><?php echo("Items: " . count($meal['items']) . "");?></td>
                <td align='center'><?php echo(date("d/m/Y", strtotime($meal['meal_date'])));?></td>
                <td align='center'><?php echo(date("g:i A", strtotime($meal['meal_time'])));?></td>
                <td align='center'><?php echo($meal['meal_notes']);?></td>
            </tr>
            <?php foreach ($meal["items"] as $mealItem)
            {?>
                    <tr>
                        <td align='center' colspan='4'><?php echo($mealItem['meal_item_name']);?></td>
                        <td align='center'><?php echo($mealItem['meal_item_measure']);?></td>
                        <td align='center'><?php echo($mealItem['meal_item_measurement']);?></td>
                    </tr>
            <?php
            }
        }
    ?>
    <tfoot>
        <tr>
        <td colspan="8" align="center">- end of report -</td>
        </tr>
    </tfoot>
</table>
<?php
    mysqli_close($con);
}?>