php - 如何实现分层cms网站？

我正在编写一个学习cms的小项目，我遇到了阻碍我完成下一步的障碍。我知道我应该考虑kiss（保持简单，愚蠢），但我认为它会很好，能够将页面按层次进行分组。
问题是我希望页面[root]->fruits->tropical->bananas只能从这个url：http://localhost/cms/fruits/tropical/bananas/访问。我现在想到的是cms表有一个指向其父字段的父字段。问题是：如何解析uri地址并从数据库中选择查询最少/效率最高的行？

Table structure:
Id
Slug
...
...
...
ParentId

所有的帮助和建议都被接受。

最佳答案

这是我用来测试这个的表结构-

CREATE TABLE  `test`.`pages` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `slug` varchar(45) NOT NULL,
    `title` varchar(45) NOT NULL,
    `content` text NOT NULL,
    `parent_id` int(10) unsigned DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `UQ_page_parent_id_slug` (`parent_id`,`slug`),
    CONSTRAINT `FK_page_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `pages` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

注意上的唯一键（parent_id，slug）。这是从以下查询获得最佳性能的关键。我用5万行测试了它，它仍然在不到1毫秒的时间内返回到5个slug路径-/cms/slug-1/slug-2/slug-3/slug-4/slug-5/
下面是我为构建合适的查询而编写的php代码-

<?php

// I will assume the rest of the url has already been stripped away
$url = '/fruits/tropical/bananas/';

// lets just make sure we don't have any leading or trailing /
$url = trim($url, '/');

// now let's split the remaining string based on the /
$aUrl = explode('/', $url);

/**
* Now let's build the query to retrieve this
*/

// this array stores the values to be bound to the query at the end
$aParams = array();

$field_list = 'SELECT p1.* ';
$tables = 'FROM pages p1 ';
$where = "WHERE p1.parent_id IS NULL AND p1.slug = ? ";

// this array stores the values to be bound to the query at the end
$aParams[] = $aUrl[0];

// if we have more than one element in our array we need to add to the query
$count = count($aUrl);

for ($i = 1; $i < $count; $i++) {

    // add another table to our query
    $table_alias = 'p' . ($i + 1);
    $prev_table_alias = 'p' . $i;
    $tables .= "INNER JOIN pages $table_alias ON {$prev_table_alias}.id = {$table_alias}.parent_id ";

    // add to where clause
    $where .= "AND {$table_alias}.slug = ? ";
    $aParams[] = $aUrl[$i];

    // overwrite the content of $field_list each time so we
    // only retrieve the data for the actual page requested
    $field_list = "SELECT {$table_alias}.* ";

}

$sql = $field_list . $tables . $where;

$result = $this->db->query($sql, $aParams);

关于php - 如何实现分层cms网站？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/9609976/