CODEWITHSUNDEEP
phporacle-database
Eric
Eric

Reputation: 27

Oracle inner join headache oci_execute(): ORA-00920: invalid relational operator

I'm trying to accomplish an inner join (Oracle calls them equijoins) with two tables and I'm just missing something because it's different than MySQL inner joins. I'm also new to PHP (coming from Lasso) so I'm trying to be strict with my syntax for my own sanity. I've looked at all the other posts about this error but they haven't helped.

Connecting to an Oracle 12c database with PHP 5.5.29, OCI8 is installed and verified in php info page.

Oracle's explanation of inner joins

$login is plaintext and $pw is hashed before this snippet:

// separate the login types by the "_" character
if (strpos($login, "_") == true) {
    $fieldname = 'web_password_hash';
    $dynfield = 'web_id';
    $dynfield1 = 's.web_id';
} else {
    $fieldname = 'student_web_password_hash';
    $dynfield = 'student_web_id';
    $dynfield1 = 's.student_web_id';
}

$schoolid = '(pcs.schoolid=4 OR pcs.schoolid=5)';

// search custom table for a field with the correct password (parent/student)
$dbc = oci_connect($psusername, $pspassword, $psconnection);

if(!$dbc) {
    echo "Oracle Not connected";
} else {

$stmt = oci_parse($dbc, 'SELECT pcs.student_number AS snumber, :dynfield1 AS loginid, s.ID AS sid, s.family_ident AS famid, s.firstname AS fname, s.last_name AS lname, s.grade_level AS gradelevel 
    FROM pvsis_custom_students pcs, students s 
        WHERE pcs.student_number=s.student_number 
            AND :schoolid 
            AND pcs.field_name=:fieldname 
            AND pcs.string_value=:pw 
            AND s.enroll_status=0;');

// bind parameters to prevent SQL injection         
oci_bind_by_name($stmt, ':dynfield1', $dynfield1);          
oci_bind_by_name($stmt, ':fieldname', $fieldname);
oci_bind_by_name($stmt, ':schoolid', $schoolid);
oci_bind_by_name($stmt, ':pw', $pw);

// execute statement
oci_execute($stmt);

The error I then get is: Warning: oci_execute(): ORA-00920: invalid relational operator in /path/to/file line XXX

Upvotes: 1

Views: 597

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50077

You can't bind the name of the fields the way you're doing it here. You'll need to build the query dynamically:

$stmt = oci_parse($dbc, 'SELECT pcs.student_number AS snumber, ' . $dynfield1 . ' AS loginid, s.ID AS sid, s.family_ident AS famid, s.firstname AS fname, s.last_name AS lname, s.grade_level AS gradelevel 
    FROM pvsis_custom_students pcs, students s 
        WHERE pcs.student_number=s.student_number 
            AND ' . $schoolid . '
            AND pcs.field_name=' . $fieldname . '
            AND pcs.string_value=:pw 
            AND s.enroll_status=0;');

Remove the bind_by_name calls for :dynfield1, :fieldname, and :schoolid.

Best of luck.

Upvotes: 1

Related Questions