(08-09-2017, 10:49 AM)Kaosweaver Wrote: Code:
$this->db->select("select pu.last_name,
Remove the select, this should just be column names.
Code:
$this->db->select("pu.last_name,
I figured out what is going on, but I'm not sure how to fix it.
The last_query() method is, in fact, returning what it thinks is the query, and I see where it's crashing. First let me show you part of my Status_model.
________________________________________________________________
<?php
class Status_model extends CI_Model {
var $iTable = 'PASSPORT.PASSPORT_INFORMATION_DETAIL';
var $uTable = 'PASSPORT.PASSPORT_USERS';
var $sTable = 'PASSPORT.PASSPORT_STATUS';
var $sql;
// order of columns
var $column_order = array(
'LAST_NAME',
'FIRST_NAME',
'MIDDLE_NAME',
'PASSPORT_NUMBER'
);
// set the searchable columns
var $column_search = array(
'LAST_NAME',
'FIRST_NAME',
'MIDDLE_NAME',
'PASSPORT_NUMBER'
);
// default sort column
var $order = array(
'LAST_NAME' => 'asc'
);
private function _get_datatables_query() {
// individual column searching
$column_count = 0;
foreach ($this->column_search as $item) {
if ($_POST['columns'][$column_count]['search']['value']) {
$this->db->like('LOWER(' . $item . ')', strtolower($_POST['columns'][$column_count]['search']['value'])); // fix to make case-insensitive
}
$column_count++;
}
$this->db->select("pu.username,
pu.last_name,
pu.first_name,
pu.middle_name,
ppi.passport_number,
ps.passport_status_desc,
pmax_status_date
from
passport_users pu,
(select pi.username,
pi.passport_number,
max(pid.passport_status_date) pmax_status_date
from passport_information pi,
passport_information_detail pid
where pi.username = pid.username
group by pi.username,
pi.passport_number) p_max_status,
passport_information ppi,
passport_information_detail ppid,
passport_status ps
where pu.username = p_max_status.username
and pu.username = ppi.username
and ppi.passport_number = p_max_status.passport_number
and ppi.passport_number = ppid.passport_number
and ppid.passport_status_date = p_max_status.pmax_status_date
and ppid.passport_status = ps.passport_status
and sysdate between ppi.passport_issue_date and NVL(ppi.passport_expiration_date, sysdate)", FALSE);
$i = 0;
foreach ($this->column_search as $item) {
if ($_POST['search']['value']) {
if ($i === 0) {
$this->db->group_start();
$this->db->like('LOWER(' . $item . ')', strtolower($_POST['search']['value'])); // fix to make case-insensitive
} else {
$this->db->or_like('LOWER(' . $item . ')', strtolower($_POST['search']['value'])); // fix to make case-insensitive
}
if (count($this->column_search) - 1 == $i)
$this->db->group_end();
}
$i++;
}
if (isset($_POST['order'])) {
$this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
} else if (isset($this->order)) {
$order = $this->order;
$this->db->order_by(key($order), $order[key($order)]);
}
}
function get_datatables() {
$this->_get_datatables_query();
if ($_POST['length'] != -1)
$this->db->limit($_POST['length'], $_POST['start']);
$query = $this->db->get();
//echo $this->db->last_query();
return $query->result();
}
function count_filtered() {
$this->_get_datatables_query();
$query = $this->db->get();
//echo $this->db->last_query();
return $query->num_rows();
}
public function count_all() {
$this->db->from($this->uTable);
return $this->db->count_all_results();
}
________________________________________________________________
Notice the $this->db->select piece in the _get_datatables_query() function (in blue). It works as expected and the DataTable gets loaded correctly; however, when you try to do a footer or page search, it crashes. If I do a last_query() and look at it from the search, it looks like this:
SELECT * FROM (SELECT inner_query.*, rownum rnum FROM (SELECT pu.last_name, pu.first_name, pu.middle_name, ppi.passport_number from passport_users pu, (select pi.username, pi.passport_number, max(pid.passport_status_date) pmax_status_date
from passport_information pi, passport_information_detail pid where pi.username = pid.username group by pi.username, pi.passport_number) p_max_status, passport_information ppi, passport_information_detail ppid, passport_status ps
where pu.username = p_max_status.username
and pu.username = ppi.username
and ppi.passport_number = p_max_status.passport_number
and ppi.passport_number = ppid.passport_number
and ppid.passport_status_date = p_max_status.pmax_status_date
and ppid.passport_status = ps.passport_status
and sysdate between ppi.passport_issue_date and NVL(ppi.passport_expiration_date, sysdate)
WHERE LOWER(LAST_NAME) LIKE '%o%' ESCAPE '!'
ORDER BY "LAST_NAME" ASC
) inner_query WHERE rownum < 11)
Now, look at the two WHERE clauses in bold (one lower case and one upper case). This can't work! That's broken sql query syntax for Oracle! If I take that query and run it in Toad, it breaks...like it should. But if I change the line from this:
WHERE LOWER(LAST_NAME) LIKE '%o%' ESCAPE '!'
to this:
AND LOWER(LAST_NAME) LIKE '%o%' ESCAPE '!'
It works!
It appears what's causing that is the code above in red. The problem is that I don't know how to get around it or fix it.
Does all this make sense? I hope I've explained it well enough.
Thanks...
