如何显示类别及其子类别

Question

我有一张这样的桌子

cat:
----
cat_id    name
---------------
1        cat-1
2        cat-2 


sub-cat:
--------

id    cat_id    name
-------------------------
1       1       sub-cat-1
2       1       sub-cat-2
3       2       sub-cat-3
4       2       sub-cat-4

我需要像使用PHP这样的输出

cat-1
 sub-cat-1
 sub-cat-2
cat-2
 sub-cat-3
 sub-cat-4

我的问题是

SELECT * FROM `cat` 
        JOIN `sub-cat`  ON cat.cat_id = sub-cat.cat_id

Answer 1

我最终用smarty编写了这段代码

{assign var=foo value=''}

{foreach from=$blog_lists key=k item=blog }
          {if $foo neq $blog ->category_id}
              {$blog->category_name}<br />
              {assign var=foo value=$blog->category_id}
          {/if}

          {$blog->subcat_name}<br />

{/foreach}

Answer 2

您可以使用以下代码

$sql = mysqli_query("SELECT * FROM `cat` ");
while($row = mysqli_fetch_array($sql))
{
   echo "cat ".$row['name'];
   $sql1 = mysqli_query("SELECT * FROM `sub-cat` where id = ".$row['cat_id']);
   while($row1 = mysqli_fetch_array($sql1))
   {
        echo "sub-cat ".$row1['name'];
   }
}

Answer 3

使用下面的代码

function tree($pid='',$new_ar=array())
{
if($pid!="")
{
    $new_ar1=array();
    $sel="select name from subcat where cat_id=".$pid." order by cat_id";
    $mq=mysql_query($sel);
    while($fe=mysql_fetch_assoc($mq))
    {
        array_push($new_ar1,$fe["name"]);
    }
    return $new_ar1;
}
else
{
    $sel="select name from cat order by name";
    $mq=mysql_query($sel);
    while($fe=mysql_fetch_assoc($mq))
    {
        array_push($new_ar,$fe["name"]);

        $new_ar[$fe["name"]]=array();

        array_push($new_ar[$fe["name"]],tree($fe["name"],$new_ar));

    }
}
//die($sel);
//echo "<pre>";
//print_r($new_ar);
//echo "<pre>";
    return $new_ar;
}

调用函数

tree();