In the context of a recipe database, what are some alternative approaches to aggregating and displaying ingredient data in a table format using PHP and MySQL?

Issue: When displaying ingredient data in a table format in a recipe database, it is important to consider alternative approaches to aggregate and display the data efficiently and effectively. One alternative approach is to use a pivot table structure where ingredients are listed as column headers and each row represents a recipe with the corresponding quantities of ingredients. This allows for a more concise and organized display of ingredient data. 

// Retrieve ingredient data from MySQL database
$query = "SELECT recipe_name, ingredient_name, quantity FROM recipes";
$result = mysqli_query($connection, $query);

// Create an associative array to store ingredient data in a pivot table format
$ingredientData = array();
while($row = mysqli_fetch_assoc($result)) {
    $recipeName = $row['recipe_name'];
    $ingredientName = $row['ingredient_name'];
    $quantity = $row['quantity'];
    
    if(!isset($ingredientData[$recipeName])) {
        $ingredientData[$recipeName] = array();
    }
    
    $ingredientData[$recipeName][$ingredientName] = $quantity;
}

// Display ingredient data in a table format
echo "<table>";
echo "<tr><th>Recipe Name</th>";
$ingredients = array_unique(array_reduce($ingredientData, 'array_merge', array()));
foreach($ingredients as $ingredient) {
    echo "<th>$ingredient</th>";
}
echo "</tr>";

foreach($ingredientData as $recipeName => $ingredients) {
    echo "<tr><td>$recipeName</td>";
    foreach($ingredients as $quantity) {
        echo "<td>$quantity</td>";
    }
    echo "</tr>";
}

echo "</table>";

Keywords

PHP MySQL recipe database ingredient data table format

Related Questions