Create a Cumulative Sum Column in codeigniter

I have this table pc_expenditure

Code:

MariaDB [paper2]> select * from pc_expenditure;

+----------------------+-------------------------+-------------------+---------------+---------------------+
| pc_expenditureID | expensetypesID       | receipt_no       | details         |    amount_spent |
+----------------------+-------------------------+-------------------+---------------+---------------------+
|                        1 |                            1 | 345                 | transport     |                    43 | 
|                        2 |                            2 | 3456               | water          |                    50 |
+----------------------+-------------------------+-------------------+---------------+---------------------+

I'd like to get a  cumulative sum(running total) for each column in codeigniter but it's kind tricky it returns all the values in the same column
I don't know how to go around it.

cumulative sum

Code:

MariaDB [paper2]> select expensetypesID, amount_spent, (@csum := @csum + amount_spent) as cumulative_sum
   -> from pc_expenditure
   -> order by expensetypesID;

+--------------------+------------------+---------------------+
| expensetypesID | amount_spent | cumulative_sum  |
+--------------------+------------------+---------------------+
|                      1 |                 43 |                      43 |
|                      2 |                 50 |                      93 |
+--------------------+------------------+---------------------+

Below is my code in codeigniter
model

PHP Code:

function get_pettycash_amount() {
        $this->db->select('amount_spent');
        $this->db->from('pc_expenditure');
        $this->db->order_by("pc_expenditureID", "ASC");
    $query = $this->db->get();
        return $query->result(); 


controller

PHP Code:

$this->data['original'] = $this->pc_expenditure_m->get_pettycash_amount($schoolyearID); 


view

PHP Code:

<td data-title="<?=$this->lang->line('pettycash_bal')?>">
         <?php
                $total = array();
               foreach($original as $number)
                         {
                                            $total[] = array_sum($total)+$number->amount_spent;
                         }
               $total_expense = implode(',', $total);
                                        
     echo ($total_expense); ?>
</td> 


the above code display this kind of result;
+--------------------+------------------+---------------------+
| expensetypesID | amount_spent | balance             |
+--------------------+------------------+---------------------+
|                      1 |                 43 |                  43,93|
|                      2 |                 50 |                  43,93|
+--------------------+------------------+---------------------+
which is slightly different from the second table.

Do you need the individual ID? If not just run SUM on the whole thing.

Code:

SELECT SUM(amount_spent) AS cumulative_sum FROM pc_expenditure;

If you really want your results to look like your query, just run it with.

PHP Code:

$this->db->query('YOUR QUERY HERE'); 


As you don't need to use Query Builder.

Here's a better way to do math.

PHP Code:

$total = 0;$total += $number->amount_spent; 


Do you need the individual ID? If not just run SUM on the whole thing.

Code:

SELECT SUM(amount_spent) AS cumulative_sum FROM pc_expenditure;

If you really want your results to look like your query, just run it with.

PHP Code:

$this->db->query('YOUR QUERY HERE'); 


As you don't need to use Query Builder.

Here's a better way to do math.

PHP Code:

$total = 0;$total += $number->amount_spent; 


Well, i like your math buts that's not what i requested...I have tried that before 
and it only yields the sum of  amount_spent. Remember i need the cumulative sum:

So use option two then. Run your query.

I have this table pc_expenditure

Code:

MariaDB [paper2]> select * from pc_expenditure;

+----------------------+-------------------------+-------------------+---------------+---------------------+
| pc_expenditureID | expensetypesID       | receipt_no       | details         |    amount_spent |
+----------------------+-------------------------+-------------------+---------------+---------------------+
|                        1 |                            1 | 345                 | transport     |                    43 | 
|                        2 |                            2 | 3456               | water          |                    50 |
+----------------------+-------------------------+-------------------+---------------+---------------------+

I'd like to get a  cumulative sum(running total) for each column in codeigniter but it's kind tricky it returns all the values in the same column
I don't know how to go around it.

cumulative sum

Code:

MariaDB [paper2]> select expensetypesID, amount_spent, (@csum := @csum + amount_spent) as cumulative_sum
   -> from pc_expenditure
   -> order by expensetypesID;

+--------------------+------------------+---------------------+
| expensetypesID | amount_spent | cumulative_sum  |
+--------------------+------------------+---------------------+
|                      1 |                 43 |                      43 |
|                      2 |                 50 |                      93 |
+--------------------+------------------+---------------------+

Below is my code in codeigniter
model

PHP Code:

function get_pettycash_amount() {
        $this->db->select('amount_spent');
        $this->db->from('pc_expenditure');
        $this->db->order_by("pc_expenditureID", "ASC");
    $query = $this->db->get();
        return $query->result(); 


controller

PHP Code:

$this->data['original'] = $this->pc_expenditure_m->get_pettycash_amount($schoolyearID); 


view

PHP Code:

<td data-title="<?=$this->lang->line('pettycash_bal')?>">
         <?php
                $total = array();
               foreach($original as $number)
                         {
                                            $total[] = array_sum($total)+$number->amount_spent;
                         }
               $total_expense = implode(',', $total);
                                        
     echo ($total_expense); ?>
</td> 


the above code display this kind of result;
+--------------------+------------------+---------------------+
| expensetypesID | amount_spent | balance             |
+--------------------+------------------+---------------------+
|                      1 |                 43 |                  43,93|
|                      2 |                 50 |                  43,93|
+--------------------+------------------+---------------------+
which is slightly different from the second table.

oh I got where the problem was! I had two foreach loops so this led the display of these two Cumulative Sums in the same  Column