MySQL CASE WHEN END in Codeigniter

Question

目前我正在从事与商店管理相关的Codeigniter项目。在模型中，我使用了以下函数来获得购买&通过控制器发布视图摘要。

功能

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(一切都很好。但是案例构造函数触发以下错误

发生数据库错误错误编号：1064

您的SQL语法有错误；请查看与您的MySQL服务器版本相对应的手册，以获得在附近使用的正确语法。CCD_ 1。第1行处的officer_name ENDASsupplierFR’

03(我试图解决这个问题。但是没有得到想要的输出。我找不到错误&可能出了什么问题。有人能帮我吗？

供应商表

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

军官桌

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

输出

+------+--------------------------+------------+------------+--------------+
| 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        |
+------+--------------------------+------------+------------+--------------+

Answer 1

尝试更改：

$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->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不喜欢*之后select语句中的内容，很多时候它会拒绝使用