Fill Missing Column With 0 and Generate Date Sequence From MySQL Database Given a Start and End Date-CodePudding

I have gone through the forum and seen some examples but not still quite able to get what I'm actually looking for here. So please if you can, ANSWER directly here without linking to other questions.

I want to fill the missing values with 0 given a sequence of date from mysqli result

I have a table (iv_profits) structured as:

 --- -------- -------- ---------------------- 
|id | type   | amount | created_at           |
 --- -------- -------- ---------------------- 
| 1 | invest |  100   | 2022-02-25 10:59:20  |
| 2 | profit |  20    | 2022-03-27 01:39:20  |
| 3 | profit |  20    | 2022-03-01 15:29:20  |
| 4 | invest |  150   | 2022-03-02 12:10:20  |
 ------------ -------- ----------------------

Now I'm targetng 30 or 31 records before today's date with the below code:

$to = date('Y-m-d',strtotime(date('Y-m-d'). '   1 days')); //2022-03-03
$from = date('Y-m-d', strtotime($to. ' - 31 days')); //2022-01-30

Then I query my database with

$q2 = "SELECT * FROM iv_profits WHERE type='profit' created_at BETWEEN '$from' AND '$to' ORDER BY created_at";

I am able to generate all invest into json object as follows

NOTE: $res = $q2->get_result(); ::::

$data = [];
while ($row = $res->fetch_assoc()){
   $date = date_format(date_create($row['created_at']),'d M');
   $data[$date] = $row['amount'];
}

/*
OUTPUT
{
27 Feb: 20,
01 Mar: 20,
}
*/

But the result I'm hoping to get is something like:

/*
{
30 Jan: 0,
...
24 Feb: 0
25 Feb: 0,
26 Feb: 0,
27 Feb: 20,
28 Feb: 0,
01 Mar: 20,
}
*/

PS: If the question is quite confusing. I can keep editing to get to the point. Thank you Dev!

CodePudding user response：

Once you've populated $data, you can loop from your start date to your end date and display either the corresponding info from $data or 0 if there is no such element:

$current = new DateTime( $from );
$end = new DateTime( $to );
while ( $current <= $end ) {
    echo $current->format('d M') . ': '; // The day and month we are up to
    // If we have a record, display it:
    if ( $data[ $current->format('d M') ] ) {
        echo $data[ $current->format('d M') ];
    } else {
        // No record, so show 0:
        echo 0;
    }
    echo "\n";
    $current->add(new DateInterval('P1D')); // Go to next day
}

CodePudding user response：

You can fetch the data from the database and build the $data array just as you currently are. Then you just need to iterate the given date range and to create the output you want. If there's a matching value in $data, then use that, otherwise default to a zero value.

$start = new DateTime(' - 31 days');
$end = new DateTime('   1 days');

while ($start <= $end) {
    $key = $start->format('d M');

    // Get the matching value from $data if it exists, use zero if not
    $result[$key] = $data[$key] ?? 0;

    $start->modify(' 1 day');
}