表中包含子类别的类别 - PHP + MYSQL [英] Categories with Subcategories in table - PHP + MYSQL

问题描述

我的数据库中有分类和子类别。数据库表包含cid，pid和cname列。

I have Categories and Sub-Categories on my database. Database table contain "cid" , "pid" and "cname" columns.

Cid =类别ID

Cid = Categories ID

Pid =如果Category是子类别，则Pid =属于哪个类别。

Pid = If Category is a Sub-Category , then Pid = that belong which category.

Cname =类别名称

Cname = Category Name

例如软件是一个类别，id = 15，IOS软件是一个子类别，id = 30，pid = 15

For Example Software is a category and id = 15 and "IOS Software is a sub-category and id= 30 and pid = 15

现在我要展示这个Cat。和Sub-Cat在表中。这是我的代码：

Now I want to show this Cat. And Sub-Cat in a table. Here is my code:

   <?php
  function categories() {
  $categorysql = $DB['DB_Database']->query("SELECT cid, pid, cname FROM categories GROUP BY cid");
       while ($row = $DB['DB_Database']->fetch_assoc($categorysql))
      {
          if ($row['pid'] > 0 {
                      }
                       else {
                              $catid = $row['cid'];
                              $catname = $row['cname']; }
  }

机智这个代码我只能显示主要类别。此处的示例图片：

with this code I can show only Main Categories. Sample Pics here:

但我希望这样显示：

我的表格代码如下：

<table width="268" border="1">
  <tr>
    <td width="52" rowspan="2" valign="top">Image</td>
    <td width="200" height="23"><a href="{$catid}">{$catname}<a></td>
  </tr>
  <tr>
    <td height="36" valign="top">This ara for sub cat.</td>
  </tr>
</table> 

那么我怎么能这样做，任何想法？

So how can I do that, any idea?

推荐答案

我建议更改您的PHP代码以使用以下使用 OUTER JOIN 的SQL：

I would suggest changing your php code to work with the following sql that uses an OUTER JOIN:

select c.cname, c2.cname subname
from categories c
  left join categories c2 on c.cid = c2.pid
where c.pid is null

• SQL小提琴演示


• SQL Fiddle Demo

这假设父亲的pid字段为空。或者，您可以使用 GROUP_CONCAT 在一行中返回所有子类别而不是多行 - 这可能更容易实现。

This assumes the parent's pid field is null. Alternatively you could use GROUP_CONCAT to return all your subcategories in a single row instead of multiple rows -- this might be easier for your implementation.

这篇关于表中包含子类别的类别 - PHP + MYSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！