How can I sort my SQL records in PHP by Month?-CodePudding

I have a table in my database, that stores statistical data. Structure is like this:

---------------------------------------------
| travelTimes                               |
---------------------------------------------
| ID       | INT(6)  | *Primary key*        |
| goesfrom | INT(6)  | *location ID*        |
| goesto   | INT(6)  | *location ID*        |
| length   | INT(6)  | *time it takes*      |
| time     | Date    | *date of the record* |
---------------------------------------------

I am trying to display these data on a PHP page grouped by the month of the time Column, loading these data to charts (if I have records from 3 different months, there should be 3 different charts).

What do you think the best solution would be for this problem?

I have tried loading the table to a PHP array(associative), and compare the dates that way, but php is quite unique as I understand. So this didn't work:

foreach($travelTimes as $travelTime){
    if(date("m", strtotime($travelTime["time"])) == date("m")){
        // I would sort here the current month's records
    }
}

Also there was a problem at the end of the years, because my tries ignored the year change.

CodePudding user response：

Your idea is a good one, so I adapted it to make it fill an array with every month and its associated travelTimes:

$monthlyTravelTimes = [];
foreach($travelTimes as $travelTime) {
    $month = date("m", strtotime($travelTime["time"]);
    $monthlyTravelTimes[$month] = $travelTime;
}

You can get the travel times for the current month via:

$monthlyTravelTimes[date('m')];

Note: This doesn't handle years at all, but could easily be adapted by replacing 'm' by 'Y-m'.

CodePudding user response：

Someone added an answer that got deleted since, but his idea helped find a solution:

SELECT *, MONTH(time) as timeMonth, YEAR(time) as timeYear FROM travelTimes ORDER BY time;

With this, I can simply compare strings in PHP like:

if($travelTime["timeYear"] == "2021" && $travelTime["timeMonth"] == "10"){}

Without problems.