Populate an array with 12 months of data using db results or default values-CodePudding

I have a Mysql query that fetch monthly records from DB to use to draw a graph. If there is no enough data to cover the whole year (12 months) the graph will be drawn with only 2 months. This means I need to create an array that can initialize all months with 'income' zero values. Then replace the 'income' values with values from DB.

$year = date('Y');
for ($i = 1; $i < 13 ; $i  ) { 
  $month = date('M-Y', strtotime('01-'.$i.'-'.$year));
  $income = 0;
  $months[] = array('dates' =>$month, 'income'=>$income);
}
print_r($months);
/*Output Array
(
    [0] => Array
        (
            [dates] => Jan-2022
            [income] => 0
        )

    [1] => Array
        (
            [dates] => Feb-2022
            [income] => 0
        )
      etc.. etc.. up to Dec-2022
*/

From Db this is my result. Because the data is only 1 and 1/2 months old.

/*DBOutput
Array
(
    [0] => Array
        (
            [dates] => Sep-2022
            [income] => 1864
        )

    [1] => Array
        (
            [dates] => Oct-2022
            [income] => 548
        )

)*/

See my below code. It seems to replace Sep-2022 'income' with 0, Though Oct-2022 has correct 'income' output.

foreach ($months as $key) {
        foreach ($dbdata as $db) {
          if ($key['dates'] === $db['dates']) {
            $net = $db['income'];
            $dates = $db['dates'];
          } 
          else{
            $net = $key['income'];
            $dates = $key['dates'];
          }
        }
        $graphData[] = array('dates' => $dates, 'income' => $net);
      }
      if ($graphData) {
        echo json_encode($graphData);
      }

/*Output 
[{"dates":"Jan-2022","income":0},{"dates":"Feb-2022","income":0},{"dates":"Mar-2022","income":0},{"dates":"Apr-2022","income":0},{"dates":"May-2022","income":0},{"dates":"Jun-2022","income":0},{"dates":"Jul-2022","income":0},{"dates":"Aug-2022","income":0},{"dates":"Sep-2022","income":0},{"dates":"Oct-2022","income":548},{"dates":"Nov-2022","income":0},{"dates":"Dec-2022","income":0}]
*/

As you can see, September's income is not getting the DB Value. I am pretty sure this code will only push the last income value in the dbArray, which is incorrect.

CodePudding user response：

Your code seems fine and in the right logic.
You only need to replace the 'income' values with values from DB.
Do it like this:

foreach ($dbdata as $db) {
  // first find the key of the element
  $key = array_search($db['dates'], array_column($months, 'dates'));
  if($key !== false)){
    $months[$key]['income'] = $db['income'];
  }
}

CodePudding user response：

Calling array_column() and array_search() on each iteration is NOT an efficient approach.

Convert your database result set into a lookup array by declaring first-level associative keys to each row based on the dates values.

Then when you are building your monthly rows, check the lookup array and fallback to default values when a month is not represented in the database.

Code: (Demo)

$db = [
    ['dates' => 'Sep-2022', 'income' => 1864],
    ['dates' => 'Oct-2022', 'income' => 548]
];
$lookup = array_column($db, null, 'dates');

$year = date('Y');
for ($i = 1; $i < 13 ;   $i) { 
    $date = date('M-Y', strtotime("$year-$i-01"));
    $months[] = $lookup[$date]
        ?? ['dates' => $date, 'income' => 0];
}
var_export($months);

That said, if this was my application, I'd probably just JOIN on a derived table that prepopulates all months in a give year with the default values so that all of the data preparation is done in the SQL query. See this and this.