title: how to union two querys in codeigniter
body: <p>I have 2 querys in codeigniter,am using for ajax data tables those querys. Before that it will worked when i use single 
  query. After adding one more query for UNION it is not working for me. Please find my model functions using for 
  data tables.</p>
  
  <h2>Model.php (Working this function)Using single query in _get_online_supplier_reports_datatable() function</h2>
  
  <pre><code>&lt;?php
  public function _get_online_supplier_reports_datatable(){
  
       $this-&gt;db-&gt;select('"Amadeus" AS SupplierName,TST_Type,TKT_Type,TKTNumber,TKT_FO_REF,BasePrice,TaxPrice,Commission1,(BasePrice+TaxPrice) AS TotalPrice,"issued" AS tkt_type,createdby,TicketedDate as created_date,CONCAT(a3m.firstname," ",a3m.lastname) AS created_name,a3m.account_id,a3m.locationvalue AS Location_ID,a3m.locationdescription AS Location_Name,a3m.costcentervalue AS CostCenter_ID,a3m.costcenterdescription AS CostCenter_Name');
          $this-&gt;db-&gt;from($this-&gt;traveldb-&gt;database.".booking_price bp");
          $this-&gt;db-&gt;join('a3m_account_details a3m','a3m.account_id=createdby','left');
          $this-&gt;db-&gt;where('TKTNumber !=', '');
  
  
          if(isset($_POST['order'])) // here order processing
          {
              $this-&gt;db-&gt;order_by($this-&gt;column_order_online_supplier_reports_match[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
          } 
          else if(isset($this-&gt;order_online_supplier_reports_match))
          {
              $order = $this-&gt;order_online_supplier_reports_match;
              $this-&gt;db-&gt;order_by(key($order), $order[key($order)]);
          }
  
      }
  
      public function get_online_supplier_reports_datatable(){
          $this-&gt;_get_online_supplier_reports_datatable();
          if($_POST['length'] != -1)
          $this-&gt;db-&gt;limit($_POST['length'], $_POST['start']);
          $query = $this-&gt;db-&gt;get();
           return $query-&gt;result();
      }
      public function count_filtered_online_supplier_reports_rec(){
          $this-&gt;_get_online_supplier_reports_datatable();
          $query = $this-&gt;db-&gt;get();
          return $query-&gt;num_rows();
      } 
  
      ?&gt;
  </code></pre>
  
  <p>I have added One more query in _get_online_supplier_reports_datatable() function for Union both querys .after that it is not working </p>
  
  <pre><code>&lt;?php
  public function _get_online_supplier_reports_datatable(){
  
          //Query 1 here
          $this-&gt;db-&gt;select('"Amadeus" AS SupplierName,TST_Type,TKT_Type,TKTNumber,TKT_FO_REF,BasePrice,TaxPrice,Commission1,(BasePrice+TaxPrice) AS TotalPrice,"issued" AS tkt_type,createdby,TicketedDate as created_date,CONCAT(a3m.firstname," ",a3m.lastname) AS created_name,a3m.account_id,a3m.locationvalue AS Location_ID,a3m.locationdescription AS Location_Name,a3m.costcentervalue AS CostCenter_ID,a3m.costcenterdescription AS CostCenter_Name');
          $this-&gt;db-&gt;from($this-&gt;traveldb-&gt;database.".booking_price bp");
          $this-&gt;db-&gt;join('a3m_account_details a3m','a3m.account_id=createdby','left');
          $this-&gt;db-&gt;where('TKTNumber !=', '');
          $query1 = $this-&gt;db-&gt;get_compiled_select(); 
  
          //QUery2 Here
           $this-&gt;db-&gt;select('"Amadeus" AS SupplierName,"" AS TST_Type,"" AS  TKT_Type,br_tkt AS TKTNumber,"" as TKT_FO_REF,br_fare_paid_B as BasePrice,br_tax_refund_TXT as TaxPrice,br_cancellation_penalty as Commission1,br_fare_total_RFT AS TotalPrice,br_cancel_type AS tkt_type,br_added_by as createdby,br_refund_date as created_date,CONCAT(a3m.firstname," ",a3m.lastname) AS created_name,a3m.account_id,a3m.locationvalue AS Location_ID,a3m.locationdescription AS Location_Name,a3m.costcentervalue AS CostCenter_ID,a3m.costcenterdescription AS CostCenter_Name');
          $this-&gt;db-&gt;from($this-&gt;traveldb-&gt;database.".booking_refund bp");
          $this-&gt;db-&gt;join('a3m_account_details a3m','a3m.account_id=br_added_by','left');
          $this-&gt;db-&gt;where('br_tkt !=', '');
          $query2 = $this-&gt;db-&gt;get_compiled_select(); 
  
      //Union both querys 
       $query=  $this-&gt;db-&gt;query($query1." UNION ALL".$query2);
  
          if(isset($_POST['order'])) // here order processing
          {
              $this-&gt;db-&gt;order_by($this-&gt;column_order_online_supplier_reports_match[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
          } 
          else if(isset($this-&gt;order_online_supplier_reports_match))
          {
              $order = $this-&gt;order_online_supplier_reports_match;
              $this-&gt;db-&gt;order_by(key($order), $order[key($order)]);
          }
  
      }
  
      public function get_online_supplier_reports_datatable(){
          $this-&gt;_get_online_supplier_reports_datatable();
          if($_POST['length'] != -1)
          $this-&gt;db-&gt;limit($_POST['length'], $_POST['start']);
          $query = $this-&gt;db-&gt;get();
           return $query-&gt;result();
      }
      public function count_filtered_online_supplier_reports_rec(){
          $this-&gt;_get_online_supplier_reports_datatable();
          $query = $this-&gt;db-&gt;get();
          return $query-&gt;num_rows();
      } 
  </code></pre>
  
  <p>The main probelm is when am using union there will  be getting data but i dont want to get data their only. Just i want to
  union both 2 querys . My required Out put function _get_online_supplier_reports_datatable()   is like this code.</p>
  
  <pre><code>public function _get_online_supplier_reports_datatable(){
  
          //Query 1 here
          $query1= $this-&gt;db-&gt;select('"Amadeus" AS SupplierName,TST_Type,TKT_Type,TKTNumber,TKT_FO_REF,BasePrice,TaxPrice,Commission1,(BasePrice+TaxPrice) AS TotalPrice,"issued" AS tkt_type,createdby,TicketedDate as created_date,CONCAT(a3m.firstname," ",a3m.lastname) AS created_name,a3m.account_id,a3m.locationvalue AS Location_ID,a3m.locationdescription AS Location_Name,a3m.costcentervalue AS CostCenter_ID,a3m.costcenterdescription AS CostCenter_Name');
          $this-&gt;db-&gt;from($this-&gt;traveldb-&gt;database.".booking_price bp");
          $this-&gt;db-&gt;join('a3m_account_details a3m','a3m.account_id=createdby','left');
          $this-&gt;db-&gt;where('TKTNumber !=', '');
  
          //$query1 = $this-&gt;db-&gt;get_compiled_select(); 
  
          //QUery2 Here
          $query2=  $this-&gt;db-&gt;select('"Amadeus" AS SupplierName,"" AS TST_Type,"" AS  TKT_Type,br_tkt AS TKTNumber,"" as TKT_FO_REF,br_fare_paid_B as BasePrice,br_tax_refund_TXT as TaxPrice,br_cancellation_penalty as Commission1,br_fare_total_RFT AS TotalPrice,br_cancel_type AS tkt_type,br_added_by as createdby,br_refund_date as created_date,CONCAT(a3m.firstname," ",a3m.lastname) AS created_name,a3m.account_id,a3m.locationvalue AS Location_ID,a3m.locationdescription AS Location_Name,a3m.costcentervalue AS CostCenter_ID,a3m.costcenterdescription AS CostCenter_Name');
          $this-&gt;db-&gt;from($this-&gt;traveldb-&gt;database.".booking_refund bp");
          $this-&gt;db-&gt;join('a3m_account_details a3m','a3m.account_id=br_added_by','left');
          $this-&gt;db-&gt;where('br_tkt !=', '');
  
          //$query2 = $this-&gt;db-&gt;get_compiled_select(); 
  
      //Union both querys 
       $query=  $this-&gt;db-&gt;query($query1." UNION ALL".$query2);
  
          if(isset($_POST['order'])) // here order processing
          {
              $this-&gt;db-&gt;order_by($this-&gt;column_order_online_supplier_reports_match[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
          } 
          else if(isset($this-&gt;order_online_supplier_reports_match))
          {
              $order = $this-&gt;order_online_supplier_reports_match;
              $this-&gt;db-&gt;order_by(key($order), $order[key($order)]);
          }
  
      }
  </code></pre>