Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #!/usr/bin/env php
- <?php
- //Variables
- $servername = "localhost";
- $username = "root";
- $password = "test123";
- $dbname = "employees";
- $resultArr = array();
- // Create connection
- $conn = new mysqli($servername, $username, $password, $dbname);
- // Check connection
- if ($conn->connect_error) {
- die("Connection failed: " . $conn->connect_error);
- }
- //Request to get the top 5 paid employees by their emp_no sorting only the latest date (9999)
- $sqlSal = "SELECT emp_no, salary FROM salaries WHERE INSTR(to_date, 9999) ORDER BY salary DESC LIMIT 5";
- $salResult = mysqli_query($conn, $sqlSal);
- if ($salResult->num_rows > 0) {
- // output data of each row
- $i = 0;
- while($row = $salResult->fetch_assoc()) {
- //echo "emp_no: " . $row["emp_no"]. " - Salary: " . $row["salary"];
- //echo " i=" . $i . "\n";
- //Getting into the joint table to get dept_no from emp_no
- $sqlDeptEmp = "SELECT dept_no FROM dept_emp WHERE emp_no=" . $row["emp_no"];
- $deptEmpResult = mysqli_query($conn, $sqlDeptEmp);
- $deptNo = $deptEmpResult->fetch_assoc();
- //Getting deppartment name using dept_no
- $sqlDept = "SELECT dept_name FROM departments WHERE dept_no='" . $deptNo["dept_no"] . "'";
- $deptResult = mysqli_query($conn, $sqlDept);
- $dept = $deptResult->fetch_assoc();
- //Getting Name
- $sqlName = "SELECT first_name, last_name FROM employees WHERE emp_no=" . $row["emp_no"];
- $nameResult = mysqli_query($conn, $sqlName);
- $name = $nameResult->fetch_assoc();
- //Getting Title
- $sqlTitle = "SELECT title FROM titles WHERE INSTR(to_date, 9999) AND emp_no=" . $row["emp_no"];
- $titleResult = mysqli_query($conn, $sqlTitle);
- $title = $titleResult->fetch_assoc();
- //Putting all fields into final array before posting to json file
- $resultArr[$i] = array($title["title"] , $name["first_name"], $name["last_name"], $row["salary"], $deptNo["dept_no"]);
- $i = $i +1;
- }
- file_put_contents('top5.json', json_encode($resultArr));
- } else {
- echo "0 results";
- }
- ?>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement