How to display category and subcategory?

问题:

How to display category and subcategory ? I have one table in DB. Row in this table looks something like this:

CREATE TABLE IF NOT EXISTS `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `parent_id` int(11) NOT NULL,
  `order` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM

I want display category and subcategory like on this site: http://www.dealsdirect.com.au/c/baby-1/ (menu on the left)

My try:

<?php 
include 'Category_model.php'; 
include 'Advert_model.php'; 
$nr = $_GET['id']; 

function show_category($nr){ 


    try 
        { 
            $pdo = new PDO('mysql:host=localhost;dbname=advert', 'root', ''); 
            $pdo -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 

            $stmt = $pdo -> query("SELECT * FROM category where `parent_id` = $nr"); 


            echo "<ul>";  

            foreach ($stmt as $row){ 
                echo "<li><a href=category_view.php?id={$row['id']}> {$row['name']}</a>".show_category($row['id'])."</li>"; 

                } 
                echo "</ul>"; 
                //$id = $_GET['id']; 
                } 






       catch(PDOException $e) 
                { 
           echo 'Error!: ' . $e->getMessage(); 

           } 

   } 

   show_category($nr); 

回答1:

The below code should get you started. It's a "recursive function" - a function that calls itself. As @Fake51 has just mentioned, it's not very efficient, but it should work.

You'll need some CSS to structure the list you've produced.

function showItems($parent = 0) {
    $q = "SELECT id, name FROM category WHERE parent_id = $parent";
    $q = mysql_query($q);
    if(mysql_num_rows($q)) {
        echo "<ul>";
        while($r = mysql_fetch_row($q)) {
            echo "<li>";
            echo "<a href=\"page.php?id=".$r[0]."\">".htmlentities($r[1])."</a>";
            showItems($r[0]);
            echo "</li>"\n;
        }
        echo "</ul>\n";
    }
}
showItems();

Edit: Since there's still been no accepted answer, here's my code modified to do it all with a single SQL query which should be much more efficient, albeit a little more confusing potentially. See how it goes for you.

//Recursive function to show menu items from a passed in array
function showItems(&$menu, $parent = 0) {
    if(is_array($menu[$parent]) && sizeof($menu[$parent])) {
        echo "<ul>";
        foreach($menu[$parent] as $num=>$name) {
            echo "<li>";
            echo "<a href=\"page.php?id=".$num."\">".htmlentities($name)."</a>";
            showItems($menu, $num);
            echo "</li>\n";
        }
        echo "</ul>\n";
    }
}

//Create a multi-dimensional array of ALL menu items, separated by parent
$menu = array();
$q = "SELECT id, name, parent_id FROM category ORDER BY order";
$q = mysql_query($q);
while($r = mysql_fetch_row($q)) {
    $menu[$r[2]][$r[0]] = $r[1];
}

//Call the function
showItems($menu);

回答2:

With a structure like that, you'll need to first construct a query for the chosen item, then afterwards construct a query for the chosen items parent. Essentially, you'll need a query for each level of menus you have, that should be visible.

This is the reason why a parent_id scheme of tree relationship is not very efficient. Much better to go with nested set (http://en.wikipedia.org/wiki/Nested_set_model) or materialized path (http://en.wikipedia.org/wiki/Materialized_path). If you expect to do some updating of the items, materialized path will probably be easier to deal with in the long run - I personally much prefer it, having tried both.

回答3:

Since you only need category and all its subcategories displayed you don't need recursion for menu construction, and you certainly don't want recursion in your DB calls. So most basic solution (all code in one file) would be something like this:

<?php 
include 'Category_model.php'; 
include 'Advert_model.php'; 

$pdo = new PDO('mysql:host=localhost;dbname=advert', 'root', ''); 
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$nr = $_GET['id'];

$sql = 'SELECT * FROM category where `parent_id` = :id or `id` = :id';
$stmt = $pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$stmt->execute(array(':id' => $nr));
$menuCategories = $sth->fetchAll();

// If sub category id is passed fetch whole menu
if ((1 == count($menuCategories)) && ($nr == $menuCategories[0]['id'])) {
    $nr = $menuCategories[0]['parent_id'];
    $stmt->execute(array(':id' => $nr));
    $menuCategories = $sth->fetchAll();
}

$parentCategoryFormat = '<h5><a href="category_view.php?id=%s">%s</a></h5>';
$subCategoryFormat = '<li><a href="category_view.php?id=%s">%s</a></li>';
$parentCategoryHTML = '';
$subMenuHTML = '';
foreach ($menuCategories as $row) {
    if ($row['id'] == $nr) {
        $parentCategoryHTML = sprintf($parentCategoryFormat, $row['id'], htmlentities($row['name'])); // Render parent category
    } else {
        $subMenuHTML .= sprintf($subCategoryFormat, $row['id'], htmlentities($row['name'])); // Render subcategory
    }
 }

 echo $parentCategoryHTML;
 if (!empty($subMenuHTML)) {
      echo "<ul>{$subMenuHTML}</ul>";
 }