red_bairn red_bairn - 2 months ago 8
MySQL Question

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

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>


MySql statement looking for the topic name and sub topic name in a table. Needs distinct values.
MySql statement looking for the topic name and sub topic name in a table. Organised by the topic and looking for distinct values.
List of topics and subtopics displayed on the page with PHP

Answer

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>';
}
Comments