PHP数组合并问题

Question

I have a table of races that have the race name, race id, and race date.

I am trying to build a single array (it has to be a single array returned via a function call) which would contain a list of the races in the following format:

,,---2017---
The Resolution AR, 58, 1.14.2017
Heartbreaker AR, 59, 2.11.2017
,,---2016---
The Blue Ridge AR, 38, 5.21.2016
The Fathers Day AR, 43, 6.18.2016
Adventure Bike (Santos), 54, 7.30.2016
,,---2015---
Gemini Springs AR, 4, 12.14.2015

I am almost there! But I am having one issue.

This is the current code I have:

function get_races() {

    // get all the years that have races
    $years = $wpdb->get_results(" 
        select DATE_FORMAT(r.race_date,'%Y') year 
          from race_calendar r 
         group by DATE_FORMAT(r.race_date,'%Y') 
         order by DATE_FORMAT(r.race_date,'%Y') desc; 
    "); 

    // loop through all years that have races
    foreach ( $years as $year ) {

        // add year header to array
        $year_header = array(array ('','','---'.$year->year.'---')); 

        // get races for this particular year 
        $races = $wpdb->get_results(" 
            select r.race_name 
                  ,r.race_id 
                  ,date_format(r.race_date,'%c.%d.%Y') race_date 
              from race_calendar r 
             where DATE_FORMAT(r.race_date,'%Y') = ".$year->year." 
             order by r.race_date;
        ", ARRAY_N); 

        // merge the year header and the races into an array
        $merged_arr = array_merge($year_header, $races); 

    }

    return $merged_arr;

}

// call function to get list of races
$races = get_races();

// display the list of races (with the year separator)
foreach ( $races as $race ) 
{
  echo $race['0'] . ',' . $race['1'] . ',' . $race['2'] . '<br />';
}

It works. But problem is, that code above only displays the last iteration of the years loop, in this case, 2015:

,,---2015---
Gemini Springs AR, 4, 12.14.2015

Obviously $merged_arr is begin reset with each iteration of the years loop

How can I update it so that the resulting array in the function contains the data for all the iterations of the years loop?

Answer 1

You can do this all with a single query. Just select all the rows, then loop over the results and detect when the year changes from the previous iteration to know when to output your separator row.

$last = null;
$query = <select * from race_calendar order by race_date>
foreach ($query as $row) {
    $year = // year of $row['race_date']
    if ($year != $last) {
        // new year, output separator line
        $last = $year;
    }
    // output data line
}