我想輸出類別名稱，每個類別中的子類別名稱，以及每個類別和每個子類別中的文章。
輸出應該是HTML 中的in<ul>和 inner<ul>標簽。
下面是輸出的樣子：
我有這 5 個表：
table_article_categories(articlecat_id, parent_id, articlecat_status, ...)
table_article_to_article_categories(articlecat_id, articles_id)
table_articles(articles_id, articles_status, ...)
table_articles_description(articles_id, language_id, articles_heading_title, ...)
table_article_categories_description(articlecat_id, articlecat_name, language_id, ...)
到目前為止，我已經制作了這個 SQL：
SELECT
ac.parent_id,
ac.articlecat_id,
a.articles_id,
ad.articles_heading_title AS article_name,
acd.articlecat_name
FROM
table_article_categories AS ac
LEFT JOIN
table_article_to_article_categories AS atac
ON
atac.articlecat_id = ac.articlecat_id
LEFT JOIN
table_articles AS a
ON
a.articles_id = atac.articles_id
AND
a.articles_status = 1
LEFT JOIN
table_articles_description AS ad
ON
ad.articles_id = a.articles_id
AND
ad.language_id = 1
INNER JOIN
table_article_categories_description AS acd
ON
acd.articlecat_id = ac.articlecat_id
AND
acd.language_id = 1
WHERE
ac.parent_id = 99 # --- This is the root article-categori-id ---
AND
ac.articlecat_status = 1
AND
ac.articlecat_id != 77 # --- This is an excluded article-categori-id ---
ORDER BY
acd.articlecat_name ASC,
article_name ASC
uj5u.com熱心網友回復：
當我查看您想要的輸出時，我看到的是一棵樹，葉子可能會或可能不會鏈接到文章。沒有附加文章的葉子可以被認為是一個類別。有文章的葉子可以被認為是文章。
有了這個假設，您可以構建您的架構：
Category_Tree Category Article
------------ -------- -------
id id id
pid category_id article_id
article_id language_id language_id
category_id title title
（當然，這不是第四范式。如果需要，您可以標準化，但現在這將是過早的優化）
現在，應該更容易了解如何解決您的問題：您所要做的就是首先在 php 陣列中構建樹；然后遍歷該陣列并單獨查詢每個葉子的詳細資訊（或至少查詢鏈接的 href 和文本所需的內容）。
一個簡單的遞回入門：
<?php
/*
Category_Tree
id pid name article_id category_id
1 0 Category 1 ... ...
2 1 Article 1
3 0 Category 2
4 3 SubCategory 1
5 4 Article 7
6 4 Article 8
7 3 Article 2
8 3 Article 3
9 0 Category 3
10 9 Article 4
11 9 Article 5
12 0 Article 6
*/
function getRecord($pid) : array
{
// database access is left to the reader
// $db->run('SELECT * FROM CATEGORY_TREE WHERE PID=?',[$pid]);
// $rows = [];
// while($row = $db->getRow()) {
// $rows[] = $row;
// }
return $rows;
}
function makeTree($pid) : array
{
static $idList = [];
static $indent = 0;
$rows = getRecord($pid);
foreach ($rows as $row) {
$idList[] = [
'id' => $row->id,
'indent' => $indent,
'articleId' => $row->articleId,
'categoryId' => $row->categoryId,
];
$indent ;
makeTree($row->id);
$indent--;
}
return $idList;
}
$idList = makeTree(0);
*警告：這不會直接轉換為無序串列。我不喜歡混合 html 和邏輯，但在這種情況下，您需要在遞回中構建一個字串。這是您可以提出或研究的更集中的問題。
現在，您遍歷$idList并查找所需的資訊以填寫您想要的標題
偽代碼（資料庫檢索再次留給讀者）：
// $languageId is already assigned as you see fit
// add title to $idList
foreach($idList as $index => $row) {
if($row->articleId ?? FALSE) {
$db->run('SELECT TITLE FROM ARTICLE WHERE ARTICLE_ID=? AND LANGUAGE_ID=?', [$row->articleId, $languageID]);
} else {
$db->run('SELECT TITLE FROM CATEGORY WHERE CATEGORY_ID=? AND LANGUAGE_ID=?', [$row->categoryId, $languageId]);
}
$row->title = $db->get('title');
$idList[$index] = $row;
}
然后，當您輸出 html 時，您只需$idList再次迭代并根據需要輸入值。
顯然，這不是一個剪切和粘貼的答案；有太多的編碼要做。但希望這會為您指明一個可行的方向。
uj5u.com熱心網友回復：
我建議不要在單個查詢中執行此操作，而是建議使用回圈處理單獨的多個查詢的解決方案。
對于您的情況，只需要 3 個單獨的查詢，因為您有 3 層（Cat/Subcat/Article）。另一方面，由于層的數量已經定義（并且是有限的），所以這次不需要使用遞回（如果有無限（或未知）的層次結構，我們需要遞回）。
請將資料庫架構更改為適當且明確定義的架構（如下）：
category (catid, name)
subcategory (subcatid, catid, name)
article (articleid, catid, subcatid, name)
哪里catid is linking to category；和subcatid is linking to subcategory，如果適用
所以資料將是（參考你帖子中的圖片）
category (catid, name)
1 Category1
2 Category2
3 Category3
subcategory (subcatid, catid, name)
1 2 Subcategory1
article (articleid, catid, subcatid, name)
1 0 0 Article6
2 1 0 Article1
3 2 1 Article7
4 2 1 Article8
5 2 0 Article2
6 2 0 Article3
7 3 0 Article4
8 3 0 Article5
我認為上述模式的優勢和清晰度是顯而易見的，因此我認為不需要進一步解釋。當然，您可以向表中添加更多資料欄位，但主要結構如上。
現在，請使用 PHP 生成以下 HTML：
對于第一個 ul, li (只運行一次)
(a) 遍歷類別并顯示名稱
然后
(b) 回圈具有 (catid=0 or catid is null) 和 (subcatid=0 or subcatid is null) 的文章顯示記錄，然后顯示名稱
第二個 ul,li
對于上述 (a) 中的每條記錄：
(c) 遍歷子類別，其中 catid=category.catid 并顯示子類別名稱
然后
(d) loop over article, display records having catid=category.catid and (subcatid=0 or subcatid is null) , then display the name
3rd ul,li
For each of the records in (c) above:
(e) loop over article, display records having catid=category.catid and subcatid=subcategory.subcatid , then display the name
To demonstrate, I will put the "2nd ul, li" as a function known as branch2(); and "3rd ul, li" as a function known as branch3(), and then call them accordingly.
So, the code will be:
<?php
$servername = "localhost";
$username = "xxxxxxx";
$password = "xxxxxxxxxx";
$dbname = "xxxxxx";
$conn = new mysqli($servername, $username, $password, $dbname);
$sql1="(
SELECT name, catid AS id1, '1' as subdata
FROM category
ORDER BY id1
)
UNION (
SELECT name, articleid AS id1, '0' as subdata
FROM article
WHERE catid =0
AND subcatid =0
ORDER BY id1
)";
$result = $conn -> query($sql1);
$index=0;
$count=mysqli_num_rows($result);
echo "<ul>";
while ($index <$count) {
$row = $result -> fetch_assoc() ;
echo "<li>". $row["name"];
if ($row["subdata"]=='1'){
branch2($row["id1"], $conn);
}
$index ;
}
echo "</ul>";
$result -> free_result();
function branch2($var1, $conn){
$sql2="(
SELECT name, catid AS id1, subcatid as id2, 1 as subdata
FROM subcategory where catid=". $var1 . " ORDER BY id1 )
UNION (
SELECT name, articleid AS id1, 0 as id2 , 0 as subdata
FROM article
WHERE catid =" . $var1 . " AND subcatid =0 ORDER BY id1 )";
$result2 = $conn -> query($sql2);
$index2=0;
$count2=mysqli_num_rows($result2);
echo "<ul>";
while ($index2 <$count2) {
$row2 = $result2 -> fetch_assoc() ;
echo "<li>". $row2["name"];
if ($row2["subdata"]=='1') {
branch3($row2["id1"], $row2["id2"], $conn);
}
$index2 ;
}
echo "</ul>";
$result2 -> free_result();
}
function branch3($var1, $var2, $conn){
$sql3="(
SELECT name, articleid AS id1, 0 as id2 , 0 as subdata
FROM article
WHERE catid =" . $var1 . " and subcatid=". $var2 . " ORDER BY id1 )";
$result3 = $conn -> query($sql3);
$index3=0;
$count3=mysqli_num_rows($result3);
echo "<ul>";
while ($index3 <$count3) {
$row3 = $result3 -> fetch_assoc() ;
echo "<li>". $row3["name"];
$index3 ;
}
echo "</ul>";
$result3 -> free_result();
}
?>
The fully working example can be seen here:
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/355540.html
上一篇：如果滿足特定條件，如何復制表格？