CI 묻고 답하기

제목 ci 모델 쿼리 안에 where in 변수
카테고리 CI 2, 3
글쓴이 박종윤 작성시각 2020/10/29 18:50:18
댓글 : 5 추천 : 0 스크랩 : 0 조회수 : 9280   RSS 
안녕하세요 모델 쿼리를 작성하는데 위에 쿼리문은 잘 작동하는데 
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();
    }
 다음글 CI3 과 CI4 중에 어떤걸 써야 될지 고민입니다. (5)
 이전글 foreach 문을 2번 사용 (5)
목록

댓글

변종원(웅파) / 2020/10/29 18:56:42 / 추천 0 
$params['search_query'] 내용은요?
한대승(불의회상) / 2020/10/29 20:31:52 / 추천 0

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

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

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

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

 
박종윤 / 2020/10/29 20:34:16 / 추천 0 
컨트롤러
$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;');

        }

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

 

 
박종윤 / 2020/10/29 20:40:17 / 추천 0

한대승님 감사합니다!!! implode 빼고 알려주신대로 하니까 바로 성공했어요 감사합니다 ㅠㅠ
한대승(불의회상) / 2020/10/29 21:09:35 / 추천 1

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

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

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