查询以使用不同的表构建类别和子类别导航

Question

I searched for this on older post of stackoverflow questions but i can't find the right solution for my problem here.

I have 2 tables in database:

article_category table:

cat_id   category
1        Elektronics
2        Furniture

article_subcategory table:

id   cat_id   subcategory            total
1    1        Kitchen-elektronics      1
2    1        Computers                5
3    2        Kitchen furniture        2
4    2        Living room furniture    7

What i want to do is to build a query for outputting the data in the HTML table like this:

                   <li>
                      <a href="#">
                         <div class="clearfix"><span class="pull-left">category items</span> <span class="pull-right">(total items)</span></div>
                      </a>

                      <ul class="test">

                         <li class="current"><a href="#">subcategory items (total items)</a></li>

                      </ul>
                   </li>

The total items in the first li must be the total items from all subcats underneath the head category. I have made a basic query but i see only the first subcategory item underneath the head category here. If it is possible, i want to do it without the use of functions here, but something in pure PHP.

SELECT 
   sc.subcategory,
   sc.total
   ac.category
FROM 
   article_category ac
JOIN
   article_subcategory sc
ON
   sc.cat_id = ac.cat_id
GROUP BY
   ac.cat_id

---

EDIT: FINAL SOLUTION

<?php
         //Make connection to db

$sql = "
            SELECT 
               ac.category,
               sc.aantal,
               sc.subcategory ,
               group_concat(sc.subcategory) as items,
               group_concat(sc.aantal) as totals,
               SUM(sc.aantal) as mainItemsTotal,
               group_concat(CONCAT(sc.subcategory, '-', sc.aantal)) as items2
            FROM 
               article_category ac
            JOIN
               article_subcategory sc
            ON
               sc.cat_id = ac.cat_id
            GROUP BY
               ac.cat_id
            ORDER BY 
               ac.category asc, sc.subcategory asc
            "; 
$res = mysql_query($sql) or die (mysql_error());


         while($row = mysql_fetch_assoc($res)) {

?>
        <ul>
                <li><?php echo $row['category']; ?> (<?php echo $row['mainItemsTotal']; ?>)</li>
                        <?php 
                        $items = $row['items2'];
                        $items_array = explode(',',$items);

                        if(is_array($items_array) && count($items_array) > 0 ){
                        ?>
                            <ul>
                                <?php 
                                foreach($items_array as $item_name){ 

                                    $arr = explode('-',$item_name);
                                    if($arr[1] > 0) { ?>
                                       <li>
                                           <?php echo $arr[0] . ' ('. $arr[1]. ')'; ?>
                                       </li>
                                <?php 
                                    } 
                               } 
                               ?>
                            </ul>
                        <?php } ?>
        </ul>
<?php } ?>

Now i have this output:

Elektronics (6)
   Kitchen-elektronics (1)
   Computers (5)

Furniture (9)
   Kitchen furniture (2)
   Living room furniture (7)

And that is all correct. Subitems with total = 0 are not shown.

Answer 1

You are close. Just SUM the total.

Below is the updated query where

GROUP_CONCAT(sc.total) AS subItemsTotal will give you comma separated total for each sub item.

SUM(sc.total) AS mainItemsTotal will give you total items of all sub cats

   SELECT 
    ac.category,
    GROUP_CONCAT(sc.subcategory) AS items,
    GROUP_CONCAT(sc.total) AS subItemsTotal,
    SUM(sc.total) AS mainItemsTotal
FROM 
    article_category ac
JOIN
    article_subcategory sc
ON
    sc.cat_id = ac.cat_id
GROUP BY
    ac.cat_id

Working Fiddle