<?php
// Parameters for connection
  $servername = "localhost"; //server
  $username = "root"; //username
  $password = "root"; //password
  $dbname = "employees"; //name of the table
  $response = array(); //array with data about 5 employess

// Creating connection
  $conn = new mysqli($servername, $username, $password, $dbname);
// Checking connection
  if ($conn->connect_error) {
    echo "Connection failed: " . $conn->connect_error;
  }

// SQL request to get ID of 5 top paid employees
  $sql = "SELECT `emp_no`,`salary`, `to_date` FROM `salaries` ORDER BY `to_date` DESC, `salary` DESC LIMIT 5";
  $result = $conn->query($sql);

  $counter = 0;
  //Checking if request was succesfull
  if ($result->num_rows > 0) {

      //creating JSON file and opening writing link
      $fp = fopen('employees.json','w');

      //dividing retrieved data into rows and going through trem
      while($row = $result->fetch_assoc()) {
          //the array that will hold data about 1 employee
          $posts = array();

          //Requesting first and last names of employee
          $sql = "SELECT `first_name`, `last_name` FROM `employees` WHERE `emp_no` = {$row['emp_no']} LIMIT 1";
          $request = $conn->query($sql);
                //dividing data from DB and storing it
                $row1 = mysqli_fetch_row($request);
                $fname = $row1[0];
                $lname = $row1[1];

          //Requesting tittle of the employee
          $sql = "SELECT `title` FROM `titles` WHERE `emp_no` = {$row['emp_no']} ORDER BY `to_date` LIMIT 1";
          $request = $conn->query($sql);
                //dividing data from DB and storing it
                $row1 = mysqli_fetch_row($request);
                $title = $row1[0];

          //Requesting department name where employee works
          $sql = "SELECT `dept_name` FROM `departments`,`dept_emp` WHERE `dept_emp`.`emp_no` = {$row['emp_no']} AND `dept_emp`.`dept_no` = `departments`.`dept_no` ORDER BY `to_date` DESC LIMIT 1";
          $request = $conn->query($sql);
                //dividing data from DB and storing it
                $row1 = mysqli_fetch_row($request);
                $depart = $row1[0];


          //each piece of information from the requests goes from variables into the posts array
          $posts[] = array('Title'=> $title, 'First name'=> $fname, 'Last name'=> $lname, 'Title'=> $title, 'Salary'=> $row['salary'], 'Department'=> $depart);


      //the posts array goes into the response
      $counter++;
      $response["Employee {$counter}"] = $posts;
   }
  } else {
      echo "Request error" . mysql_error();
  }

//writing response array in to the JSON file
fwrite($fp, json_encode($response));
//closing writing link
fclose($fp);

// Closing connection with DB
$conn->close();

?>