ci 모델 쿼리 안에 where in 변수

질문 Q #22831

박종윤 2020-10-29 18:50:18

안녕하세요 모델 쿼리를 작성하는데 위에 쿼리문은 잘 작동하는데 
if 문 아래에 있는 쿼리에 where in 조건을 주고 변수를 넣으려고 하면 작동이 되지 않습니다. 저 밑줄 부분에 변수를 사용하려면 어떻게 해야 하나요? ㅠㅠ

    function get_ckp_group_board($params){

        $query = $this->db->query('select name,sum(fund),sum(commission) from (


        SELECT  substring_index(cm.ckp_nickname, "_", 1) as name,
                        round(sum(chs.ckp_sum_amount),1) as fund,
                        0 as commission
        FROM ckp_master as cm
                 LEFT JOIN ck_code_setting as ccs ON ckp_type = code_seq
                 LEFT JOIN ckp_history_stat as chs ON cm.ckp_master_id = chs.ckp_master_id
        where
                ccs.title like "%구매자금%"
        GROUP BY name,ccs.title

        union all

        SELECT  substring_index(cm.ckp_nickname, "_", 1) as name,
                        0 as fund ,
                        round(sum(chs.ckp_sum_amount),1) as commission
        FROM ckp_master as cm
                 LEFT JOIN ck_code_setting as ccs ON ckp_type = code_seq
                 LEFT JOIN ckp_history_stat as chs ON cm.ckp_master_id = chs.ckp_master_id
        where
                ccs.title like "%물류비%"
        GROUP BY name,ccs.title

) as v

group by name
order by name asc;');

        if ($params['search_query'] <> "") {
            $query = $this->db->query('select name,sum(fund),sum(commission) from (


        SELECT  substring_index(cm.ckp_nickname, "_", 1) as name,
                        round(sum(chs.ckp_sum_amount),1) as fund,
                        0 as commission
        FROM ckp_master as cm
                 LEFT JOIN ck_code_setting as ccs ON ckp_type = code_seq
                 LEFT JOIN ckp_history_stat as chs ON cm.ckp_master_id = chs.ckp_master_id
        where
                ccs.title like "%구매자금%"
        GROUP BY name,ccs.title

        union all

        SELECT  substring_index(cm.ckp_nickname, "_", 1) as name,
                        0 as fund ,
                        round(sum(chs.ckp_sum_amount),1) as commission
        FROM ckp_master as cm
                 LEFT JOIN ck_code_setting as ccs ON ckp_type = code_seq
                 LEFT JOIN ckp_history_stat as chs ON cm.ckp_master_id = chs.ckp_master_id
        where
                ccs.title like "%물류비%"
        GROUP BY name,ccs.title

) as v
where name in ('$params['search_query']')
group by name
order by name asc;');

        }


        return $query->result_array();
    }

답변 5

#1 변종원(웅파) 2020-10-29 18:56:42

$params['search_query'] 내용은요?

#2 한대승(불의회상) 2020-10-29 20:31:52

내용은 잘 모르겠지만...

where name in ('$params['search_query']')

를 아래처럼 바꿔서 테스트 해보세요.

where name in (' . "'" . implode("', '", $params['search_query']) . "'" . ')

#3 박종윤 2020-10-29 20:34:16

컨트롤러
$params['search_query'] = $search_query;
$data['arr_ckp_group_board'] = $this -> authority -> getCKPBoardGroup($params);

라이브러리
public function getCKPBoardGroup($params){
    return $this -> ci -> ckp_model -> get_ckp_group_board($params);
}

모델   
 function get_ckp_group_board($params){

        $query = $this->db->query('select name,sum(fund),sum(commission) from (


        SELECT  substring_index(cm.ckp_nickname, "_", 1) as name,
                        round(sum(chs.ckp_sum_amount),1) as fund,
                        0 as commission
        FROM ckp_master as cm
                 LEFT JOIN ck_code_setting as ccs ON ckp_type = code_seq
                 LEFT JOIN ckp_history_stat as chs ON cm.ckp_master_id = chs.ckp_master_id
        where
                ccs.title like "%구매자금%"
        GROUP BY name,ccs.title

        union all

        SELECT  substring_index(cm.ckp_nickname, "_", 1) as name,
                        0 as fund ,
                        round(sum(chs.ckp_sum_amount),1) as commission
        FROM ckp_master as cm
                 LEFT JOIN ck_code_setting as ccs ON ckp_type = code_seq
                 LEFT JOIN ckp_history_stat as chs ON cm.ckp_master_id = chs.ckp_master_id
        where
                ccs.title like "%물류비%"
        GROUP BY name,ccs.title

) as v

group by name
order by name asc;');

        if ($params['search_query'] <> "") {
            $query = $this->db->query('select name,sum(fund),sum(commission) from (


        SELECT  substring_index(cm.ckp_nickname, "_", 1) as name,
                        round(sum(chs.ckp_sum_amount),1) as fund,
                        0 as commission
        FROM ckp_master as cm
                 LEFT JOIN ck_code_setting as ccs ON ckp_type = code_seq
                 LEFT JOIN ckp_history_stat as chs ON cm.ckp_master_id = chs.ckp_master_id
        where
                ccs.title like "%구매자금%"
        GROUP BY name,ccs.title

        union all

        SELECT  substring_index(cm.ckp_nickname, "_", 1) as name,
                        0 as fund ,
                        round(sum(chs.ckp_sum_amount),1) as commission
        FROM ckp_master as cm
                 LEFT JOIN ck_code_setting as ccs ON ckp_type = code_seq
                 LEFT JOIN ckp_history_stat as chs ON cm.ckp_master_id = chs.ckp_master_id
        where
                ccs.title like "%물류비%"
        GROUP BY name,ccs.title

) as v
where name in ('$params['search_query']')
group by name
order by name asc;');

        }

이런식으로 넘겨주는데 위에 쿼리는 되는데 똑같이 복사해서 밑에 변수만 추가해주려니 밑줄 부분에 에러가 나네요 ㅠㅠ

#4 박종윤 2020-10-29 20:40:17

한대승님 감사합니다!!! implode 빼고 알려주신대로 하니까 바로 성공했어요 감사합니다 ㅠㅠ

#5 한대승(불의회상) 2020-10-29 21:09:35

ㅎㅎ 빼고 하니 된다니 신기하네요.

echo $this->db->last_query();

쿼리 출력해서 확인해 보세요.