首页 文章

如何使用group_by codeIgniter基于id对数据库中的数据求和

提问于
浏览 次
2

我试图通过group_by从数据库中获取数据

名称经销商ID经销商数量
詹姆斯001 5
托马斯002 7
比利005 4
詹姆斯001 6
托马斯002 9

我想要Out out

詹姆斯= 11
托马斯= 16
比利= 4

这是我的控制器

$data=array('reseller'  =>$this->hasil_m->view_hasil(),
            'isi'       =>'admin/hasil'
        );
$this->load->view('dashboard/wrapper',$data);

我的模特

function view_hasil() {
    $this->db->select('*');
    $this->db->from('tb_produk as pro');
    $this->db->join('tb_reseller as res', 'pro.reseller_produk  = res.nomor_reseller');
    $this->db->join('tb_pelanggan as pel', 'pro.id_barcode  = pel.id_barcode');     
    $this->db->group_by('res.nomor_reseller');
    $ambil = $this->db->get('');
    if ($ambil->num_rows() > 0) {
        foreach ($ambil->result() as $data) {
            $hasil[] = $data;
        }
        return $hasil;
    }   
}

我的观点

<table class="table table-hover table-striped">
                <thead>
                <tr>    
                    <th><div align="center">No</th>
                     <th class="text-center">Nama Reseller</th>
                    <th><div align="center">ID Reseller</th>
                    <th><div align="center">Jumlah Produk Terjual</th>
                    <th><div align="center">Biaya ADM</th>                  
                    <th><div align="center">Total Di Terima</th>
                    <th><div align="center">Action</div></th>
                </tr>
                </thead>
                <tbody>

                <?php
                    if ($reseller == NULL) {
                    ?>
                    <div class="col-md-12">
                    <div class="alert alert-danger text-center" role="alert">Tanggal tidak ditemukan</div>
                    </div>
                    <?php
                    } else {
                    $no = 1;
                    $total_sum=0;
                    foreach ($reseller as $row) {
                    ?>
                    <tr>
                        <td><div align="center"><?php echo $no; ?></div></td>
                        <td class="fontCap"><?php echo $row->nama_reseller; ?></td>
                        <td><div align="center"><?php echo $row->nomor_reseller; ?></div></td>
                        <td class="fontCap text-center"><?php echo $total_sum+=$row;?></td>
                        <td class="fontCap text-center"></td>
                        <td><div align="center"></div></td>
                        <td><div align="center">
                            <a href="<?php echo site_url('admin/pelanggan/delete/' .$row->id_koperasi); ?>" title="delete" onclick="return confirm('Anda yakin ingin menghapus produk <?php echo $row->nama_produk; ?> dengan ID <?php echo $row->id_barcode; ?> pada database?');" class="btn btn-danger btn-xs"><i class="fa fa-trash-o"></i></a>
                            </div>
                        </td>
                            <?php
                            $no++;
                        }
                    }
                        ?>
                    </tr>
                </tbody>
            </table>
php mysql codeigniter-3

3 回答

  • 0

    您需要先将其求和，然后按此字段分组 Name Reseller ID reseller

    $this->db->select('nama_reseller,ID_Reseller,sum (Quantity) as qty');
   ///
   ///
   ///    
   $this->db->group_by(array('Name_Reseller','ID_reseller'));

    或者，如果你想要你可以 ->query() 简单的方式

    $this->db->query('select pro.*,SUM(pro.Quantity) as Qty,res.*,pel.* from tb_produk as pro 
                    left join tb_reseller as res on pro.reseller_produk  = res.nomor_reseller
                    left join tb_pelanggan as pel on pro.id_barcode  = pel.id_barcode
                    group by Name_Reseller,ID_reseller');
    回复于
  • 0

    尝试选择如下：

    $this->db->select('SUM(pro.jumlah) as totalQuantity, pro.*');
    回复于
  • 2

    在您的选择中使用 SUM(Quantity) . 见下面的代码

    function view_hasil() 
 {
    $this->db->select('SUM(pro.Quantity) as Qty,YOUR_OTHER_FIELDS');
    $this->db->from('tb_produk as pro');
    $this->db->join('tb_reseller as res', 'pro.reseller_produk  = res.nomor_reseller');
    $this->db->join('tb_pelanggan as pel', 'pro.id_barcode  = pel.id_barcode');
    $this->db->group_by(['res.nomor_reseller','pro.ID_reseller']);
    $ambil = $this->db->get();
    if ($ambil->num_rows() > 0) {
       //no need to looping it again $ambil->result() this is also an array
        return $ambil->result();
   }   
}
    回复于

相关问题