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

CodePudding user response：

Assuming that you are willing to change the database schema (as follows):

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

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

Then, please use PHP to generate the following HTML:

For 1st ul, li

(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

For 2nd ul,li

For each of the records in (a) above:

then

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

For 3rd ul,li

For each of the records in (c) above:

(e) loop over article, display records having catid=subcategory.catid and subcatid=subcategory.subcatid , then display the name

CodePudding user response：

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:

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

(more explanation coming, but it will take some time to create...)