How can JOIN and GROUP_CONCAT functions be utilized in PHP to efficiently retrieve and display categories and subcategories?

To efficiently retrieve and display categories and subcategories in PHP, you can use the JOIN and GROUP_CONCAT functions in a SQL query. By joining the tables that contain categories and subcategories and then using GROUP_CONCAT to concatenate the subcategories within each category, you can retrieve the data in a structured format for display. 

<?php
// Assuming you have a database connection established

// SQL query to retrieve categories and their corresponding subcategories
$sql = "SELECT categories.category_name, GROUP_CONCAT(subcategories.subcategory_name) AS subcategories
        FROM categories
        LEFT JOIN subcategories ON categories.category_id = subcategories.category_id
        GROUP BY categories.category_id";

$result = mysqli_query($conn, $sql);

// Display the categories and subcategories
while ($row = mysqli_fetch_assoc($result)) {
    echo "Category: " . $row['category_name'] . "<br>";
    echo "Subcategories: " . $row['subcategories'] . "<br><br>";
}

// Close the database connection
mysqli_close($conn);
?>

Keywords

JOIN GROUP_CONCAT PHP categories subcategories

Related Questions