Home > Software design >  Trying to get daykey for Array but not getting output as required
Trying to get daykey for Array but not getting output as required

Time:03-10

I am trying to get the daykey from dates which i need to use in Array to fill "0" if data is not available . My dates start from "2022-02-08" and end with "2022-03-10". Trying to get daykey using code

$rahul = Buffalomilkrecord::select(DB::raw('date'), DB::raw('sum(totalmilk) as totalmilk, DATE_FORMAT(date,"%d") as "daykey"')) 
                                        ->whereBetween('date', [$olddate, $todaydate] )
                                        ->groupBy(DB::raw('date'))
                                        ->orderBy('date','asc')
                                        ->get();

the out put i am getting

array:31 [▼
      0 => "2022-03-01"
      1 => 0
      2 => 0
      3 => 0
      4 => 0
      5 => 0
      6 => 0
      7 => "2022-02-08"
      8 => 0
      9 => 0
      10 => 0
      11 => 0
      12 => 0
      13 => 0
      14 => 0
      15 => "2022-02-16"
      16 => "2022-02-17"
      17 => "2022-02-18"
      18 => "2022-02-19"
      19 => "2022-02-20"
      20 => "2022-02-21"
      21 => "2022-02-22"
      22 => "2022-02-23"
      23 => 0
      24 => "2022-02-25"
      25 => "2022-02-26"
      26 => 0
      27 => 0
      28 => 0
      29 => 0
      30 => 0
      ]


 $ddtest = [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0];

                                        foreach($rahul as $order)
                                            {
                                                $ddtest[$order->daykey-1] = $order->date;
                                            }

Here i am expecting to start array with 0 => "2022-02-08" and so on.... as date start in Month and end with March

I feel i am doing some error in query but can not find where i am doing mistake.. Thanks in advance for help

CodePudding user response:

The SQL will only return exiting data, so if there is no data for this specific day, you wont get a row for this day. This is something you have to take care outside of the query.

You need to build the date range by yourself. for example like this: I have 2 dates in PHP, how can I run a foreach loop to go through all of those days?

Build one array with "default" entries per day with 0 values and than merge them with the sql result.

  • Related