sum total amount paid by specific student when they have paid more than twice

[barimahagyapong]

I have  mysql table titled fee_payment.

details of the table:

 

fee_payment Table

payment_id    student_id          name            class        term                       year             date                  amount

1                             6                john              JHS2        2nd_term              2013           23-04-2013 56        67

2                             6                john              JHS2        2nd_term              2013           23-04-2013 56        34

3                             5                peter            JHS3        3rd_term               2014           23-04-2014 56        85

4                             6                john              JHS2        2nd_term              2014           23-04-2014 56        76

5                             6                john              JHS2        3rd_term               2014           23-04-2014 56        34

6                             6                john              JHS2        2nd_term              2014           23-04-2014 56        23

7                             5                peter             JHS2        3rd_term               2014           23-04-2014 56        43

 

what i need to do now is to sum total amount paid by each student so that i can view payment records based on term and year.

what i have tried:

 

 

Controller:

/******MANAGE FEE PAYMENTS / INVOICES WITH STATUS*****/
    function payfees($param1 = '', $param2 = '', $param3 = '')
    {
        if ($this->session->userdata('admin_login') != 1)
            redirect(base_url(), 'refresh');
        
        if ($param1 == 'create') {
            $data['student_id']         = $this->input->post('student_id');
            $data['name']         = $this->input->post('name');
            $data['class_id']              = $this->input->post('class');
            $data['term']        = $this->input->post('term');
            $data['year']             = $this->input->post('year');
            $data['amount']             = $this->input->post('amount');
            $data['creation_timestamp'] = strtotime($this->input->post('date'));
            
            $this->db->insert('fee_payment', $data);
           redirect(base_url() . 'index.php?admin/payfees', 'refresh');
        }
        if ($param1 == 'do_update') {
            $data['student_id']         = $this->input->post('student_id');
            $data['class_id']              = $this->input->post('class');
            $data['term']        = $this->input->post('term');
            $data['year']             = $this->input->post('year');
            $data['amount']             = $this->input->post('amount');
            $data['creation_timestamp'] = strtotime($this->input->post('date'));
            
            $this->db->where('payment_id', $param2);
            $this->db->update('fee_payment', $data);
            redirect(base_url() . 'index.php?admin/payfees', 'refresh');
        } else if ($param1 == 'edit') {
            $page_data['edit_data'] = $this->db->get_where('fee_payment', array(
                'payment_id' => $param2
            ))->result_array();
        }
        if ($param1 == 'delete') {
            $this->db->where('payment_id', $param2);
            $this->db->delete('fee_payment');
            redirect(base_url() . 'index.php?admin/payfees', 'refresh');
        }
        $page_data['page_name']  = 'payfees';
        $page_data['page_title'] = get_phrase('fee_payment');
        $this->db->order_by('creation_timestamp', 'term');
        $page_data['payments'] = $this->db->get('fee_payment')->result_array();
        $this->load->view('index', $page_data);
    }
      

 

The Invoice:

 

 

<div class="box-content">
        <?php foreach($edit_data as $row):?>
        
        
        <div class="pull-left">
        
            <span style="font-size:20px;font-weight:100;">
            
            <?php echo get_phrase('payment_to');?>
            <img width="50" height="30" src="<?php echo base_url();?>uploads/logo.png" style="max-height:100px;margin:20px 0px;" />
            </span>
            <br />
            <?php echo $system_name;?>
            <br />
            <?php echo $this->db->get_where('settings' , array('type'=>'address'))->row()->description;?>
        </div>
        <div class="pull-right">
            <span style="font-size:20px;font-weight:100;">
                <?php echo get_phrase('credited_account:');?>
            </span>
            <br />
                <?php echo $this->db->get_where('student' , array('student_id'=>$row['student_id']))->row()->name;?>
            <br />
                <?php echo get_phrase('student_id');?> :
                <?php echo "FAVECSID0000", $this->db->get_where('student' , array('student_id'=>$row['student_id']))->row()->student_id;?>
            <br />
                <?php echo get_phrase('class');?> :
                <?php
                $class_id    =    $this->db->get_where('student' , array('student_id'=>$row['student_id']))->row()->class_id;
                echo $this->db->get_where('class' , array('class_id'=>$class_id))->row()->name;
                ?>
        </div>
        <div style="clear:both;"></div>
        <hr />
        <table width="100%" background="http://localhost/schoolmanager/uploads/backlogos.png">
            <tr style="background-color:#7087A3; color:#fff; padding:5px;">
                <td style="padding:5px;"><?php echo get_phrase('payment_details');?></td>
                <td width="30%" style="padding:5px;">
                    <div class="pull-right">
                        <?php echo get_phrase('amount');?>
                    </div>
                </td>
            </tr>
            <tr>
                <td>
                    <span style="font-size:20px;font-weight:100;">
                        <?php echo get_phrase('payment_made');?>
                    </span>
                    <br />
                    
                </td>
                <td width="30%" style="padding:5px;">
                    <div class="pull-right">
                        <span style="font-size:20px;font-weight:100;">
                        <?php echo "Gh", $row['amount'];?>    
                        </span>
                    </div>
                </td>
            </tr>
            
            <tr>
           
            <tr>
                <td>
                    <span style="font-size:20px;font-weight:100;">
                        <?php echo get_phrase('balance');?>
                    </span>
                    <br />
                    <?php echo $row['description'];?>
                    <?php
                                $exams = $this->db->get_where('fee_payment', array('student_id'=>$row['student_id']))->result_array();
                                foreach($exams as $row):
                                ?>
                                <?php
                                
                                $ttpaid = sum($row['amount']);
                                echo $ttpaid;?>
                                 <?php
                                endforeach;
                                ?>
                </td>
                <td width="30%" style="padding:5px;">
                    <div class="pull-right">
                        <span style="font-size:20px;font-weight:100;">
                <?php
                
                $tuition    =    $this->db->get_where('student' , array('student_id'=>$row['student_id']))->row()->tuition_fee;
                $status    =    $this->db->get_where('student' , array('student_id'=>$row['student_id']))->row()->status_fee;
                $total_fees = $tuition + $status;
                $balance_fee = $total_fees - $row['amount'];
                echo "Gh", $balance_fee;
                ?>            
                        </span>
                    </div>
                </td>
            </tr>
            
            <tr>
       
                <td></td>
                <td width="30%" style="padding:5px;">
                    <div class="pull-right">
                    <hr />
                    <?php echo get_phrase('status');?> : <?php echo $row['status'];?>
                    <br />
                    <?php echo get_phrase('receipt_no:');?> : <?php echo "FAVECSFP0000",$row['payment_id'];?>
                    <br />
                    <?php echo get_phrase('date');?> : <?php echo date('m/d/Y', $row['creation_timestamp']);?>
                    </div>
                </td>
            </tr>
      </table>
<br />
<br />

        
        <?php endforeach;?>
    </div>
</div>

 

 

output:

ayment To
fffff
P O BOX 126

Credited Account:
john
Student Id : FAVECSID00006
Class : JHS2A

Payment Details

Amount

Payment Made

Gh34

Balance

 

 

as we can see, i am only able to fetch one payment made by john but not able to sum all amount that belong to john.

please help. thank you in advance

[Barand]

I suppose that somewhere in there a query is being executed to get the records for John but no way to see what it is and, therefore, no way to know how to change it. 

 

All I can do then is point you towards Sql SUM() function and GROUP BY clauses