CODEWITHSUNDEEP
phpcodeigniterselectactiverecordsum
JonYork
JonYork

Reputation: 1243

Implement SELECT SUM() query using Codeigniter select_sum() active record method and return the value

I needed to add up the column values of all the rows for a result. I am using the select_sum() in my code and using its second parameter to assign the column alias, but it is returning an array.

My model method:

function Dues_Paid_Tot($date)
{
    $query = $this->db->select_sum('Dues_Paid', 'Dues_Paid_Tot');
    $query = $this->db->get('Membership');
    return $query->result();
}

Here is the controller

function Fiscal2()
{
    $date = $this->input->post('Select_Date');
    if($query = $this->report_model->fiscal_list($date))
    {
        $data['records'] = $query;
    }
    $data['date'] = $this->input->post('Select_Date');
    $data['Dues_Paid_Tot'] = $this->report_model->Dues_Paid_Tot($date);
    $data['main_content'] = 'report_fiscal_view';
    $this->load->view('includes/template', $data);
}

And this is the pertinent code for the view:

<?php echo $Dues_Paid_Tot; ?>

The problem is, instead of showing a summation of all the entries in the column Dues_Paid, I get "Array" in my view.

Where am I going wrong?

Upvotes: 1

Views: 16832

Answers (4)

mickmackusa
mickmackusa

Reputation: 47991

To access the lone row in the result set object chain ->row(), then get the column value from that object using property access syntax (->).

Additionally:

  • there is no benefit in aliasing the sum if you are only returning it's value
  • method names in PHP should be camelCased
  • since you are passing in $date, I assume it needs to be added to your query logic -- but I can't be sure how.
  • I also can't be 100% sure if your summed value might be a float or an int, so I chose the more lenient return type.

Model method:

function totalDuesPaid(string $date): float
{
    return $this->db
        ->select_sum('Dues_Paid')
        ->where('I_Dont_Know', $date)
        ->get('Membership')
        ->row()
        ->Dues_Paid;
}

Or

    return $this->db
        ->select_sum('Dues_Paid')
        ->get_where('Membership', ['I_Dont_Know' => $date])
        ->row()
        ->Dues_Paid;

Upvotes: 0

jeroen
jeroen

Reputation: 91762

You´re not doing anything wrong, that´s just how CodeIgniter works. From the manual on result():

This function returns the query result as an array of objects, or an empty array on failure.

So the information you are looking for is contained in an object in the first element of the array.

If you do a var_dum($data['Dues_Paid_Tot']); you will see how you can access your value, it will probably be something like:

$data['Dues_Paid_Tot'][0]->some_name

Upvotes: 1

jondavidjohn
jondavidjohn

Reputation: 62402

It is still a query so you still have to process the result, try changing your model to this...

function Dues_Paid_Tot($date)
{
    $query = $this->db->select_sum('Dues_Paid', 'Dues_Paid_Tot');
    $query = $this->db->get('Membership');
    $result = $query->result();

    return $result[0]->Dues_Paid_Tot;
}

Upvotes: 6

Pav
Pav

Reputation: 2328

function Dues_Paid_Tot($date)
{
    $query = $this->db->select_sum('Dues_Paid', 'Dues_Paid_Tot');
    $query = $this->db->get('Membership');
    return $query->result();
}

return $query->result();

This function returns the query result as an array of objects, or an empty array on failure.

For more information please see http://codeigniter.com/user_guide/database/results.html

Upvotes: 1

Related Questions