如何从表CodeIgniter和MySql中加总数量

Question

I am new to CodeIgniter and still exploring it. I just want to ask if, how can I add all the quantity of the stocks based on the product id. I have five tables that have relations to each other, the product, brand, category, size and stocks.

The category, brand, and size is foreign key on product table while product table is foreign key on stocks table.

Every time there is a stock, the quantity and date of a specific product will be saved to the database. I want to know how to sum all the stocks of the specific product and display all the total quantity.

Database: product table

CREATE TABLE `products` (
  `prod_id` int(11) NOT NULL AUTO_INCREMENT,
  `prod_code` varchar(45) NOT NULL,
  `prod_desc` varchar(55) NOT NULL,
  `brand_id` int(11) DEFAULT NULL,
  `category_id` int(11) NOT NULL,
  `size_id` int(11) DEFAULT NULL,
  `original_price` decimal(10,2) NOT NULL,
  `date_registered` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `date_modified` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`prod_id`),
  KEY `category_fk_idx` (`category_id`),
  KEY `size_fk_idx` (`size_id`),
  KEY `brand_id_idx` (`brand_id`),
  CONSTRAINT `brand_id` FOREIGN KEY (`brand_id`) REFERENCES `brand` (`brand_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `category_fk` FOREIGN KEY (`category_id`) REFERENCES `category` (`category_id`),
  CONSTRAINT `size_fk` FOREIGN KEY (`size_id`) REFERENCES `sizes` (`size_id`)
) 

stocks table

CREATE TABLE `stocks` (
  `stock_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) DEFAULT NULL,
  `quantity` double DEFAULT NULL,
  PRIMARY KEY (`stock_id`),
  KEY `prod_fk_idx` (`product_id`),
  CONSTRAINT `prod_fk` FOREIGN KEY (`product_id`) REFERENCES `products` (`prod_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) 

my view (product_view.php)

  <div class="col-sm-10" id="main">

    <div class="table-wrapper">
       <div id="content">
           <?php echo $this->session->flashdata('successMessage');
                echo $this->session->flashdata('errorMessage'); ?>
<legend class="text-danger"><h1>Product List</h1></legend>
            <?php   $tableAttr = array(
                    'table_open' => '<table class="table table-responsive table-striped table-hover" id="item_tbl">',
                    );
                                    $item_table = $this->table->set_heading('No.','PRODUCT CODE','PRODUCT DESCRIPTION','BRAND', 'CATEGORY', 'SIZE','QUANTITY','ACTION');
                $item_table = $this->table->set_template($tableAttr);
                $num = 0;
                foreach ($items as $item) {
                    $itemName = urlencode($item->prod_desc);
                    $num++;
                    $item_table = $this->table->add_row($num, $item->prod_code, $item->prod_desc, $item->brand_name,$item->category_name,$item->size,$item->quantity,"

                        <a href='".base_url("item/update/$itemName")."'><button class='btn btn-info btn-sm'>EDIT</button></a> 
                                                    <a href='".base_url("item/stock_in/$itemName")."'><button class='btn btn-primary btn-sm'>STOCK IN</button></a> 
                        ");
                }
                echo $this->table->generate($item_table);  ?>
                </div>

</div>                            
</div>  

controller

    public function home () {
    $this->load->model('item_model');

    $data['items'] = $this->item_model->itemList();
    $data['page'] = 'home';

    $this->load->view('header',$data);
    $this->load->view('side_menu');
    $this->load->view('product_view',$data);
    $this->load->view('footer');
}

model (item_model.php)

    public function itemList () {
    $this->load->database();

$this->db->select('p.*,c.category_name,s.size,b.brand_name,t.quantity')

            ->from('stocks t');
              $this->db->join('products p','p.prod_id = t.product_id','full')
                    ->join('category c','p.category_id = c.category_id','full');
               $this->db->join('sizes s','p.size_id = s.size_id','full');
                $this->db->join('brand b','p.brand_id = b.brand_id','full');

            $result=$this->db->get()->result();

    return $result;
}

What I want/ I mean to display in my website is

+-----+--------------+--------------+-------------------+
| ID  | Product Code | Description  | Stocks Quantity   |
+-----+--------------+--------------+-------------------+
| 001 |   12345      | sample       |  12               |
| 002 |   23456      |  try         |  15               |
+-----+--------------+--------------+-------------------+

Inside my database table

+-----+--------------+--------------+
| ID  | Product_Id   | Quantity     |
+-----+--------------+--------------+
| 1   |   1          |     10       | 
| 2   |   3          |     7        |
+-----+--------------+--------------+
| 3   |   1          |     2        | 
| 4   |   3          |     8        |
+-----+--------------+--------------+

I hope you would help guys.

Answer 1

Use SUM() and group by :

$this->db->select('p.*,c.category_name,s.size,b.brand_name,SUM(t.quantity) as total_quantity',false)
               ->from('stocks t')
               ->join('products p','p.prod_id = t.product_id','full')
               ->join('category c','p.category_id = c.category_id','full')
               ->join('sizes s','p.size_id = s.size_id','full')
               ->join('brand b','p.brand_id = b.brand_id','full')
               ->group_by('t.product_id');
          $result=$this->db->get()->result();