How to bring SQL result grouped by year and within months

Asked

Viewed 109 times

0

I have a table like this:

data               |id
2017-02-01 00:00:00|1
2017-02-01 00:00:00|5
2017-04-01 00:00:00|2
2018-02-01 00:00:00|3
2018-04-01 00:00:00|4

Then I make a query with PHP in Mysql that brings me 

SELECT id, data
FROM orders
GROUP BY data

and brings in php

foreach($results as $row){
echo date('m',$row->data).'<br/>';
}

the above result is:

02
04
02
04

But I wanted him to bring in groups every year:

2017
02
04

2018
02
04
php mysql select

2 answers

1


Well, not knowing what’s coming back is very difficult to answer. You can try this code. It creates an array to group your results into a multidimensional array. 

foreach($results as $row){
    $mes = date('m',$row->data);
    $ano =  date('y',$row->data);
    $dia = date('d', $row->data);
    $intervals[$ano][$mes] = $dia;

}

foreach($intervals as $ano => $meses){
    echo $ano . "</br></br>";
    foreach($meses as $mes => $dia){
        echo $mes . "</br>";
    }
}

  • he is bringing the results empty, I made a echo $mes and $year, came straight, but in the interval, came empty

  • actually, it Apare as a result: year

  • Well test this new (edited). if it goes wrong you can show me which function or method you use to get $results

  • Perfect! Gave straight, thanks!!!

  • The manipulations that PHP has with arrays are the most powerful tools that the language has. The programmer who dominates these manipulations will have a productivity above average. ;)

  • I’ll take a look at it, I’m still beginner, vlw!!

  • PHP Recommendation - Object-oriented programming - Pablo Dall'Oglio. The first chapter brings a practical approach to value manipulation tools.

  • only had a problem actually, if they have 2 months of the same year only appears 1 month, ex: dates 02/2018, 03/2018 and 02/2017, only appears 02/2018 and 02/2017

  • I edited the answer. Well it would be nice if you provide a var_dump($results)

  • Show, now gave straight, thank you very much!

Show 5 more comments

0

First problem in your code is that if the date is the same and the time is different, they will not be grouped together, then ideally you group by the year and month of the date, the select would look like this:

SELECT id, data
FROM orders
GROUP BY YEAR(data), MONTH(data);

Already the print on the screen would look like this:

$anoAnterior = '';
foreach($results as $row){
    $anoAtual = date('Y', $row->data)
    if (($anoAnterior != '') && ($anoAnterior != $anoAtual))
        echo "<br />";
    if ($anoAnterior != $anoAtual)
        echo $anoAtual.'<br />';
    echo date('m', $row->data).'<br/>';
}

Browser other questions tagged php mysql select

You are not signed in. Login or sign up in order to post.