How do I get the first level category to display o

Question

I have a table set up in mysql as follows:

I would like to create a sidebar category navigation with Parent category - subcategories - and possible third level categories which I have stored in another table.

I am trying to get them to display in a tree structure like so:

A Night Out

• Clubs and Societies
• Public houses
• Restaurants
• Taxis and Private Hire Vehicles
• Theatres and concert Halls
• Wine Bars

Accommodation

• Bed and Breakfast
• Guest Houses
• Holiday Accommodation
• Hotels
• Self Catering Accommodation

Agriculture

• Agricultural Machinery and Spares
• Agricultural Merchants
• Animal Feedstuffs
• Country Wear
• Dairies

etc

At the moment I am able to create the following:

using this code:

$dbc = mysql_connect($db_host,$db_user,$db_pass);
$sdb = mysql_select_db($db_database);

$query = 'SELECT category_name, subcategory_name FROM categories, subcategories WHERE subcategory_parent = category_name';

$result = mysql_query($query, $dbc)
or die (mysql_error($dbc));

while($row = mysql_fetch_array($result)) {

$catname = $row["category_name"];
$subcatname = $row["subcategory_name"];

echo "<li>$catname</li><ul><li>$subcatname</li></ul>";
}

I want to get the first level category to display only once with the subcategories in a list below them. Can anyone tell me the most efficient way to do this? I think I need to use foreach but am not sure.

The third level category table I have set up has the same structure as this table but is subsubcategory_id / subsubcategory_parent / subsubcategory_name.

Answer 1

I think you would need something along the lines of this:

$cat_query = mysql_query("SELECT * FROM `TABLE_NAME` GROUP BY `subcategory_parent`") or die(mysql_error());

while($r_cat = mysql_fetch_array($cat_query)) {

    $cat_name = $r_cat['subcategory_parent'];

    echo("$cat_name<br />");

    $sub_cat_query = mysql_query("SELECT * FROM `subcategory_name` WHERE (`subcategory_parent` = '$cat_name')") or die(mysql_error());

    while($r_sub_cat = mysql_fetch_array($sub_cat_query)) {

        $sub_cat_name = $r_sub_cat['subcategory_name'];

        echo(" - $sub_cat_name<br />");

    }

    echo("<br />");

}

There is probably a more efficient way of doing this, a lot of the guys here are far more knowledgeable in this field, but this should do the trick! You may also want to check, table names/column titles etc too, to make sure they match your database.

Answer 2

only output the catname when it changes. You will also need to order your query by category_name first.

$row = mysql_fetch_array($result);
$catname = $row["category_name"];
$subcatname = $row["subcategory_name"];
$last = $catname;

echo "<li>$catname</li><ul>"

while($row = mysql_fetch_array($result)) {
    $catname = $row["category_name"];
    $subcatname = $row["subcategory_name"];
    if($last != $catname){
        echo "</ul><li>$catname</li><ul>"
    }
    echo "<li>$subcatname</li>";
    $last = $catname;
}
echo "</ul>";

Edit: Just re-read the question fully and want to say that when it comes to hierarchical trees, using a parent/child (or category/sub/subsub) is not the best method. Although it does work, it usually requires multiple queries and recursive functions for display. A better approach is to use the nested set which is made for exactly this purpose.

Answer 3

I'd simplify the data structure if you want the possibility of many-layered sub-categories (which you've said you might). The best way I've found to do something like this is to do something slightly different with data:

category
+----+-----------+------------+----------------------------+
| id | parent_id | sort_order | name                       |
+----+-----------+------------+----------------------------+
| 1  | 0         | 0          | A Night Out                |
| 2  | 1         | 1          | Clubs and Societies        |
| 3  | 1         | 2          | Public houses              |
| 4  | 1         | 3          | Restaurants                |
| 5  | 1         | 4          | Taxis ...                  |
| 6  | 1         | 5          | Theatres ...               |
| 7  | 1         | 6          | Wine Bars                  |
| 8  | 0         | 0          | Accommodation              |
| 9  | 8         | 1          | Bed and Breakfast          |
| 10 | 8         | 2          | Guest Houses               |
| x  | x         | x          | ... and so on ...          |
+----+-----------+------------+----------------------------+

Note: I'm only using the sort_order to sort on child nodes with the same parent.

Now I'd create a really simple SQL query to just retrieve the data ordered by parent_id then sort_order:

SELECT category.id, category.parent_id, category.name FROM category ORDER BY category.parent_id ASC, category.sort_order ASC

Now comes the "fun" part ... because, really, what we want here is structured data so I'd pass the query into an XML DOMDocument() to create a structured category system.

Essentially you loop through the data and as you're doing that loop you create nodes with an id something like node_{$category_id} and you can then assign the child nodes to their parent in the right order. You can then use standard DOM navigation to retrieve the data tree you want.

The biggest advantage with this is that you can start from any child node and walk back up the node tree to create your category list - which means you can have it expand and collapse.

I did write a far more detailed explanation recently; I can post a link to it if you'd like.