PHP Create array using Loop and add days with zero orders

Question

I have a mysql query output that give the number of loads for each day of the month.

Array
(
    [0] => Array
        (
            [day] => 01
            [Loads] => 9
        )

    [1] => Array
        (
            [day] => 02
            [Loads] => 7
        )

    [2] => Array
        (
            [day] => 03
            [Loads] => 12
        )

    [3] => Array
        (
            [day] => 04
            [Loads] => 5
        )

    [4] => Array
        (
            [day] => 06
            [Loads] => 1
        )

    [5] => Array
        (
            [day] => 07
            [Loads] => 4
        )

    [6] => Array
        (
            [day] => 08
            [Loads] => 9
        )

    [7] => Array
        (
            [day] => 09
            [Loads] => 19
        )

    [8] => Array
        (
            [day] => 10
            [Loads] => 11
        )

    [9] => Array
        (
            [day] => 11
            [Loads] => 5
        )

    [10] => Array
        (
            [day] => 12
            [Loads] => 2
        )

    [11] => Array
        (
            [day] => 14
            [Loads] => 7
        )

    [12] => Array
        (
            [day] => 15
            [Loads] => 9
        )

    [13] => Array
        (
            [day] => 16
            [Loads] => 11
        )

    [14] => Array
        (
            [day] => 17
            [Loads] => 9
        )

    [15] => Array
        (
            [day] => 18
            [Loads] => 6
        )

    [16] => Array
        (
            [day] => 19
            [Loads] => 3
        )

)

You can see from the output not all days have loads. Day 5 and Day 13 are missing. I want to loop and create a new array and if there are no loads for that day add the day with zero loads.

I have been struggling with this. It seems that it would be simple but I can't seem to get it. Can someone here please help me?

I am extremely thankful.

Here is what I have so far:

$dayCountArr = [];
for ($i = 1; $i <= 31; $i++) {
    $tempArr = [];
    foreach ($dayO as $d) {
        if ($d['day'] == $i) {
            $day = $d['day'];
            $loads = $d['Loads'];
        } else {
            $day = $i;
            $loads = 0;
        }

        $dayCountArr[] = [$day, $loads];
    }
}

Here is query. It returns the above output

SELECT 
  DATE_FORMAT(r.ship_date, '%d') AS day, 
  COUNT(DISTINCT(r.order_number)) AS 'Loads' 
FROM table r 
WHERE YEAR(r.ship_date) = '2022' 
  && MONTH(r.ship_date) = 3 
  && r.team = 'TEAM-2' 
  && (r.order_status = 'Progress' || r.order_status = 'Delivered') 
GROUP BY DATE_FORMAT(r.ship_date, '%d')

This is probably a bit more complicated than it first appears. For example, it is relatively easy to find the gaps between the min (01) and max (19) days, but how do you know if there are missing days from the end? You need to introduce some date checks to see if there are missing days on the end. — waterloomatt, Mar 20 '22 at 13:25
Please edit your question and show us what you've tried so we can see how to contribute. — waterloomatt, Mar 20 '22 at 13:27
A possible solution to the above problem would be to start with a date range, beginning of the month to today. Then iterate each day and check if the input array contains an entry for the corresponding day. If it does, move on. If it doesn't create it. Finally, another solution would be to do this in SQL, https://stackoverflow.com/a/3538926/296555. — waterloomatt, Mar 20 '22 at 13:31
Also see https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=52d242480c80feebac5675d04ac17ab5 — waterloomatt, Mar 20 '22 at 13:37
Seems related: https://stackoverflow.com/q/22117442/2943403 , https://stackoverflow.com/q/63566097/2943403 — mickmackusa, Mar 20 '22 at 13:48
Also relevant: [MySQL - How to show all days records in particular month?](https://stackoverflow.com/q/30300664/2943403) — mickmackusa, Mar 20 '22 at 14:26
I would recommend doing this in SQL if at all possible, but here’s a general PHP approach: https://3v4l.org/BCT1P. Probably not the most efficient, but if we’re only talking a month it probably doesn’t matter. — Chris Haas, Mar 20 '22 at 15:34

Zulfiqar Ali Syed · Answer 1 · 2022-03-21T06:39:03.533

I assume your sql array is in $dayO and days are in preceding 0 format, I created load array for available days and assigned them to new dayCountArray. I applied str_pad to avoid keys 1(numeric) and use 01(string)

 $dayCountArr = array();
foreach($dayO as $day){
    $load[str_pad($day['day'], 2, "0", STR_PAD_LEFT)] = $day['Loads']; 
}   

     for ($i = 1; $i <= 31; $i++) {
//Generating keys ('01','02',..,'31')
            $key = str_pad($i, 2, "0", STR_PAD_LEFT);
    //Assign 0 if Loads not available for $key 
        $dayCountArr[] = array('day' => $key,'Loads' => isset($load[$key])?$load[$key]:0);
        }

print_r($dayCountArr);

PHP Create array using Loop and add days with zero orders

1 Answers1