codeigniter - MySQL Available Balance has not match with Actual Balance

Question

I have a MySQL database that manages receives and issues of items. One of the tables includes as follows. There are different prices available for same item.

store_update_stock_details Table

+-------------------------+------+-----+------------+
| update_stock_details_id | item | qty | unit_price |
+-------------------------+------+-----+------------+
|                       1 |    4 |   8 |      35.00 |
|                       2 |    4 |  30 |      38.50 |
|                       3 |    4 |  20 |      42.00 |
|                       4 |    4 | -11 |      38.50 |
|                       5 |    4 |  -1 |      38.50 |
|                       6 |    4 |  -1 |      35.00 |
|                       7 |    4 |  -1 |      35.00 |
|                       8 |    4 |  -1 |      35.00 |
|                       9 |    4 |  -1 |      35.00 |
|                      10 |    4 |  -4 |      35.00 |
+-------------------------+------+-----+------------+ 

(-) sign denote the issues in the table. Then I need to get available balances as follows after performing issues.

+------+-----+------------+
| item | qty | unit_price |
+------+-----+------------+
|    4 |  18 |      38.50 |
|    4 |  20 |      42.00 |
+------+-----+------------+

I used the following query to do that.

public function isExistProduct($q)
    {
        if (!empty($q)) {           

            $this->db->select("store_item.*, store_update_stock.*, sum(qty) as qty, unit_price");
            $this->db->from('store_update_stock_details');
            $this->db->join('store_update_stock', 'store_update_stock_details.update_stock_id=store_update_stock.update_stock_id');
            $this->db->join('store_item', 'store_update_stock_details.item=store_item.item_id');            
            $this->db->where("store_update_stock.status=1 and store_item.item_id= $q");
            $this->db->where("store_update_stock_details.qty != 0 ");
            $this->db->group_by('store_update_stock_details.unit_price','store_item.item_id');

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

            if ($q1->num_rows() > 0) {
                return $q1->result_array();
            }
            return 0;
        }
    }

But the function returns a result as follows :

+------+-----+------------+
| item | qty | unit_price |
+------+-----+------------+
|    4 |   0 |      35.00 |
|    4 |  18 |      38.50 |
|    4 |  20 |      42.00 |
+------+-----+------------+

I do not need to get the balances of unavailability (qty=0) of items. What can be changed in my query ?. Can anyone help ?

Answer 1

You must use having to filter the result

Documentation are here :

https://codeigniter.com/user_guide/database/query_builder.html#looking-for-similar-data

So in your case:

$this->db->having('qty <> 0 ');