[FIXED] How to return JSON response in below format from MySQL table in PHP

Issue

In my Database table I store dates in a format like this 2015-03-22 in a date column.

|   mydate   |
------------
| 2015-03-22 |
| 2015-03-17 |
| 2015-04-02 |
| 2015-04-01 |

I need JSON to return a response like:

    {  
   "result": [{    
     "2014":[{}],
     "2015":[{
        "April":[
            "2015-04-05",
            "2015-04-04",
            "2015-04-03",
            "2015-04-01"
        ],
        "March":[
            "2015-03-25",
            "2015-03-14",
            "2015-03-07",
            "2015-03-01"
        ]
     }] 
   }]    
}

I tried this query, but I failed to make JSON objects like above:

$getDate = "SELECT DISTINCT( mydate), MONTHNAME( mydate ) AS m, YEAR( mydate ) as y 
FROM table_name 
GROUP BY m, mydate  ORDER BY mydate DESC";

Solution

You need to sequentially define a nested array:

$sql = "SELECT
    DISTINCT( mydate) AS `date`,
    MONTHNAME( mydate ) AS m,
    YEAR( mydate ) as y
FROM
    table_name
GROUP BY
    m, mydate
ORDER BY
    mydate DESC";

$result = mysqli_query($c, $sql);

$structure = array();
while ($row = mysqli_fetch_assoc($result))
{
    //year
    if(!isset($structure[$row['y']]))
    {
        $structure[$row['y']] = array();
    }

    //month inside the year
    if(!isset($structure[$row['y']][$row['m']]))
    {
        $structure[$row['y']][$row['m']] = array();
    }

    //date inside the month
    $structure[$row['y']][$row['m']][] = $row['date'];
}
mysqli_free_result($result);

//encode to json
$structure = array('result' => $structure);
$json = json_encode($structure);

Answered By - user4035