php - 如果 LEFT OUTER JOIN 找到行，则创建子 项

Question

我在将 mysql 数据拖到所需的 HTML 格式时遇到问题，我将尝试给出一个简化的示例来说明我所拥有/需要的内容:

示例表:

CREATE TABLE IF NOT EXISTS `tblcat` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `catname` varchar(100) DEFAULT NULL,
  `comments` text NOT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
)

CREATE TABLE IF NOT EXISTS `tblsubcat` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `subcatname` varchar(100) DEFAULT NULL,
  `catid` int(10) DEFAULT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
)

示例数据:

INSERT INTO `tblcat` (`id`, `catname`, `comments`, `enabled`) VALUES
(100, 'Bells & Horns', '', 0),
(101, 'Bottles & Bottle Cages', '', 0),
(216, 'Brake Blocks - Shimano', '', 0),
(243, 'Brake Discs & Accessories', '', 0)

INSERT INTO `tblsubcat` (`id`, `subcatname`, `catid`, `enabled`) VALUES
(194, 'Bottom Bracket Axles', 102, 0),
(204, 'Bottom Bracket Parts', 102, 0)

目标 HTML:

<li><a href="viewproducts.php?task=showsubs&catID=100">Bells & Horns</a></li>
<li><a href="viewproducts.php?task=showsubs&catID=101">Bottles & Bottle Cages</a></li>
<li>
<a href="viewproducts.php?task=refine&catID=102" class="extends">Bottom Brackets & Accessories</a>
<ul class="subcategory">
  <li><a href="viewproducts.php?task=refine&catID=102&subcatID=194">Bottom Bracket Axles</a></li>
  <li><a href="viewproducts.php?task=refine&catID=102&subcatID=204">Bottom Bracket Parts</a></li>
</ul>
</li>
<li><a href="viewproducts.php?task=showsubs&catID=216">Brake Blocks - Shimano</a></li>
<li><a href="viewproducts.php?task=showsubs&catID=243">Brake Discs & Accessories</a></li>

到目前为止我的尝试:

$CurrentCatID = 0;
$Categories = mysql_query("SELECT tblcat.id as CatID, tblcat.catname as CatName, IFNULL(tblsubcat.id, -1) as SubCatID, IFNULL(tblsubcat.subcatname, '') as SubCatName FROM tblcat LEFT OUTER JOIN tblsubcat ON tblcat.id = tblsubcat.catid WHERE tblcat.enabled = '0' ORDER BY tblcat.catname ASC, tblsubcat.subcatname ASC");  
$CurrentCatID = mysql_result($Categories, 0, "CatID");
while ($row = mysql_fetch_assoc($Categories)) {
    echo "<li><a href=\"viewproducts.php?task=showsubs&catID=" . $row["CatID"] . "\">" . htmlspecialchars($row["CatName"]) . "</a></li>";
}

如何扩展上述 PHP 来创建上述目标 HTML？我已经尝试过各种复杂的向后和向前循环方法，这些方法都很丑陋，而且我确信必须有一种更简洁的方法来做到这一点 - 所以我想在这里问:)

编辑:

我的最终工作版本:

$InSub = false;
$PreviousCatID = "";
$Categories = mysql_query("SELECT tblcat.id as CatID, tblcat.catname as CatName, IFNULL(tblsubcat.id, -1) as SubCatID, IFNULL(tblsubcat.subcatname, '') as SubCatName FROM tblcat LEFT OUTER JOIN tblsubcat ON tblcat.id = tblsubcat.catid WHERE tblcat.enabled = '0' ORDER BY tblcat.catname ASC, tblsubcat.subcatname ASC");  

while ($row = mysql_fetch_assoc($Categories)) {
    if ($InSub === true && ($row["SubCatID"] === "-1" || $PreviousCatID !== $row["CatID"])) {
        echo "  </ul>\n";
        echo "</li>\n";
        $InSub = false;
    }        
    if ($row["SubCatID"] === "-1") {
        echo "<li><a href=\"viewproducts.php?task=showsubs&catID=" . $row["CatID"] . "\">" . htmlspecialchars($row["CatName"]) . "</a></li>\n";  
    } else {
        if ($PreviousCatID !== $row["CatID"] || $InSub === false) {
            echo "<li><a href=\"viewproducts.php?task=showsubs&catID=" . $row["CatID"] . "\" class=\"extends\">" . htmlspecialchars($row["CatName"]) . "</a>\n";
            echo "  <ul class=\"subcategory\">\n";
            $InSub = true;
        }
        echo "    <li><a href=\"viewproducts.php?task=showsubs&catID=" . $row["CatID"] . "&subcatID=" . $row["SubCatID"] . "\">" . htmlspecialchars($row["SubCatName"]) . "</a></li>\n";
    }
    $PreviousCatID = $row["CatID"];
}

Answer 1

执行“到目前为止的尝试”的更简洁的方法:

首先，我建议将您的查询设为存储过程。这将清理您的代码，并提高性能，因为它将被编译。

如果 SP 不是一个选项，至少将查询分配给上面的常量值，然后使用 mysql_query(CONST_QRY); 。 。 。因为您没有动态构建 SQL 的变量。

其次，您对结果集的循环并不是非常“丑陋”，但您通常不希望在服务器端代码中构建“ View ”或客户端 HTML。您可以使用 JQuery 和 Ajax 向服务器端 PHP 代码发出请求，然后服务器端 PHP 代码将执行查询，并将结果返回到客户端代码进行显示。

<小时/>

编辑

<小时/>

关于您的评论，我误解了您问题的这一部分。抱歉。

我建议使用一个 bool 值来判断您当前是否处于子级别。另一个判断最后一次循环迭代是否在子级别中。下面的代码是其工作原理的概念。

$blnSub = false;
$prevSubId = "";
while(...){
   $blnEndSub = $blnSub;
   $blnSub = ($row["subId"] == null);

   //if not in a sub, but was previously in sub, end the UL
   if($blnSub == false && $blnEndSub == true) {
      echo "</ul>";
   //if in sub but was not previously in sub, start UL
   } else if ($blnSub == true && $blnEndSub == false) {
      echo "<ul>";
   //if in a sub.  previously in sub.  new sub id.
   } else if ($blnSub == true && $blnEndSub == true && $prevSubId != $row["subId"]) {
      echo "</ul><ul>";
   }

   if($blnSub == true) {
        echo "<li class=sub><ahref...etc...></a></li>";
   } else {
        echo "<li><ahref...etc...></a></li>";
   }

   $prevSubId = $row["subId"];
}