title: Codeigniter pass output of a select to IN Clause of another select using subqueries
body: <p>I want to take advantage of self-security of Active Records in Codeigniter. </p>
  
  <p>The Requirement is: </p>
  
  <ol>
  <li>Get some serial numbers from first table based on some criteria</li>
  <li>Then check which of those serial numbers are present in second table based on some complex criteria.</li>
  <li>Finally show the results from first table which are not present in the second table resultset.</li>
  </ol>
  
  <p>I have the following code in CI:</p>
  
  <pre><code>$this-&gt;db-&gt;select('sno');
  $this-&gt;db-&gt;from('table1');
  $this-&gt;db-&gt;where("cid",$cid);
  $subquery=$this-&gt;db-&gt;get_compiled_select();
  
  $this-&gt;db-&gt;select("pid")-&gt;from("table2");
  $this-&gt;db-&gt;group_start();
      $this-&gt;db-&gt;group_start();
          $this-&gt;db-&gt;where("col1 &lt;",$cin);
          $this-&gt;db-&gt;where("col2 &gt;",$cin);
      $this-&gt;db-&gt;group_end();
      $this-&gt;db-&gt;or_group_start();
          $this-&gt;db-&gt;where("col1 &lt;",$cout);
          $this-&gt;db-&gt;where("col2 &gt;",$cout);
      $this-&gt;db-&gt;group_end();
      $this-&gt;db-&gt;or_group_start();
          $this-&gt;db-&gt;where("col1 &gt;",$cin);
          $this-&gt;db-&gt;where("col1 &lt;",$cout);
      $this-&gt;db-&gt;group_end();
      $this-&gt;db-&gt;or_group_start();
          $this-&gt;db-&gt;where("col1",$cin);
          $this-&gt;db-&gt;or_where("col2",$cout);
          $this-&gt;db-&gt;or_where("col1",$cout);
          $this-&gt;db-&gt;or_where("col2",$cin);
      $this-&gt;db-&gt;group_end();
      $this-&gt;db-&gt;or_group_start();
          $this-&gt;db-&gt;where("col2 &gt;",$cin);
          $this-&gt;db-&gt;where("col2 &lt;",$cout);
      $this-&gt;db-&gt;group_end();
  $this-&gt;db-&gt;group_end();
  $this-&gt;db-&gt;where_in("col3",$subquery);
  $sbquery = $this-&gt;db-&gt;get_compiled_select();
  
      $this-&gt;db-&gt;from('table1');
      $this-&gt;db-&gt;where("tid",$cid);
      $this-&gt;db-&gt;where_not_in("cid",$sbquery);
      $result=$this-&gt;db-&gt;get_compiled_select();
      var_dump($result);
      exit;
  </code></pre>
  
  <p>This code shows the following query (as the result of var_dump): </p>
  
  <pre><code>SELECT * FROM `table1` WHERE `tid` = '6' AND `cid` NOT IN('SELECT `pid`\nFROM `table2`\nWHERE (\n (\n`col1` &lt; \'2018-12-10\'\nAND `col2` &gt; \'2018-12-10\'\n )\nOR (\n`col1` &lt; \'2018-12-16\'\nAND `col2` &gt; \'2018-12-16\'\n )\nOR (\n`col1` &gt; \'2018-12-10\'\nAND `col1` &lt; \'2018-12-16\'\n )\nOR (\n`col1` = \'2018-12-10\'\nOR `col2` = \'2018-12-16\'\nOR `col1` = \'2018-12-16\'\nOR `col2` = \'2018-12-10\'\n )\nOR (\n`col2` &gt; \'2018-12-10\'\nAND `col2` &lt; \'2018-12-16\'\n )\n )\nAND `col3` IN(\'SELECT `sno`\\nFROM `table1`\\nWHERE `cid` = \\\'6\\\'\')')
  </code></pre>
  
  <p>This gives wrong results when I use the following code (last 3 lines of code):</p>
  
  <pre><code>$result=$this-&gt;db-&gt;get()-&gt;result();
  var_dump($result);
  exit;
  </code></pre>
  
  <p>If I hit the same query directly in SQL, I am getting the same wrong results. </p>
  
  <p>But, if I remove slashes, single quotes, new line and other special characters, to hit the rewritten query directly in sql, I get the perfectly accurate results.</p>
  
  <pre><code>SELECT * FROM `table1` WHERE `tid` = '6' AND `cid` NOT IN(SELECT `pid` FROM `table2` WHERE ((`col1` &lt; '2018-12-10' AND `col2` &gt; '2018-12-10') OR (`col1` &lt; '2018-12-16' AND `col2` &gt; '2018-12-16') OR (`col1` &gt; '2018-12-10' AND `col1` &lt; '2018-12-16') OR (`col1` = '2018-12-10' OR `col2` = '2018-12-16' OR `col1` = '2018-12-16' OR `col2` = '2018-12-10') OR (`col2` &gt; '2018-12-10' AND `col2` &lt; '2018-12-16')) AND `col3` IN(SELECT `sno` FROM `table1` WHERE `cid` = '6'))
  </code></pre>
  
  <p>Please suggest a solution.</p>