What are the limitations of using a 'normal Parent-ID' tree structure for storing categories in PHP and MySQL, especially in terms of querying subcategories?

Using a 'normal Parent-ID' tree structure for storing categories in PHP and MySQL can make querying subcategories inefficient and complex, especially when dealing with multiple levels of nesting. One way to address this issue is to use a nested set model, which allows for easier retrieval of subcategories and their hierarchical relationships. 

// Function to retrieve all subcategories of a given category using nested set model
function getSubcategories($category_id, $left_key, $right_key) {
    global $db; // assuming $db is the database connection object
    
    $query = "SELECT * FROM categories WHERE left_key > (SELECT left_key FROM categories WHERE category_id = ?) AND right_key < (SELECT right_key FROM categories WHERE category_id = ?)";
    $stmt = $db->prepare($query);
    $stmt->bind_param("ii", $category_id, $category_id);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $subcategories = [];
    while ($row = $result->fetch_assoc()) {
        $subcategories[] = $row;
    }
    
    return $subcategories;
}

Keywords

PHP MySQL tree structure subcategories querying

Related Questions