How to fetch categories, articles and subcategories and their articles?

Question

I want to output the category names, the subcategory names in each category, and the articles in each category and in each subcategory.

The output should be in <ul> and inner <ul> tags in HTML.

Here is how the output should look like:

I have these 5 tables:

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, ...)

I have made this SQL so far:

  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

Answer 1

Instead of doing it in a single query, I would suggest a solution of looping over separate, multiple queries.

For your case, only 3 separate queries are needed because you have 3-tier (Cat/Subcat/Article). On the other hand, since the number of tiers is already defined (and limited), there is no need to use recursion this time (if there are unlimited (or unknown) levels of hierarchy, we need recursion).

Please change the database schema to a proper, and clearly defined one (as follows):

category (catid, name)
subcategory (subcatid, catid, name)
article (articleid, catid, subcatid, name)

where catid is linking to category; and subcatid is linking to subcategory, if applicable

So the data will be (refering to the picture in your post)

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

I think the advantage and clarity is obvious in the above schema, so I don't think further explanation is needed. Of course you may add further data fields to the tables but the main structure is as above.

Now, please use PHP to generate the following HTML:

For 1st ul, li (just run once)

(a) loop over category and display the name

then

(b) loop over article display records having (catid=0 or catid is null) and (subcatid=0 or subcatid is null) , then display the name

2nd ul,li

For each of the records in (a) above:

(c) loop over subcategory which as catid=category.catid and display the subcategory name

then

(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:

http://www.createchhk.com/SO/testso10Nov2021.php

Answer 2

When I look at your desired output, what I see is a tree with leaves that may or may not link to an article. A leaf that doesn't have an article attached to it can be thought of as a category. A leaf that does have an article can be thought of as an article.

With this assumption, you can build your schema:

Category_Tree  Category      Article
------------   --------      -------
id             id            id
pid            category_id   article_id
article_id     language_id   language_id
category_id    title         title

(Granted, this isn't in 4th normal form. you can normalize if needed, but for now it would be premature optimization)

Now, it should be easier to see how to tackle your problem: all you have to do is first build the tree in a php array; then iterate through that array and individually query the details for each leaf (or at least for what you need for your link's href and text).

A simple recursion primer:

<?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);

*Caveat: this doesn't translate directly to unordered lists. I dislike mixing html and logic, but in this case you would need to build a string inside the recursion. This is a more focused question you could ask or research.

Now, you iterate through $idList and look up the needed information to fill in your desired title

psuedocode (db retrieval again left to the reader):

// $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;  
}

Then, when you're outputting your html, you just iterate through $idList again and put in the values as needed.

Obviously, this isn't a cut and paste answer; there's way too much coding to do. But hopefully this points you into a workable direction.