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.