CODEWITHSUNDEEP
phporacle-databaseora-00904
davemx_5
davemx_5

Reputation: 1

PHP Oracle SQL Select date to_char

I am getting; "Warning: oci_execute(): ORA-00904: "JAN": invalid identifier", when I try to execute these commands:

function stime($conn3, $time){

    $result = oci_parse($conn3, "SELECT TO_CHAR($time, 'mm/dd/yyyy') FROM MON_EVENTS")or die(oci_error());
    oci_execute($result);
}

STIME is also a date field in the database.

I am passing the STIME field to $time as stime($row_oci['STIME']).

Upvotes: 0

Views: 2682

Answers (2)

davemx_5
davemx_5

Reputation: 1

$id = $row_oci['ID'];
$result = oci_parse($conn2, "SELECT TO_CHAR(STIME,'MON/DD/YY hh24:mm:ss') FROM MON_EVENTS WHERE ID = $id");
oci_execute($result);
while($row_result = oci_fetch_array($result)){
echo "". $row_result['0'] ."";}

Upvotes: 0

Sylvain Leroux
Sylvain Leroux

Reputation: 52040

You were bitten by PHP string interpolation:

$result = oci_parse($conn3, "SELECT TO_CHAR($time, 'mm/dd/yyyy') FROM MON_EVENTS")or die(oci_error());
//                                          ^^^^^

$time is replaced by its content converted to a string -- and that before passing the value to the oci_parse function. As the string representation of a date might contain spaces, letters, /, ... it will confuse the Oracle SQL parser that report ORA-00904: Invalid identifier.

As of myself I would suggest to use bind parameter instead. This is much less error-prone -- and much more safe:

$result = oci_parse($conn3, "SELECT TO_CHAR(:time, 'mm/dd/yyyy') FROM MON_EVENTS");
oci_bind_by_name($result, ':time', $time);

Upvotes: 2

Related Questions