Conditional Where Filters (using DataTables PHP Libraries and SQLSRV)

stuartsjb-icr

I'm using the DataTables PHP libraries that came with Editor to connect to our MS SQL server to provide data for my tables.

Up until today I've been using SQL wildcards ('%') to filter data...

if (isset($_GET['nationalityID']) == true) {
    $nationalityID = ($_GET['nationalityID']);
} else {
    $nationalityID = '%';
}

...

->where('Applicant.NationalityID', $nationalityID, 'LIKE')

..but have run into issues where rows with NULL entries are not returned when using the wildcard. I'm not good with object-oriented programming so I'm struggling with the necessary coding syntax, but I'm trying to get my ->where filters to activate only if the variables posted to the PHP from AJAX actually have values, so I no longer need to use the wildcards. Can anyone help me with this?

stuartsjb-icr

I've tried removing the chaining from my Editor object instance in my PHP to see if I can conditionally add my SQL 'Where's to it using IF statements on my variables, but it seems to only ever return any data to the browser when using the "chained" method:

$data = Editor::inst($db, 'Applicant', 'ID')
        ->fields(
                Field::inst('Applicant.ID'), Field::inst('Applicant.Surname'), Field::inst('Applicant.Forename'), Field::inst('Nationality.Nationality'), Field::inst('StudentPath.PathName'), Field::inst('CourseParts.PartDescription'), Field::inst('ApplicantStatus.ApplicantStatus'), Field::inst('Applicant.ApplicationReceived'), Field::inst('Cohorts.Cohort'), Field::inst('CohortEntryPoint.Season')
        )

//        Filters
        ->where('Applicant.CoursePartID', $coursePartID, 'LIKE')
        ->where('Applicant.ApplicantStatusID', $applicationStatusID, 'LIKE')
        ->where('Applicant.NationalityID', $nationalityID, 'LIKE')
        ->where('Applicant.PathID', $coursePathID, 'LIKE')
        ->where('Applicant.CohortID', $cohortID, 'LIKE')
        ->where('Applicant.CohortEntryPointID', $entryPointID, 'LIKE')

//      Table Joins
        ->leftJoin('CourseParts', 'CourseParts.PartID', '=', 'Applicant.CoursePartID')
        ->leftJoin('ApplicantStatus', 'ApplicantStatus.ID', '=', 'Applicant.ApplicantStatusID')
        ->leftJoin('StudentPath', 'StudentPath.PathID', '=', 'Applicant.PathID')
        ->leftJoin('Nationality', 'Nationality.NationalityID', '=', 'Applicant.NationalityID')
        ->leftJoin('Cohorts', 'Cohorts.CohortID', '=', 'Applicant.CohortID')
        ->leftJoin('CohortEntryPoint', 'CohortEntryPoint.id', '=', 'Applicant.CohortEntryPointID')
        
        ->process($_POST)
        ->data();

echo json_encode($data);

If I change this code so that each method begins with $data and ends with a semicolon then the browser just sits there forever waiting for data, so I'm guessing I can't go about it this way either...

allan

I think breaking the chain is the correct way to do this.

$editor = Editor::inst($db, 'Applicant', 'ID')
        ->fields(
           ...
        )
        ->where('Applicant.CoursePartID', $coursePartID, 'LIKE') // etc...
        ->leftJoin('CourseParts', 'CourseParts.PartID', '=', 'Applicant.CoursePartID'); // etc

if ( isset($_GET['nationalityID']) ) {
  ... conditionally add if present, and then check for true or a string value
}

$data = $editor
        ->process($_POST)
        ->data();
 
echo json_encode($data);

Allan

edit - Missed out the $data =.

stuartsjb-icr

Thanks Allan. I think I need to work on my OOP skills..!