codeigniter: fetch records and sum using SUM()

Question

Please i need your help. i want to fetch records and sum using codeigniter

Here is my model `

{
     $query = $this->db->query('SELECT Sum(dfp_oral_qty) FROM daily_family_planning_register WHERE YEAR(dfp_date) = (YEAR(NOW()))');
    echo ($query->result_array());
}

`

Here is my controller

$data['count_oral_pills_this_year'] = $this->referral->count_oral_pills_this_year();

Here is my view

<td>
    <?php echo $count_oral_pills_this_year; ?></td> -->
</tr>

When i run this, i get a null result whereas it ought to display 300.

Thanks

Answer 1

Your result is an array so you should access your result as array.

Something like this..

$rs = $this->referral->count_oral_pills_this_year();
$data['count_oral_pills_this_year'] = $rs[0]['Sum(dfp_oral_qty)']

Or you may give alias to column, something like this

model

$query = $this->db->query('SELECT Sum(dfp_oral_qty) AS total FROM daily_family_planning_register WHERE YEAR(dfp_date) = (YEAR(NOW()))');

controller

$rs = $this->referral->count_oral_pills_this_year();
$data['count_oral_pills_this_year'] = $rs[0]['total']

Note: you are using 'resulte_array()' so you must specific index to access your result.

I recommend you to use row_array() if you are querying 1 row only.

model

$query = $this->db->query('SELECT Sum(dfp_oral_qty) AS total FROM daily_family_planning_register WHERE YEAR(dfp_date) = (YEAR(NOW()))');
$rs = $query->row_array();
return $rs['total'];

controller

$data['count_oral_pills_this_year'] = $this->referral->count_oral_pills_this_year();

Sorry for my poor english.