mySQL how to group the months by years

Question

I am doing a mobile app for consult the database
I have a table like this

I want to do a collapsible list from date_creation by year->month->day, like this

To do that, I need to put the loops in the nested collapsible blocks,
First I did this query for the years

$count_year = $bdd -> query
(
    'SELECT YEAR(date_creation) as y, COUNT(id) as nombre
     FROM rdv
     GROUP BY y
     ORDER BY y DESC'
);

HTML

<div data-role="main" class="ui-content">
    <h3>sort by date</h3>
    <?php foreach ($count_year as $key) { ?>
    <div data-role="collapsible">
        <h1><?php echo $key['y']; ?></h1>
    </div>
    <?php } ?>
</div>

which give me the years

then I tried this to get the months group by year

$count_month = $bdd -> query
(
    'SELECT YEAR(date_creation) as y, MONTH(date_creation) as m, COUNT(id) as nombre
     FROM rdv
     WHERE YEAR(date_creation) = YEAR(date_creation)
     GROUP BY y, m
     ORDER BY y DESC, m DESC'
);

HTML

<div data-role="main" class="ui-content">
    <h3>sort by date</h3>
    <?php foreach ($count_year as $key) { ?>
    <div data-role="collapsible">
        <h1><?php echo $key['y']; ?></h1>
        <?php foreach ($count_month as $key) { ?>
        <div data-role="collapsible">
            <h1><?php echo $key['m']; ?></h1>
        </div>
        <?php } ?>
    </div>
    <?php } ?>
</div>

In phpmyAdmin I get the result like this

which group the months by year, but the result in Chrome, it put all the months in one year, like this

I know I need to group the months in mySQL by WHERE YEAR(date_creation) = something present year dynamically, or maybe is just the html is wrong, cause the jQuery mobile CDN will create lots tags in DOM.
Any idea how to put the 12 to 2016?
And so on for the days group by month.

Answer 1

You don't need two queries. Just use one query and start a new year DIV whenever the year changes.

$count_month = $bdd -> query
(
    'SELECT YEAR(date_creation) as y, MONTH(date_creation) as m, COUNT(id) as nombre
     FROM rdv
     WHERE YEAR(date_creation) = YEAR(date_creation)
     GROUP BY y, m
     ORDER BY y DESC, m DESC'
);
$last_year = 0;
?>
<div data-role="main" class="ui-content">
    <h3>sort by date</h3>
    <?php 
    foreach ($count_month as $key) { 
        if ($key['y'] != $last_year) {
            if ($last_year) { // Close DIV for previous year if this is set ?>
                </div>
                <?php
            } ?>
            <div data-role="collapsible">
            <h1><?php echo $key['y']; ?></h1>
            <?php
            $last_year = $key['y'];
        }
        ?>
        <div data-role="collapsible">
            <h1><?php echo $key['m']; ?></h1>
            <div><?php echo $key['nombre']; ?></div>
        </div>
        <?php } ?>
    </div>
    <?php } ?>
</div>

Here's the version that adds days within months:

<?php
$count_month = $bdd -> query
(
    'SELECT YEAR(date_creation) as y, MONTH(date_creation) as m, DAY(date_creation) AS d, COUNT(id) as nombre
     FROM rdv
     WHERE YEAR(date_creation) = YEAR(date_creation)
     GROUP BY y, m, d
     ORDER BY y DESC, m DESC, d DESC'
);

?>
<div data-role="main" class="ui-content">
    <h3>sort by date</h3>
    <?php 
    $last_year = 0;
    foreach ($count_month as $key) { 
        if ($key['y'] != $last_year) {
            if ($last_year) { // Close DIV for previous year and month if this is set
                ?>
                </div> <!-- end month <?php echo $last_month; ?> -->
                </div> <!-- end year <?php echo $last_year; ?> -->
                <?php
            } ?>
            <div data-role="collapsible"> <!-- start year -->
            <h1><?php echo $key['y']; ?></h1>
            <?php
            $last_year = $key['y'];
            $last_month = 0;
        }
        if ($key['m'] != $last_month) {
            if ($last_month) { // Close DIV for previous month
                ?>
                </div> <!-- end month <?php echo $last_month; ?> -->
                <?php
            }
            $last_month = $key['m'];
            ?>
            <div data-role="collapsible"> <!-- start month -->
            <h2><?php echo $key['m']; ?></h2>
            <?php
        }
        ?>
        <div data-role="collapsible"> <!-- date -->
        <h3><?php echo $key['d']; ?></h3>
        <div><?php echo $key['nombre']; ?></div>
        </div> <!-- end date -->
        <?php }
    if ($last_month) { ?>

        </div> <!-- end month <?php echo $last_month; ?> -->
               <?php
               }
    if ($last_year) { ?>
        </div> <!-- end year <?php echo $last_year; ?> -->
               <?php }
    ?>
</div> <!-- end main -->

DEMO