Arrange months under year php and mysql

Question

I have been grinding my gears on this one. See my previous question on the same topic. It seems as this is a tough one to solve. But, i found this thread, and it seems as Derek Adair has managed to make a json out of the same problem.

My question is:

How can i from this query

SELECT
    MONTHNAME(date) as month, YEAR(date) as year
FROM
    MOCK_DATA
GROUP BY YEAR(date), MONTH(date) DESC

Get the result either a json array, or obecjt(stdClass)

{
  "2009":["August","July","September"],
  "2010":["January", "February", "October"]
}

Because now i get the following result:

Array(35) { [0]=> object(stdClass)#19 (2) {
["month"]=> string(8) "December" 
["year"]=> string(4) "2013" } 
[1]=> object(stdClass)#20 (2) { 
["month"]=> string(8) "November" 
["year"]=> string(4) "2013" } . . . .

And the expected result is something like:

2013
jan
feb
...etc
2014
jan
feb
..etc
2015
jan
feb
..etc

after fetching data from query, use a forloop and do it. – Niranjan N Raju Oct 14 '15 at 08:47 — Niranjan N Raju, Oct 14 '15 at 08:47

score 0 · Accepted Answer · answered Oct 14 '15 at 09:03

Please see the below code

$result = mysql_query("SELECT MONTHNAME( DATE ) AS 
MONTH , YEAR( DATE ) AS YEAR
FROM mdata
GROUP BY YEAR( DATE ) , MONTH( DATE ) DESC ");
$data = array();
while ($row = mysql_fetch_array($result, MYSQL_NUM)) { 
    $m = $row[0];
    $y = $row[1];
    if(!isset($data[$y]) ) {
        $data[$y] = array();
    }
    $data[$y][] = $m;
}
echo '<pre>'.print_r($data, true).'</pre>';

and the result is

Array
(
    [1997] => Array
        (
            [0] => February
        )

    [2004] => Array
        (
            [0] => October
        )

    [2015] => Array
        (
            [0] => October
            [1] => March
        )

)

score 0 · Answer 2 · answered Oct 14 '15 at 09:59

This is what i came up with thanks to the accepted answer!

        $sql = "SELECT MONTHNAME(date) as month, YEAR(date) as year FROM MOCK_DATA GROUP BY YEAR(date), MONTH(date) DESC";
        $query = $database->prepare($sql);
        $query->execute();

        $data = array();

while($row = $query->fetch(PDO::FETCH_ASSOC)) {
    $m = $row['month'];
    $y = $row['year'];
    if(!isset($data[$y]) ) {
        $data[$y] = array();
    }
    $data[$y][] = $m;
}

        return $data;

And this is how i print it:

echo '<br>Years and months<br>';
foreach($this->arrange as $year => $yearMonths) {
    echo '<b>Year: '.$year.'</b><br>';
    foreach($yearMonths as $month) {
        echo 'Month: '.$month.'<br>';
    }
    echo '<br>';
}
echo '<br>Only years<br>';
foreach($this->arrange as $year => $yearMonths) {
    echo '<b>Year: '.$year.'</b><br>';
}

Arrange months under year php and mysql

2 Answers2