How can I group subtopics into topics in this MySQL statement?

Question

I've tried GROUP BY with my data below but it only brings back one subtopic. How can I return all the subtopics and organise them under each topic without the topic_name appearing with each subtopic_name.

Edit: Included a screenshot of the page and here is the PHP used:

            <ul class="topics-list">
            <?php
            foreach ($data as $key){
                foreach ($key as $item){ 
                    $topic_name = $item['topic_name'];
                    $subtopic_name = ucwords($item['subtopic_name']);
                ?>
                    <div class="the_topic">
                        <h2 class="topic_change"><?php echo $topic_name; ?></h2>
                        <ul><li class="subtopic_name"><a href="#"   data-toggle="modal" data-target="#lvlModal"><h3><?php echo $subtopic_name; ?></h3></a></li></ul>
                        <hr />
                    </div>
                <?php } ?>
            <?php } ?>
            </ul>

That is something you would do best directly in your client (e.g. php). — Solarflare, Sep 24 '16 at 15:29
Added the PHP, @Solarflare. I've added another screenshot. I want it to show General just once with the subtopics in a list underneath the topic. — red_bairn, Sep 24 '16 at 15:39
You have a lot of options to do that. E.g., in your loop, save the value of the last topic. Only if it is different for the next row, add the code for a new topic (and close the last), otherwise just add the subtopic line. — Solarflare, Sep 24 '16 at 15:53

Paul Spiegel · Accepted Answer · 2016-09-24T16:40:11.873

1

You could use GROUP_CONCAT() to concatenate all subtopics into one string per topic, and then parse the string in your application code.

SELECT topic_name, GROUP_CONCAT(subtopic_name DELIMITER '§§§') as subtopic_names
FROM questions2
GROUP BY topic_name

But i do not recommend that, because you will get in troubles, if a subtopic contains your delimiter. I would just use your second query and group the result in the application code.

PHP code would look something like:

// group the data
$groupedData = array();
foreach ($data as $item) {
    $topic_name = $item['topic_name'];
    $subtopic_name = ucwords($item['subtopic_name']);
    $groupedData[$topic_name][] = $subtopic_name;
}

// grouped output
foreach ($groupedData as $topic_name => $subtopic_names) {
    echo '<div class="the_topic">';
    echo '<h2 class="topic_change">' . $topic_name . '</h2><ul>';
    foreach ($subtopic_names as $subtopic_name) {
        echo '<li class="subtopic_name"><a href="#" data-toggle="modal" data-target="#lvlModal"><h3>';
        echo $subtopic_name;
        echo '</h3></a></li>';
    }
    echo '</ul><hr /></div>';
}

edited Sep 24 '16 at 16:40

answered Sep 24 '16 at 15:31

Paul Spiegel

30,925
5
44
53

Would you know what would be the best solution to amend the PHP in this case to group the subtopics? – red_bairn Sep 24 '16 at 15:47
Would you know how I could amend this to stop at say 5 subtopics and then another 5 beside the first column? I'm using Bootstrap for the columns. – red_bairn Sep 24 '16 at 16:27
@red_bairn You should ask a new question with the bootstrap tag. Or search for a solution on SO. – Paul Spiegel Sep 24 '16 at 16:46
Yeah no problem, I've used foreach (array_slice($subtopic_names,0,5) as $subtopic_name). – red_bairn Sep 24 '16 at 16:49
@red_bairn Here is a really good CSS solution: http://stackoverflow.com/questions/6509106/is-there-a-way-to-break-a-list-into-columns – Paul Spiegel Sep 24 '16 at 16:51

How can I group subtopics into topics in this MySQL statement?

1 Answers1