Adjacency List Model + Website Navigation

Question

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);

        }

    }

Answer 1

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.