CodeIgniter中AJAX传参查询逗号分隔location字段数据问题

现有tabel_item表结构如下：

id |    name    | location
1  |   item a   |   3,5 
2  |   item b   |   4

已编写的CodeIgniter模型代码：

public function db_barangGetMaster($postData){
    if(isset($postData['location']) ){
      $this->db->select("*");
      $this->db->from('tabel_item as a');
      $this->db->where("location", $postData['location']);
      
      $response = array();
      $query = $this->db->get()->result();
      foreach($query as $row ){
            $response[] = array(
            "id" =>$row->id,
            "name" =>$row->name,
            "lokasi" =>$row->location
        );
      }
    if (count($response)) {
        return $response;
    } else {
        return ['response' => 'not found'];
    }}
 }

当前问题：传入location参数为3或5时，无法查询到location为"3,5"的item a数据，需要修改实现：传入3或5返回item a，传入4返回item b的功能。

将原有的精确匹配条件替换为MySQL的FIND_IN_SET函数，该函数可检测单个值是否存在于逗号分隔的字符串列表中。修改后的模型代码如下：

public function db_barangGetMaster($postData){
    if(isset($postData['location']) ){
      $this->db->select("*");
      $this->db->from('tabel_item as a');
      // 使用FIND_IN_SET替换原精确匹配，同时转义参数避免SQL注入
      $this->db->where("FIND_IN_SET(" . $this->db->escape($postData['location']) . ", location) > 0");
      
      $response = array();
      $query = $this->db->get()->result();
      foreach($query as $row ){
            $response[] = array(
            "id" =>$row->id,
            "name" =>$row->name,
            "lokasi" =>$row->location
        );
      }
    if (count($response)) {
        return $response;
    } else {
        return ['response' => 'not found'];
    }}
 }

关键说明 #

• FIND_IN_SET(匹配值, 目标字段)会返回匹配值在逗号分隔列表中的位置，返回值大于0即表示存在匹配
• 使用$this->db->escape()对传入参数转义，避免SQL注入风险
• 修改后，传入3、5时会匹配到location为"3,5"的记录，传入4时匹配到location为"4"的记录，完全符合需求

内容的提问来源于stack exchange，提问作者begadang nyok