I'm trying to do the following in 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 tbl_noticias table the data field of type yyyy-mm-dd . I'm still saving the id field among others (I do not need to here). I can not figure out how to get to this end result using mysql
I was able to get there as follows:
<?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!
try 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);
@pc_oc If you use a query at each iteration only to know the total (COUNT (*) AS count) you will have major problem with performance and memory consumption, especially if the table is different from MyISAM.
You can use COUNT (*) in the second query, without needing 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 in this case, it will only call 2 times.