What are some best practices for structuring database tables to support multi-level navigation menus in PHP?

When structuring database tables to support multi-level navigation menus in PHP, it is important to use a hierarchical data structure such as the adjacency list model or nested set model. This allows for easy retrieval of menu items at different levels. Additionally, using parent-child relationships and ordering columns can help maintain the hierarchy and order of menu items. 

CREATE TABLE menus (
    id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    parent_id INT,
    ordering INT,
    FOREIGN KEY (parent_id) REFERENCES menus(id)
);

// Retrieve all menu items at a certain level
function getMenuItemsByLevel($level) {
    $query = "SELECT * FROM menus WHERE parent_id = :parent_id ORDER BY ordering";
    $stmt = $pdo->prepare($query);
    $stmt->execute(['parent_id' => $level]);
    return $stmt->fetchAll();
}

Keywords

database tables multi-level navigation menus PHP structuring best practices

Related Questions