duankui1532 2017-07-05 12:19
浏览 113
已采纳

从codeigniter中的where_in中删除单引号

I am working on a search functionality. I have created a search form in which user can search an Application based on Type,ope & Formate.

I have used a subquery in my join query to get the desired result. I have tested my query in MySQL Workbench nad it's working fine.

But when I tried that same query in Codeigniter using query builder technique then I am facing a problem.

Here is the query which is worked fine in workbench:

SELECT (*)
FROM `App`
LEFT JOIN `App_type` 
ON `App_type`.`app_id` = `App`.`id`
LEFT JOIN `App_formate` 
ON `App_formate`.`app_id` = `App`.`id`
WHERE `App`.`id` IN(select app_id FROM App_type WHERE type_id in (3,2,6) group by app_id HAVING COUNT(*) = 3)
AND `App_formate`.`formate_id` IN('1', '3')
AND `jobs`.`ope_min` <= '3'
AND `jobs`.`ope_max` >= '3'
GROUP BY `jobs`.`id`;

This is the join query which I use:

$subquery = "select app_id FROM App_type WHERE type_id in ($selected_type) group by app_id HAVING COUNT(*) = $type_count";

$search_app_query = $this->db
    ->select('*')
    ->from('App')
    ->join('App_type', 'App_type.app_id = App.id', 'left outer')
    ->join('App_formate', 'App_formate.app_id = App.id', 'left outer')      
    ->where_in('App.id',$subquery)  //<-- Here is the problem
    ->where_in('App_formate.formate_id',$data['selected_formates'])
    ->where('App.ope_min <=',$data['ope_value'])
    ->where('App.ope_max >=',$data['ope_value'])    
    ->group_by("App.id", "desc")
    ->get();

While I am debugging this problem it shows the

 I have found the problem is in this part of the query:
 "WHERE `App`.`id` IN('select app_id 
 FROM App_type 
 WHERE type_id in (3,2,6) 
 group by app_id HAVING COUNT(*) = 3')"

that single quote in this subquery is creating a problem.

What I have tried so far:

To remove this single quote I have tried

  1. REPLACE($subquery, '''', '')
  2. ->where_in('App.id',trim($subquery,"'"))
  3. $subquery_improved = substr($subquery, 1, -1);

But all this solution is not working. They are not removing the single quote.

Note: I am aware of $this->db->query() but do not want to use that.

  • 写回答

11条回答 默认 最新

  • duanhuangyun3887 2017-07-21 07:18
    关注

    Your task looks pretty simple

    instead of

    ->where_in('App.id',$subquery)  //<-- Here is the problem
    

    you can try the following

    ->where("App.id IN (".$subquery.")",NULL, false)
    

    You can find this exact information in the Codeigniter Documentation here (point 4 and the section below).

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(10条)

报告相同问题?

悬赏问题

  • ¥66 换电脑后应用程序报错
  • ¥50 array数据同步问题
  • ¥15 pic16F877a单片机的外部触发中断程序仿真失效
  • ¥15 Matlab插值拟合差分微分规划图论
  • ¥15 keil5 target not created
  • ¥15 C/C++数据与算法请教
  • ¥15 怎么找志同道合的伙伴
  • ¥20 如何让程序ab.eXe自已删除干净硬盘里的本文件自己的ab.eXe文件
  • ¥50 爬虫预算充足,跪巨佬
  • ¥15 滑块验证码拖动问题悬赏