Currently I am working on Codeigniter projected related to stores management. In model I used the following function to get the purchase & issues summary for the view through controller.

Function

function issueDetailReport($id,$start,$end)
 $this->db->select('*, store_update_stock_details.item,
 CASE 
 WHEN store_update_stock.order_status = "purchase" THEN tbl_supplier.supplier_name
 WHEN store_update_stock.order_status = "issue" THEN store_officer.officer_name 
 END AS supplier');
 $this->db->from('store_update_stock');
 $this->db->join('store_update_stock_details','store_update_stock.update_stock_id=store_update_stock_details.update_stock_id');
 $this->db->join('store_officer','store_update_stock.supplier=store_officer.officer_id');
 $this->db->join('tbl_supplier','store_update_stock.supplier=tbl_supplier.supplier_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=$id");
 //$this->db->where('store_update_stock.update_stock_id in (select update_stock_id from store_update_stock) ');
 if($start!=NULL && $end!=NULL)
 $this->db->where("store_update_stock.billed_date BETWEEN '$start' AND '$end'");
 $this->db->order_by('store_update_stock.purchased_date','DESC');
 $q=$this->db->get();
 if($q->num_rows()>0)
 return $q->result();
 
 return false;
 

02) All are working fine. But the case constructor fires the following error

A Database Error Occurred
Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.order_status = "issue" THEN store_officer.officer_name END AS supplier FR' at line 1

03) I tried to solve the issue. But did't get the desired output. I can't find the error & what may be wrong. Can anyone help me ?

Supplier Table

+-------------+---------------+
| supplier_id | supplier_name |
+-------------+---------------+
| 500 | ABC |
| 501 | DEF |
| 502 | GHI |
| 503 | JKL |
+-------------+---------------+

officer Table

+------------+--------------+
| officer_id | officer_name |
+------------+--------------+
| 1000 | Danial |
| 1001 | Jhon |
| 1002 | William |
| 1003 | Patrick |
| 1004 | Salman |
+------------+--------------+

Output

+------+--------------------------+------------+------------+--------------+
| item | supplier / officer_name | start | end | order_status |
+------+--------------------------+------------+------------+--------------+
| A4 | ABC | 2018-11-01 | 2018-11-01 | purchase |
| A5 | DEF | 2018-11-01 | 2018-11-01 | purchase |
| A3 | Danial | 2018-11-02 | 2018-11-02 | issue |
| B5 | Jhon | 2018-11-05 | 2018-11-05 | issue |
+------+--------------------------+------------+------------+--------------+

Try changing:

$this->db->select('*, store_update_stock_details.item,
 CASE 
 WHEN store_update_stock.order_status = "purchase" THEN tbl_supplier.supplier_name
 WHEN store_update_stock.order_status = "issue" THEN store_officer.officer_name 
 END AS supplier');

To:

$this->db->select('store_update_stock_details.item,
 CASE 
 WHEN store_update_stock.order_status = "purchase" THEN tbl_supplier.supplier_name
 WHEN store_update_stock.order_status = "issue" THEN store_officer.officer_name 
 END AS supplier, *');

MySQL doesn't like stuff in the select statements after *'s and many times it'll refuse to work