0





57
1

How can I get the total deposit and total withdraw combined for each month? e.g (all January month total deposit and total withdraw) from an array.

Actually, I have some table (100+). Each table has so many records with deposit, withdraw and date column fields. I have to sum the deposit and withdraw value with grouped by date.

Indeed, I got the monthly total deposit and total withdraw amount for each table. Now, I have to sum each month from all tables. e.g, Total deposit and withdraw of Jaunuary of all tables.

In my CI Controller:

$all_table_id = $this->admin_model->get_all_id();

foreach ($all_table_id as $table_id)
{
    // $table_id['accountNo'] is generated table name 
    // e.g baby_ld_account_45456 ,baby_ld_account_12345

    $tableName = "baby_ld_account_" . $table_id['accountNo'];

    $single_table_monthly_data = $this->admin_model->get_monthly_data_each_table($tableName);

    // print_r($single_table_monthly_data);

    $all_table_monthly_data[] = $single_table_monthly_data;

    print_r($monthly_data);
}

In my CI model:

public function get_monthly_data_each_table($tableName) {
    $sql = "SELECT DATE_FORMAT(`entryDate`,'%M') as Month, 
                   SUM(`deposit`) AS `Deposit`, 
                   SUM(`withDraw`) AS `Withdraw` 
            FROM $tableName 
            WHERE YEAR(`entryDate`) = YEAR(CURDATE()) 
            GROUP BY DATE_FORMAT(`entryDate`,'%M')";

    $query = $this->db->query($sql);
    $result = $query->result_array($query);

    return $result;
}

Array result:

Array (
    [0] => Array ( 
        [0] => Array ( [Month] => January [Deposit] => 4000 [Withdraw] => 8000 )
        [1] => Array ( [Month] => February [Deposit] => 200 [Withdraw] => 5000 )
    )

    [1] => Array (
        [0] => Array ( [Month] => January [Deposit] => 1000 [Withdraw] => 1000 )
        [1] => Array ( [Month] => February [Deposit] => 3000 [Withdraw] => 6000 )
    )
    [2] => Array (
        [0] => Array ( [Month] => January [Deposit] => 6000 [Withdraw] => 2000 )
        [1] => Array ( [Month] => February [Deposit] => 4000 [Withdraw] => 8000 )
    )

    [3] => Array (
        [0] => Array ( [Month] => January [Deposit] => 3500 [Withdraw] => 2000 )
        [1] => Array ( [Month] => February [Deposit] => 1200 [Withdraw] => 5000 )
    )
)

Can anyone give me a solution?

Question author Riyadh-ahmed | Source

0


1

As this was pointed in comments, this better to be done with SQL query.

But you sure can do it with PHP:

$result = array_reduce(
    array_reduce($array, 'array_merge', []), // Flattern array.
    function ($result, $item) {
        // Extract array elements to variables, for the sake of easier use.
        extract($item);

        // If there's no entry for current month - create emtpy entry.
        if (!isset($result[$Month])) {
            $result[$Month] = [
                'Deposit' => 0,
                'Withdraw' => 0
            ];
        }

        // Add current amounts.
        $result[$Month]['Deposit'] += $Deposit;
        $result[$Month]['Withdraw'] += $Withdraw;

        return $result;
    },
    []
);

Here is working demo.

Answer author Sevavietl

Ask about this question here!