How can PHP be used to output data from two related tables in a hierarchical structure?

When outputting data from two related tables in a hierarchical structure, you can use PHP to query the database for the related data and then organize it into a nested array structure. This can be achieved by using SQL JOIN queries to fetch the related data and then iterating through the results to build the hierarchical structure. Finally, you can use PHP functions like json_encode() to output the data in a hierarchical format. 

// Assuming we have two tables: 'categories' and 'products' with a one-to-many relationship
// Query to fetch data from both tables
$query = "SELECT c.category_id, c.category_name, p.product_id, p.product_name
          FROM categories c
          LEFT JOIN products p ON c.category_id = p.category_id
          ORDER BY c.category_id, p.product_id";

$result = mysqli_query($connection, $query);

$data = array();

// Build hierarchical structure
while ($row = mysqli_fetch_assoc($result)) {
    $data[$row['category_id']]['category_name'] = $row['category_name'];
    $data[$row['category_id']]['products'][$row['product_id']] = $row['product_name'];
}

// Output hierarchical data
echo json_encode($data);

Keywords

PHP MySQL JOIN hierarchical structure related tables

Related Questions