0

Im trying to get all years and months from a date column from a mysql table. The expected result is supposed to be used in a meny, and should look something like this:

**2015**
oct
sep
aug
july
june
may
apr
mars
feb
jan
**2014**
dec
nov
oct
sep
aug
july
june
may
apr
mars
feb
jan
**2013**
dec
nov
oct
sep
aug
july
june
may
apr
mars
feb
jan

At first i tried this:

public static function arrange(){

        $database = DatabaseFactory::getFactory()->getConnection();

        //Select all years
        $sql = "SELECT DISTINCT YEAR(date) FROM MOCK_DATA ORDER by YEAR(date) DESC";
        $query = $database->prepare($sql);
        $query->execute();
        $years = $query->fetchAll();

        //Select all months in numbers
        $sql = "SELECT DISTINCT MONTH(DATE) AS month_num FROM MOCK_DATA WHERE YEAR(DATE) = :year ORDER BY DATE DESC";
        $query = $database->prepare($sql);
        $query->execute(array(':year' => $years));
        $query->fetchAll();

         $months_int = array(
                         1=>'januari',
                         2=>'februari',
                         3=>'mars',
                         4=>'april',
                         5=>'maj',
                         6=>'juni',
                         7=>'juli',
                         8=>'agusti',
                         9=>'september',
                         10=>'oktober',
                         11=>'november',
                         12=>'december');

                $months_string = array();
                foreach($months_int as $month){
                        $months_string[] = strtr($month,$months_int);
                }

        return $months_string;
}

But i didn't get it to work, and it seems to be more "advanced" then neccesery, because after an hour or so of googling - i found this:

SELECT DISTINCT CONCAT(MONTHNAME(date), ' ', YEAR(date)) AS `Month`
FROM MOCK_DATA
ORDER BY date DESC

From this stack thread.

So, unfortunately i cannot use that query. But finding that query made think that there is a query that can produce what I'm looking for!

I think my query should give me a stdClass corresponding something like $data->years and $data->months. So i can loop it out with

foreach($data->years as $years){
echo $years;
foreach($data->months as $months){
echo $months;}
}

My question is: Is there and mysql query that gives me all years and all months for that year? If not: How can i do this with php?

Community
  • 1
  • 1
Adam
  • 1,231
  • 1
  • 13
  • 37
  • Possible duplicate of [MySQL Query GROUP BY day / month / year](http://stackoverflow.com/questions/508791/mysql-query-group-by-day-month-year) – Jan Oct 12 '15 at 17:21
  • Just taking off the `CONCAT` "wrapper" should give you the data you need. It may not make sense for you to get months separately since the list of months with data could be different for each year. You could get them separately, but then you would need to query separately for each year. – Uueerdo Oct 12 '15 at 17:41

0 Answers0