邻接列表模型 +网站导航
我正在使用邻接列表模型在我的网站中查找子类别。我有可用的 PHP 代码来查找所有类别和子类别，但现在我不知道如何使用它来创建导航系统。以下是该网站的工作方式，非常基本：
URL 字符串 将有一个主要类别，然后是级别
index.php?category=类别名称&level1=子类别&level2=另一个子类别&level3=内容项目
稍后我会制作SEO友好链接。
没有子类别的网址 其中级别 1 是内容项
www.website.com/category/content-item/
包含子类别的网址 其中级别 1、2、3 等是子类别，最终级别是内容项
www.website.com/category/sub-category/sub-category-2/content-item/
这是我用来查找类别和子类别的代码。目前它只输出所有类别和子类别的列表以及每个子级的级别。不确定这是否有帮助，它只是创建一个列表。
function display_children($ParentCategoryID, $Level) {
// retrieve all children of parent
if ($ParentCategoryID == ''){
$Result = mysql_query('SELECT * FROM categories WHERE parent_category_id IS null');
}
else{
$Result = mysql_query('SELECT * FROM categories WHERE parent_category_id="'.$ParentCategoryID.'";');
}
// display each child
while ($Row = mysql_fetch_array($Result)) {
echo str_repeat('-',$Level)."[".$Level."]".$Row['category_name']."<br />";
display_children($Row['category_id'], $Level + 1);
}
}
I am using the adjacency list model to find sub categories within my website. I have working PHP code to find all the categories and sub categories, but now I cannot figure out how use that to create a navigation system. Here is how the site will work, very basic:
URL string
There will be a main category, followed by levels
index.php?category=category-name&level1=sub-category&level2=another-sub-category&level3=content-item
Later I will make SEO friendly links.
URL with no sub categories
Where Level 1 is the content item
www.website.com/category/content-item/
URL with sub categories
Where Level 1, 2, 3, etc are the sub categories and the final level is the content item
www.website.com/category/sub-category/sub-category-2/content-item/
Here is the code I am using to find categories and sub categories. Currently it just outputs a list of all categories and sub categories and number's the level of each child. Not sure if this helps, it just creates a list.
function display_children($ParentCategoryID, $Level) {
// retrieve all children of parent
if ($ParentCategoryID == ''){
$Result = mysql_query('SELECT * FROM categories WHERE parent_category_id IS null');
}
else{
$Result = mysql_query('SELECT * FROM categories WHERE parent_category_id="'.$ParentCategoryID.'";');
}
// display each child
while ($Row = mysql_fetch_array($Result)) {
echo str_repeat('-',$Level)."[".$Level."]".$Row['category_name']."<br />";
display_children($Row['category_id'], $Level + 1);
}
}
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
首先查看此问题，了解有关如何操作的选项表示数据库中的分层数据。
邻接列表因其简单性而非常有用，并且使更改变得容易，但也可能很糟糕，因为它会导致递归代码，例如上面的函数，在实践中，这是负载下的性能杀手。在不更改数据模型的情况下，最好的方法是使用MySQL 会话变量可在一次查询中检索整个层次结构，从而在一次数据库调用中返回您需要的所有数据。即使这会导致负载下的性能较差——比递归函数差——但仍然不好；而且，我是根据经验写的:)。
如果是我，我会使用嵌套集、邻接列表以及一些非规范化，例如桥接表和平面表，或者只是沿袭表。实际上取决于数据更改的频率以及您是否需要轻松完成这些更改。所有这些选项的使用速度应该快得多，而不是仅仅依赖父子 ID 列。
See this question first for options on how to represent hierarchical data in a database.
Adjacency list is great for its simplicity, and makes changes easy, but can be awful because it leads to recursive code, such as your function above, in practice, which is a performance killer under load. The best approach, absent changing your data model is using MySQL session variables to retrieve the entire hierarchy in one query, which brings back all the data you need in one database call. Even this though leads to poor performance under load - less so than the recursive function - but still not good; and, I write from experience :).
If it was me I'd use either Nested Sets, Adjacency List in combination with some denormalizations, such as the Bridge Table and Flat Table, or just a Lineage Table. Really depends on how often the data changes and if you need those changes to be done easily. All of these options should be much, much faster, to work with rather than relying upon just the parent-child ID columns.