## Courses — PHP and JSON data

Two problems to solve here:

- Producing JSON data on server side
- Processing JSON data on client-side

    <?php

    /// use Dotenv to load database info  .env file
    // DB_HOST, DB_USER, DB_PW, DB_NAME
    require_once('vendor/autoload.php');
    $dotenv = Dotenv\Dotenv::createImmutable(__DIR__);
    $dotenv->safeLoad();

    // Connecting, selecting database
    $mysqli = new mysqli($_ENV['DB_HOST'],$_ENV['DB_USER'],$_ENV['DB_PW'],$_ENV['DB_NAME']);
    if ($mysqli->connect_errno) {
        echo "Failed to connect to mysql: ".$mysqli->connect_errno." ".$mysqli->connect_error;
    }

    $dept_code = $_GET['department_code'];
    $sqlescaped_dept_code = $mysqli->real_escape_string($dept_code);

    // Performing SQL query
    $query = <<<ENDQUERY
    SELECT
      department,
      department_code,
      course_number,
      term,
      academic_year,
      short_title,
      title,
      description,
      faculty_description

      ext_courses
    WHERE
      department_code = '$sqlescaped_dept_code'
    ORDER BY
      department,
      course_number,
      title
    ENDQUERY;

    $query_result = $mysqli->query($query);

    $i = 0;
    while ($row = $query_result->fetch_assoc()) {
      // calculate year
      $academic_year_display = $row['academic_year'];
      if ($row['term'] == 'Spring' or $row['term'] == 'January') {
        $academic_year_display += 1 ;
      }
      $row['academic_year_display'] = $academic_year_display;

      $results[$i++] = $row;
    }

    $template_data['courses'] = $results;
    $template_data['department_name'] = $dept_code;

    header('Content-type: application/json');
    echo json_encode($template_data);

    // cleanup query result and close connection
    $query_result->free();
    $mysqli->close();
    ?>