How can a hierarchical structure be accurately represented in a PHP database?

Representing a hierarchical structure in a PHP database can be achieved by using a parent-child relationship where each node has a reference to its parent node. This can be done by adding a column in the table that holds the reference to the parent node. To retrieve the hierarchy, recursive functions can be used to traverse the tree structure. 

// Example table structure for hierarchical data
CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);

// Function to retrieve hierarchical data
function getCategories($parent_id = null) {
    $categories = [];
    
    // Query database for categories with given parent_id
    $query = "SELECT * FROM categories WHERE parent_id = ?";
    $stmt = $pdo->prepare($query);
    $stmt->execute([$parent_id]);
    $results = $stmt->fetchAll();
    
    foreach ($results as $result) {
        $categories[] = [
            'id' => $result['id'],
            'name' => $result['name'],
            'children' => getCategories($result['id'])
        ];
    }
    
    return $categories;
}

// Example usage
$categories = getCategories();
echo json_encode($categories);

Keywords

hierarchical structure PHP database representation data modeling nested set model

Related Questions