I’m trying to implement a find by criteria method with OCI8 in PHP, where if the object has a parameter set, it should query all records, where the given parameters are like the ones in the record. But for some reason it wont return anything.

$id = $model->getId();
$schedule = $model->getSchedule();
$term = $model->getTerm();
$subject = $model->getSubject();
$room = $model->getRoom();

// create the criterias for each parameters
if (isset($subject) && $subject->getId() !== null) {
    $crits[] = 'CRS.' . TableDefinition::COURSE_TABLE_FIELD_SUBJECT_ID . " LIKE :subjectId";
    $subjectId = $subject->getId();
}
if (isset($id)) $crits[] = 'CRS.' . TableDefinition::COURSE_TABLE_FIELD_ID . " = :id";
if (isset($schedule)) $crits[] = TableDefinition::COURSE_TABLE_FIELD_SCHEDULE . " LIKE :schedule";
if (isset($term)) $crits[] = TableDefinition::COURSE_TABLE_FIELD_TERM . " LIKE :term";
if (isset($room) && $room->getId() !== null) {
    $crits[] = 'ROOM.' . TableDefinition::COURSE_TABLE_FIELD_ROOM_ID . " LIKE :roomId";
    $roomId = $room->getId();
}

if (!empty($crits))
    //extend the sql with the criterias
    $sql .= " AND " . implode(" AND ", $crits);

    if (!$stmt = oci_parse($this->dataSource->getConnection(), $sql))
       throw new DataAccessException('parse ' . json_encode(oci_error($stmt)));
 
    // bind the required parameters
    if (isset($subjectId) && !oci_bind_by_name($stmt, ':subjectId', $subjectId, -1))
       throw new DataAccessException('bind subjectId ' . json_encode(oci_error($stmt)));
    if (isset($id) && !oci_bind_by_name($stmt, ':id', $id, -1))
       throw new DataAccessException('bind id ' . json_encode(oci_error($stmt)));
    if (isset($schedule) && !oci_bind_by_name($stmt, ':schedule', $schedule, -1))
       throw new DataAccessException('bind schedule ' . json_encode(oci_error($stmt)));
    if (isset($term) && !oci_bind_by_name($stmt, ':term', $term, -1))
       throw new DataAccessException('bind term ' . json_encode(oci_error($stmt)));
    if (isset($roomId) && !oci_bind_by_name($stmt, ':roomId', $roomId, -1))
       throw new DataAccessException('bind roomId ' . json_encode(oci_error($stmt)));

    if (!oci_execute($stmt, OCI_DEFAULT))
       throw new DataAccessException('exec ' . json_encode(oci_error($stmt)));


    // handle result...
}

The sql must be correct, I copied the one assembled by the code, I did the binding manually, and got the correct results, so I think the problem should be in the binding.

I checked if the error handling is not correct, and the bindig does not actually happen, by removing them, but that cannot be the case, since then the oci_execute will throw an error.

Since checking the binded sql is not possible, I’m stuck.

Thanks for your help!