-
jace
Newbie
-
Posts: 9
Threads: 1
Joined: Jul 2017
Reputation:
0
I’m getting this error when trying an Oracle query inside a search function:
Code: <div style="border:1px solid #990000;padding-left:20px;margin:0 0 10px 0;">
<h4>A PHP Error was encountered</h4>
<p>Severity: Warning</p>
<p>Message: oci_execute(): ORA-00936: missing expression</p>
<p>Filename: oci8/oci8_driver.php</p>
<p>Line Number: 286</p>
<p>Backtrace:</p>
<p style="margin-left:10px">
File: C:\xampp\htdocs\passport\application\models\Status_model.php<br />
Line: 143<br />
Function: get </p>
<p style="margin-left:10px">
File: C:\xampp\htdocs\passport\application\controllers\Status.php<br />
Line: 29<br />
Function: get_datatables </p>
<p style="margin-left:10px">
File: C:\xampp\htdocs\passport\index.php<br />
Line: 304<br />
Function: require_once </p>
</div><br />
<b>Fatal error</b>: Call to a member function result() on boolean in <b>C:\xampp\htdocs\passport\application\models\Status_model.php</b> on line <b>144</b><br />
<div style="border:1px solid #990000;padding-left:20px;margin:0 0 10px 0;">
<h4>A PHP Error was encountered</h4>
<p>Severity: Error</p>
<p>Message: Call to a member function result() on boolean</p>
<p>Filename: models/Status_model.php</p>
<p>Line Number: 144</p>
<p>Backtrace:</p>
</div>
PHP Code: $this->db->select("select pu.username,
pu.last_name,
pu.first_name,
pu.middle_name,
ppi.passport_number,
ps.passport_status_desc,
passport.rowid as unique_id", FALSE);
$this->db->from("passport_users, (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");
$this->db->where("pu.username = p_max_status.username");
$this->db->where("pu.username = ppi.username");
$this->db->where("ppi.passport_number = p_max_status.passport_number");
$this->db->where("ppi.passport_number = ppid.passport_number");
$this->db->where("ppid.passport_status_date = p_max_status.pmax_status_date");
$this->db->where("ppid.passport_status = ps.passport_status");
Thanks ahead of time. Any help or direction is much appreciate.
jACE
-
Kaosweaver
Member
-
Posts: 133
Threads: 19
Joined: Jan 2015
Reputation:
14
I'm not certain how Oracle does alias names, however, I do not see anything for the alias "pu" in the list of tables in the FROM declaration. I would assume that it is for the passport_users?
Maybe:
PHP Code: $this->db->from("passport_users pu, (select pi.username,
-
jace
Newbie
-
Posts: 9
Threads: 1
Joined: Jul 2017
Reputation:
0
Thanks for the reply. You are absolutely correct; however, after adding in the alias I still get the error. I get this whether I use a query string or Active Record. I believe the problem is in me using get() for the search functionality.
Ultimately, I want to be able to use the query string and still embed the query inside the _get_datatables_query() function. Active record would be fine, I just need to be able to utilize the query inside the search functionality.
PHP Code: <?php
class Status_model extends CI_Model {
var $iTable = 'PASSPORT.PASSPORT_INFORMATION_DETAIL';
var $uTable = 'PASSPORT.PASSPORT_USERS';
var $sTable = 'PASSPORT.PASSPORT_STATUS';
// 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++;
}
// $sql = "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)";
// $query = $this->db->query($sql);
$this->db->select("select pu.username,
pu.last_name,
pu.first_name,
pu.middle_name,
ppi.passport_number,
ps.passport_status_desc,
PASSPORT_USERS.ROWID as UNIQUE_ID", FALSE);
$this->db->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");
$this->db->where("pu.username = p_max_status.username");
$this->db->where("pu.username = ppi.username");
$this->db->where("ppi.passport_number = p_max_status.passport_number");
$this->db->where("ppi.passport_number = ppid.passport_number");
$this->db->where("ppid.passport_status_date = p_max_status.pmax_status_date");
$this->db->where("ppid.passport_status = ps.passport_status");
$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();
return $query->result();
}
Code: <div style="border:1px solid #990000;padding-left:20px;margin:0 0 10px 0;">
<h4>A PHP Error was encountered</h4>
<p>Severity: Warning</p>
<p>Message: oci_execute(): ORA-00936: missing expression</p>
<p>Filename: oci8/oci8_driver.php</p>
<p>Line Number: 286</p>
<p>Backtrace:</p>
<p style="margin-left:10px">
File: C:\xampp\htdocs\passport\application\models\Status_model.php<br />
Line: 123<br />
Function: get </p>
<p style="margin-left:10px">
File: C:\xampp\htdocs\passport\application\controllers\Status.php<br />
Line: 29<br />
Function: get_datatables </p>
<p style="margin-left:10px">
File: C:\xampp\htdocs\passport\index.php<br />
Line: 304<br />
Function: require_once </p>
</div><br />
<b>Fatal error</b>: Call to a member function result() on boolean in <b>C:\xampp\htdocs\passport\application\models\Status_model.php</b> on line <b>124</b><br />
<div style="border:1px solid #990000;padding-left:20px;margin:0 0 10px 0;">
<h4>A PHP Error was encountered</h4>
<p>Severity: Error</p>
<p>Message: Call to a member function result() on boolean</p>
<p>Filename: models/Status_model.php</p>
<p>Line Number: 124</p>
<p>Backtrace:</p>
</div>
-
jace
Newbie
-
Posts: 9
Threads: 1
Joined: Jul 2017
Reputation:
0
(08-04-2017, 05:07 AM)Kaosweaver Wrote: I'm not certain how Oracle does alias names, however, I do not see anything for the alias "pu" in the list of tables in the FROM declaration. I would assume that it is for the passport_users?
Maybe:
PHP Code: $this->db->from("passport_users pu, (select pi.username,
Not sure why this forum posts my replies sometimes, and other times it flashes something about moderation and then I never see the reply to the thread. Let's try again.
You are right. I was missing the alias; however, after adding it I still get the error:
Code: <div style="border:1px solid #990000;padding-left:20px;margin:0 0 10px 0;">
<h4>A PHP Error was encountered</h4>
<p>Severity: Warning</p>
<p>Message: oci_execute(): ORA-00936: missing expression</p>
<p>Filename: oci8/oci8_driver.php</p>
<p>Line Number: 286</p>
<p>Backtrace:</p>
<p style="margin-left:10px">
File: C:\xampp\htdocs\passport\application\models\Status_model.php<br />
Line: 123<br />
Function: get </p>
<p style="margin-left:10px">
File: C:\xampp\htdocs\passport\application\controllers\Status.php<br />
Line: 29<br />
Function: get_datatables </p>
<p style="margin-left:10px">
File: C:\xampp\htdocs\passport\index.php<br />
Line: 304<br />
Function: require_once </p>
</div><br />
<b>Fatal error</b>: Call to a member function result() on boolean in <b>C:\xampp\htdocs\passport\application\models\Status_model.php</b> on line <b>124</b><br />
<div style="border:1px solid #990000;padding-left:20px;margin:0 0 10px 0;">
<h4>A PHP Error was encountered</h4>
<p>Severity: Error</p>
<p>Message: Call to a member function result() on boolean</p>
<p>Filename: models/Status_model.php</p>
<p>Line Number: 124</p>
<p>Backtrace:</p>
</div>
I believe it has to do with me using the $query = $this->db->get() of the search functionality in the get_datatables() function (see below); however, I can't see how to mix the two. In other words, I want to be able to call the query, but inside the _get_datatables_query() method for search functionality.
Thanks for your input...
PHP Code: var $iTable = 'PASSPORT.PASSPORT_INFORMATION_DETAIL';
var $uTable = 'PASSPORT.PASSPORT_USERS';
var $sTable = 'PASSPORT.PASSPORT_STATUS';
// 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++;
}
// $sql = "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)";
// $query = $this->db->query($sql);
$this->db->select("select pu.username,
pu.last_name,
pu.first_name,
pu.middle_name,
ppi.passport_number,
ps.passport_status_desc,
PASSPORT_USERS.ROWID as UNIQUE_ID", FALSE);
$this->db->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");
$this->db->where("pu.username = p_max_status.username");
$this->db->where("pu.username = ppi.username");
$this->db->where("ppi.passport_number = p_max_status.passport_number");
$this->db->where("ppi.passport_number = ppid.passport_number");
$this->db->where("ppid.passport_status_date = p_max_status.pmax_status_date");
$this->db->where("ppid.passport_status = ps.passport_status");
$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();
return $query->result();
}
-
Kaosweaver
Member
-
Posts: 133
Threads: 19
Joined: Jan 2015
Reputation:
14
The error you're getting (ORA-00936c) is referenced here:
http://www.dba-oracle.com/sf_ora_00936_m...ession.htm
to show that something is wrong with your SQL. While I'm looking at it, I'm seeing things I'm unsure of how they would operate for Oracle, since I'm a mySQL database guy, I see scope issues with the alias naming and I see a column aliased (passport_status ps) and then used as a prefix to another column (ps.passport_status_desc) - so, not sure about how that would work out.
But, I do know the GET is working because you're getting a valid error that shows that the SQL tried, but the Orcale engine threw an error that basically says (as far as I can tell), you're SQL needs work.
I would suggest you write out the SQL in the full text version and then use your database management tool and run that SQL to make sure it is working before you try to import it to the CI model code (this way we can eliminate CI from the problem and figure out if it is something with the SQL or something with CI.
-
jace
Newbie
-
Posts: 9
Threads: 1
Joined: Jul 2017
Reputation:
0
(08-07-2017, 05:52 AM)Kaosweaver Wrote: The error you're getting (ORA-00936c) is referenced here:
http://www.dba-oracle.com/sf_ora_00936_m...ession.htm
to show that something is wrong with your SQL. While I'm looking at it, I'm seeing things I'm unsure of how they would operate for Oracle, since I'm a mySQL database guy, I see scope issues with the alias naming and I see a column aliased (passport_status ps) and then used as a prefix to another column (ps.passport_status_desc) - so, not sure about how that would work out.
But, I do know the GET is working because you're getting a valid error that shows that the SQL tried, but the Orcale engine threw an error that basically says (as far as I can tell), you're SQL needs work.
I would suggest you write out the SQL in the full text version and then use your database management tool and run that SQL to make sure it is working before you try to import it to the CI model code (this way we can eliminate CI from the problem and figure out if it is something with the SQL or something with CI.
When I plug this Oracle query in Toad, it works perfectly.
Code: 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);
-
pporlan
Newbie
-
Posts: 3
Threads: 0
Joined: Feb 2015
Reputation:
0
Maybe CI is escaping something it doesn't need to, like this:
PHP Code: $this->db->from("passport_users, (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");
Try showing what CI really sends to Oracle using this:
PHP Code: echo $this->db->last_query();
-
jace
Newbie
-
Posts: 9
Threads: 1
Joined: Jul 2017
Reputation:
0
(08-08-2017, 03:40 AM)pporlan Wrote: Maybe CI is escaping something it doesn't need to, like this:
PHP Code: $this->db->from("passport_users, (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");
Try showing what CI really sends to Oracle using this:
PHP Code: echo $this->db->last_query();
I used last_query(), and don't understand how CI assumes this:
Code: SELECT * FROM (SELECT inner_query.*, rownum rnum FROM (SELECT "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"
ORDER BY "LAST_NAME" ASC
) inner_query WHERE rownum < 11)
Code: $this->db->select("select pu.last_name,
pu.first_name,
pu.middle_name,
ppi.passport_number", TRUE);
$this->db->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");
$this->db->where("pu.username = p_max_status.username");
$this->db->where("pu.username = ppi.username");
$this->db->where("ppi.passport_number = p_max_status.passport_number");
$this->db->where("ppi.passport_number = ppid.passport_number");
$this->db->where("ppid.passport_status_date = p_max_status.pmax_status_date");
$this->db->where("ppid.passport_status = ps.passport_status");
-
Kaosweaver
Member
-
Posts: 133
Threads: 19
Joined: Jan 2015
Reputation:
14
Code: $this->db->select("select pu.last_name,
Remove the select, this should just be column names.
Code: $this->db->select("pu.last_name,
|
