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.
Model.php (Working this function)Using single query in _get_online_supplier_reports_datatable() function
<?php
public function _get_online_supplier_reports_datatable(){
$this->db->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->db->from($this->traveldb->database.".booking_price bp");
$this->db->join('a3m_account_details a3m','a3m.account_id=createdby','left');
$this->db->where('TKTNumber !=', '');
if(isset($_POST['order'])) // here order processing
{
$this->db->order_by($this->column_order_online_supplier_reports_match[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
}
else if(isset($this->order_online_supplier_reports_match))
{
$order = $this->order_online_supplier_reports_match;
$this->db->order_by(key($order), $order[key($order)]);
}
}
public function get_online_supplier_reports_datatable(){
$this->_get_online_supplier_reports_datatable();
if($_POST['length'] != -1)
$this->db->limit($_POST['length'], $_POST['start']);
$query = $this->db->get();
return $query->result();
}
public function count_filtered_online_supplier_reports_rec(){
$this->_get_online_supplier_reports_datatable();
$query = $this->db->get();
return $query->num_rows();
}
?>
I have added One more query in _get_online_supplier_reports_datatable() function for Union both querys .after that it is not working
<?php
public function _get_online_supplier_reports_datatable(){
//Query 1 here
$this->db->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->db->from($this->traveldb->database.".booking_price bp");
$this->db->join('a3m_account_details a3m','a3m.account_id=createdby','left');
$this->db->where('TKTNumber !=', '');
$query1 = $this->db->get_compiled_select();
//QUery2 Here
$this->db->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->db->from($this->traveldb->database.".booking_refund bp");
$this->db->join('a3m_account_details a3m','a3m.account_id=br_added_by','left');
$this->db->where('br_tkt !=', '');
$query2 = $this->db->get_compiled_select();
//Union both querys
$query= $this->db->query($query1." UNION ALL".$query2);
if(isset($_POST['order'])) // here order processing
{
$this->db->order_by($this->column_order_online_supplier_reports_match[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
}
else if(isset($this->order_online_supplier_reports_match))
{
$order = $this->order_online_supplier_reports_match;
$this->db->order_by(key($order), $order[key($order)]);
}
}
public function get_online_supplier_reports_datatable(){
$this->_get_online_supplier_reports_datatable();
if($_POST['length'] != -1)
$this->db->limit($_POST['length'], $_POST['start']);
$query = $this->db->get();
return $query->result();
}
public function count_filtered_online_supplier_reports_rec(){
$this->_get_online_supplier_reports_datatable();
$query = $this->db->get();
return $query->num_rows();
}
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.
public function _get_online_supplier_reports_datatable(){
//Query 1 here
$query1= $this->db->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->db->from($this->traveldb->database.".booking_price bp");
$this->db->join('a3m_account_details a3m','a3m.account_id=createdby','left');
$this->db->where('TKTNumber !=', '');
//$query1 = $this->db->get_compiled_select();
//QUery2 Here
$query2= $this->db->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->db->from($this->traveldb->database.".booking_refund bp");
$this->db->join('a3m_account_details a3m','a3m.account_id=br_added_by','left');
$this->db->where('br_tkt !=', '');
//$query2 = $this->db->get_compiled_select();
//Union both querys
$query= $this->db->query($query1." UNION ALL".$query2);
if(isset($_POST['order'])) // here order processing
{
$this->db->order_by($this->column_order_online_supplier_reports_match[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
}
else if(isset($this->order_online_supplier_reports_match))
{
$order = $this->order_online_supplier_reports_match;
$this->db->order_by(key($order), $order[key($order)]);
}
}
