Search the Community

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