Query to build a category and subcategory navigation with different tables

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.

You can use something like the one I have answered. If that doesn't work out for you, you may ping me and I am happy to reopen it. Also, I would suggest you might make both the cat and subcat tables inside a single table. — Praveen Kumar Purushothaman, Dec 18 '16 at 20:51
I don't see the total items for the head category and the subcategories. I don't see how to build this in php, if my query is right of course. Can you help me a hand here? — Brecht27, Dec 18 '16 at 20:57
Yes, definitely. You don't need joins. Also, let me open the question again and see if someone else have a different view. — Praveen Kumar Purushothaman, Dec 18 '16 at 20:58
I have already reopened the question. Refresh the page. `:)` — Praveen Kumar Purushothaman, Dec 18 '16 at 21:00
I don't know what you want to say. I have refreshed the page here and in your old post but i don't think anything has changed?? — Brecht27, Dec 18 '16 at 21:05
No, I asked you to refresh the page to see this question is reopened. `:)` — Praveen Kumar Purushothaman, Dec 18 '16 at 21:06
Ow, did you mean my question here? You give me a duplicate status but now you removed it? — Brecht27, Dec 18 '16 at 21:06
I have edited my question here. I want to do it without the use of functions if it is possible. — Brecht27, Dec 18 '16 at 21:08
Sure buddy... But it won't be effective. I might be wrong. Even I am eager to see. — Praveen Kumar Purushothaman, Dec 18 '16 at 21:12
I can't use a self-referencing table (cats and subcats in one table) because the tables are part of an admin system build years ago. I can't change that. So i must work with my 2 tables. — Brecht27, Dec 20 '16 at 10:26
I have found a solution for a part of my question. See my edit. Can anyone help me to complete my question now? — Brecht27, Dec 20 '16 at 11:45
I am not sure to understand what you want. Can you give us with the example data the expected result data ? — Daniel E., Dec 20 '16 at 11:54
@Praveen Kumar: final solution is found without functions ;-) See my edited post. — Brecht27, Dec 20 '16 at 14:18
@Brecht27 Thanks for this solution dude. Gonna upgrade my knowledge on this. — Praveen Kumar Purushothaman, Dec 20 '16 at 14:33
@Praveen Kumar: is it possible you can look why my order by is not working in the query? If i resfresh the page the sorting is different... — Brecht27, Dec 20 '16 at 15:32
@Praveen Kumar: is it possible to look at my other post, simular to this one but a little different: http://stackoverflow.com/questions/41250000/query-to-build-a-messages-system-from-different-joined-tables — Brecht27, Dec 20 '16 at 19:43

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

2

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

edited Jun 20 '20 at 09:12

Community

1
1

answered Dec 20 '16 at 12:05

Samir Selia

7,007
2
11
30

And what about the totals for each subitem? I think it must be done in the $item_array? – Brecht27 Dec 20 '16 at 12:10
Have updated my answer based on latest edit in your post. – Samir Selia Dec 20 '16 at 12:13
The part of SUM(sc.total) AS mainItemsTotal is working correct. But what to do with the GROUP_CONCAT(sc.total) AS subItemsTotal in my $items_array? How can i output that in the HTML (li elements)? – Brecht27 Dec 20 '16 at 12:23
1

explode it with comma just like you did for items array and show it based on array index – Samir Selia Dec 20 '16 at 13:22
Thanks Samir for your comment and helping me out here. I have updated my post with the final solution working. – Brecht27 Dec 20 '16 at 14:14
Why don't you use ROLLUP ? – Daniel E. Dec 20 '16 at 14:25
Never heard before of ROLLUP? What is different with the code i have edited? – Brecht27 Dec 20 '16 at 14:34
Is it possible to look at the ORDER BY in my final solution. Sometimes i refresh the page, the sorting is different?? – Brecht27 Dec 20 '16 at 14:35
@Samir: can you look also to my other post simular to this one but a little different: http://stackoverflow.com/questions/41250000/query-to-build-a-messages-system-from-different-joined-tables – Brecht27 Dec 20 '16 at 19:42

score 0 · Answer 2 · answered Dec 21 '16 at 12:05

I have an other solution, you will have to change your php code so maybe you won't use it. But I think in a sql way it's a better solution :

I use the group by modifier "WITH ROLLUP" wich do what you ask for : http://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html

Query :

SELECT 
    ac.category,
    sc.subcategory AS items,
    SUM(sc.total) AS Total
FROM 
    article_category ac
JOIN
    article_subcategory sc ON sc.cat_id = ac.cat_id
GROUP BY ac.category,items WITH ROLLUP

Result :

|----------------------------------------|
|category   |items                |Total |
|----------------------------------------|
|Elektronics|Computers            |5     
|Elektronics|Kitchen-elektronics  |1
|Elektronics|NULL                 |6
|Furniture  |Kitchen furniture    |2
|Furniture  |Living room furniture|7
|Furniture  |NULL                 |9
|NULL       |NULL                 |15

Query to build a category and subcategory navigation with different tables

2 Answers2