perform sum of values codeiginiter

Question

I need to add some values at the end of a table as shown below:

below follows the codes I am using:

Controller:

function index()
    {
        $this->template->set('title', 'Lista de Orçamentos');
        $config = array(
            "base_url" => base_url('orcamentos/p'),
            "per_page" => 9,
            "num_links" => 3,
            "uri_segment" => 3,
            "total_rows" => $this->model->countAll(),
            "full_tag_open" => "<ul class='pagination'>",
            "full_tag_close" => "</ul>",
            "first_link" => FALSE,
            "last_link" => FALSE,
            "first_tag_open" => "<li>",
            "first_tag_close" => "</li>",
            "prev_link" => "Anterior",
            "prev_tag_open" => "<li class='prev'>",
            "prev_tag_close" => "</li>",
            "next_link" => "Próxima",
            "next_tag_open" => "<li class='next'>",
            "next_tag_close" => "</li>",
            "last_tag_open" => "<li>",
            "last_tag_close" => "</li>",
            "cur_tag_open" => "<li class='active'><a href='#'>",
            "cur_tag_close" => "</a></li>",
            "num_tag_open" => "<li>",
            "num_tag_close" => "</li>"
            );

        $this->pagination->initialize($config);

        $data['pagination'] = $this->pagination->create_links();

        $offset = ($this->uri->segment(3)) ? $this->uri->segment(3):0;
        $data['orcamentos'] = $this->model->listar('ocod','asc', $config['per_page'],$offset);
        $data['total'] = $this->model->soma();

        $this->template->load('layout', 'orcamentos_lista.phtml', $data);
    }

Model:

function listar($sort = 'ocod', $order = 'asc',$limit =null, $offset = null) {
    $this->db->order_by($sort, $order);

    if($limit){
        $this->db->limit($limit,$offset);
    }

    $this->db->select('orcdat, situacao, ocod, valor, cnome, pnome,numero,rnome');
    $this->db->from('orcamentos');
    $this->db->join('clientes', 'clientes.ccod = orcamentos.cliente');
    $this->db->join('produtos', 'produtos.pcod = orcamentos.produto');
    $this->db->join('representantes', 'representantes.rcod = clientes.representante');
    $query = $this->db->get(); 
    return $query->result();
}

function soma(){
    $this->db->select('valor');
    $this->db->from('orcamentos');
    $query = $this->db->get();
    return $query->result();
}

View:

<div id="list" class="row">
    <div class="table-responsive col-md-12">
        <table class="table table-striped" cellspacing="0" cellpadding="0">
            <thead>
                <tr>
                    <th>ID</th>
                    <th>Cliente</th>
                    <th>Produto</th>
                    <th>Data</th>
                    <th>Situação</th>
                    <th>Valor</th>
                    <th>Numero</th>
        <th>Representante</th>
                    <th class="actions">Ações</th>
                </tr>
            </thead>

            <tbody>

                <?php foreach ( $orcamentos as $orcamento ) {?>
                    <tr>
                        <td><?php echo $orcamento->ocod; ?></td> 
                        <td><?php echo $orcamento->cnome; ?></td>    
                        <td><?php echo $orcamento->pnome; ?></td>
                        <td><?php echo $orcamento->orcdat; ?></td>
                        <td><?php echo $orcamento->situacao; ?></td>
                        <td><?php echo $orcamento->valor; ?></td>
                        <td><?php echo $orcamento->numero; ?></td>
          <td><?php echo $orcamento->rnome; ?></td>
                        <td class="actions">
                            <a title="Editar" class="btn btn-warning btn-sm fa fa-pencil-square-o" href="<?php echo base_url() . 'orcamentos/editar/' . $orcamento->ocod; ?>"></a>
                            <a title="Deletar" class="btn btn-danger btn-sm fa fa-trash" href="<?php echo base_url() . 'orcamentos/deletar/' . $orcamento->ocod; ?>" onclick="return confirm('Confirma a exclusão deste registro?')"></a>
                        </td>       

                    </tr>               

                <?php } ?>
            </tbody>
    <tfoot>
      <tr>
        <th>Valor Total: <?php  $total  ?> </th>    
      </tr>
    </tfoot>
        </table>

        <h3><?php echo $this->session->flashdata('mensagem');?></h3>

    </div>

</div>

see that I created a function to perform the sum of the value field but could not mount the query when using sum it returns me that it is not a recognized function of the IC, my idea is that this sum be performed also at the time when the user does a search using filters for example, it filters the budgets of X representative and at the end of the list the total sum. If anyone can tell me the best way to do that I’d appreciate it.

Answer 1

The simplest suggestion would be to generate the sum at the time of the loop:

$total = 0;

// Antes desta linha, adicione a variável acima
foreach ( $orcamentos as $orcamento ) {
    // Adicione depois para somar o valor ao total
    $total += $orcamento->valor;
    ?>

If you want to enter the sum in the query, you can make the following change:

// De
$this->db->select('orcdat, situacao, ocod, valor, cnome, pnome,numero,rnome');

// Para
$this->db->select('sum(valor), orcdat, situacao, ocod, valor, cnome, pnome, numero, rnome', FALSE);

Note: $this->db->select() accepts an optional second parameter. If you set it to FALSE, the CI will not attempt to protect its field or table names. This is useful if you need a composite selection statement where the automatic escape of fields can break them.