如何获取某个类别中表的所有行，然后从每一行中显示一个字段，但将它们按子类别划分

Question

我正在工作的一个网站，显示的域名出售，所有已被归类为约35个主要类别之一。每个要出售的域名也属于该主类别的一个子类别。每个主要类别有1至10个子类别。

目前，该网站只是列出所有的域名在数据库中的主类别选择。(它实际上显示了所选主类别的第一个子类别中的所有名称，然后显示了该主类别中的所有其他子类别域名)。

数据库中只有一个表。它有4列，大约10,000行长，每个主类别中大约有35个主类别和几个子类别。除了id (我想)，它都是文本。

以下是表的一部分：

Id   MainCat  SubCat           DomainName
1    Art      Art Companies    ArtCompanies.com
2    Beauty   Beuauty Care     CareBeauty.com
3    Art      Art Galleries    ArtInAmerica.com
4    Art      Art Companies    ArtInChicago.com
5    Art      Art Galleries    ArtMuseumsInLA.com
6    Beauty   Beauty Products  BeautyIdeas.com
7    Art      Artists          ArtMuseumsNY.com
8    Business Trademarks       TrademarksUSA.com

下面是php/mysql代码的简化版本，该代码目前检索选定主类别中的所有域名和该主类别中的第一个子类别。这是“键盘”图像下面文本顶部的代码：

$res_sub = $mysqliConnection->query("SELECT * FROM {$table} WHERE MainCategory = '$cellMainCat' AND SubCategory = '$cellSubCat' ORDER BY SubCategory ASC") or die(mysqli_error());
echo "You are viewing <div class='catFormat'>$cellSubCat</div> in <div class='catFormat'>$cellMainCat</div>";

/* determine number of rows in result set */
$total_number_of_rows_returned = $res_sub->num_rows;
$number_of_rows_remaining = 0;

while ($number_of_rows_remaining < ($total_number_of_rows_returned/2) && (boolean) ($row = mysqli_fetch_object($res_sub))) 
{
$number_of_rows_remaining++;  // increment 
    echo $row->DomainName;
}
/* now doing to do the RIGHT SIDE MIDDLE div and put the right side results in.  We don't need to do the count half thing cause there should only be half left */

while ($row = mysqli_fetch_object($res_sub)) 
{
echo $row->DomainName;
    echo "<br>";
}

下面是到该网站的链接，现在是这样的：

v40.php

我的问题:我想修改网站，以便当用户单击按钮选择主类别时，页面显示该主类别中的所有域名，但按子类别进行细分。

因此，例如，如果用户单击"Art“的主类别按钮，页面将显示：

You have chosen the Main Category Art which has 8 Subcategories.  

Subcategory 1 of 8 is:  Art Galleries.  The domain names for sale for 
this subcategory are:
             myartgalleries.com
             bigartgalleries.com
             superartgaleries.com

Subcategory 2 of 8 is:  Art Companies.  The domain names for sale for 
this subcategory are:
            bigartcompanies.com
            superartcompanies.com

       [etc. - end example]

我想知道的是，使用什么代码循环遍历子类别，并一次显示一个子类别，如上面的示例所示。如果我不需要查询每个子类别就可以逃脱，那就太好了，但是我对php/mysql还不太了解，也不知道这是否可能。我不介意数组等等。

任何帮助都将不胜感激。

谢谢。

Answer 1

您可以使用GROUP_CONCAT()一次获取所有由逗号分隔并按子类别分组的域名。

SELECT subcat,
       GROUP_CONCAT(domainname) domainname
  FROM domainnames
 WHERE maincat = 'Art'
 GROUP BY subcat

输出：

|        SUBCAT |                          DOMAINNAME |
-------------------------------------------------------
| Art Companies |   ArtCompanies.com,ArtInChicago.com |
| Art Galleries | ArtInAmerica.com,ArtMuseumsInLA.com |
|       Artists |                    ArtMuseumsNY.com |

这是SQLFiddle演示

并执行所有操作，以便在php中构建UI，如下所示

$maincat = "Art";

$db  = new mysqli('localhost', 'user', 'password', 'dbname') or die('Cannot connect');
$sql = "SELECT subcat,
               GROUP_CONCAT(domainname) domainname
          FROM domainnames
         WHERE maincat = ?
         GROUP BY subcat";  

$query = $db->prepare($sql) or die('Unable to prepare: ' . $db->error);
$query->bind_param("s", $maincat);
$query->execute();
$query->bind_result($s, $d);

$subcategories = array();
while ($query->fetch()) {
    $subcategories[$s] = explode(',', $d);
}
$db->close();

$sub_count = count($subcategories);
echo "<p>You have chosen the Main Category $maincat which has $sub_count Subcategories.</p>";

$i = 1;
foreach ($subcategories as $sub_name => $domainnames) {
    echo "<p>Subcategory $i of $sub_count is: $sub_name.<br>";
    echo "The domain names for sale for this subcategory are:</p>";
    echo "<ul>";
    foreach ($domainnames as $domain_name) {
        echo "<li>$domain_name</li>";
    }
    echo "</ul>";
    $i++;
}

Answer 2

SELECT name,category,subcategory FROM table WHERE category ='art' GROUP BY subcategory 

。。应该这么做。