List months by year in mysql

Question

Here’s what I’m trying to do php and mysql:

What is in parentheses is the number of results within each month. For this, I am saving in my database, in the table tbl_noticias the field data of the kind yyyy-mm-dd. I’m still guarding the field id among others(I don’t need to here). I’m not able to figure out how to get to this final result using mysql

Answer 1

I got there this way:

<?php
                    $sql = mysql_query("SELECT DISTINCT YEAR(data) AS ano, id AS id FROM tbl_noticias GROUP BY ano");
                    while($row = mysql_fetch_array($sql))
                    {
                        $ano = $row['ano'];
                        echo "Ano: ".$ano."<br>";
                        $sql1 = mysql_query("SELECT DISTINCT MONTH(data) AS mes, id AS id FROM tbl_noticias WHERE YEAR(data) = '$ano' GROUP BY mes");
                        while($row1 = mysql_fetch_array($sql1))
                        {
                            $mes = $row1['mes'];
                            echo "Mes: ".$mes." - ";
                            $sql2 = mysql_query("SELECT count(*) AS contagem FROM tbl_noticias WHERE YEAR(data) = '$ano' AND MONTH(data) = '$mes'");
                            $row2 = mysql_fetch_array($sql2);
                            echo $row2['contagem']."<br>";
                        }
                    }
                  ?>

Thank you!

Answer 2

tries to use this logic or even the code

$query=mysql_query("SELECT data, DATE_FORMAT(data,'%Y') as ano FROM tbl_noticias GROUP BY YEAR(data)");

$anos_meses="";
while ($row_anos = mysql_fetch_assoc($query)) 
{
    $ano = $row_anos["ano"];
    $anos_meses[]=$ano;

    $buscar_meses=mysql_query("SELECT data, DATE_FORMAT(data,'%M') as mes FROM tbl_noticias WHERE YEAR(data)='$ano' GROUP BY MONTH(data)");
    while ($row_meses = mysql_fetch_assoc($buscar_meses)) 
    {
        $mes=$row_meses['mes'];
        $anos_meses[$ano][]=$mes;
    }
}

print_r($anos_meses);

Answer 3

@pc_oc If you use a query at each iteration only to know the total (COUNT(*) AS count) you will have a big problem with performance and memory consumption, especially if the table is different from Myisam.

You can use COUNT(*) in the second query without the need for a 3rd query:

$sql = mysql_query("SELECT DISTINCT YEAR(data) AS ano, id AS id FROM tbl_noticias GROUP BY ano");
while($row = mysql_fetch_array($sql))
{
    echo "Ano: {$row['ano']}<br>";
    $sql1 = mysql_query("SELECT DISTINCT MONTH(data) AS mes, COUNT(*) AS contagem, id FROM tbl_noticias WHERE YEAR(data) = '$ano' GROUP BY mes");
    while ( ($row1 = mysql_fetch_array($sql1))) {
        echo "Mes: {$row1['mes']} - ({$row1['contagem']})<br>";
    }
}

In your example, PHP will call the COUNT(*) function 15 times, whereas this will only call 2 times.