fetchReport.php

<?php
  function first_semester_report($student_id) {
		  global $connection;
		  $query = "SELECT period_one.score, period_one.subject_id, period_two.score, period_two.subject_id, period_three.score, period_three.subject_id
			from students

			LEFT JOIN period_one
            ON students.student_id = period_one.student_id
            AND students.current_class_id = period_one.class_id
            
			LEFT JOIN period_two
			ON students.student_id = period_two.student_id
            AND students.current_class_id = period_two.class_id

			LEFT JOIN period_three 
			ON students.student_id = period_three.student_id
            AND students.current_class_id = period_three.class_id

      WHERE students.student_id = {$student_id} OR period_one.student_id = {$student_id} OR period_two.student_id = {$student_id} OR period_three.student_id = {$student_id}";      

		$result_set = mysqli_query($connection, $query);

		if(!$result_set){
			die("Database query failed. Error from first semester report: ".mysqli_error($connection)." Error number is: ".mysqli_errno($connection));
		}
              

		// Test if there was a query/syntax error
		//confirm_query($result_set);
		return $result_set;
	}

 
  function find_subject_by_id($subject_id){
		global $connection;

		$safe_subject_id = mysqli_real_escape_string($connection, $subject_id);
		$query = "SELECT * from subjects WHERE subject_id = {$safe_subject_id} LIMIT 1";

		$subject_set = mysqli_query($connection, $query);
		confirm_query($subject_set);

		if ($subject = mysqli_fetch_assoc($subject_set)){
			return $subject;
		} else {
			return null;
		}
	}


    
  $result = first_semester_report($student_id);

	if (mysqli_num_rows($result) > 0) {
		# code...
		$output .= '<h4 align="center">Semester Report</h4>';
		$output .= '<div class="table-responsive">

						<table class="table table-bordered">
								<tr>
									<th scope="row">Name</th>
									<td colspan="3">'.
										$student["first_name"]." ".$student["middle_name"]." ".$student["surname"]	
									.'</td>
								</tr>
								<tr>
									<th scope="row">Class</th>
									<td>'.$class['class_name'].'</td>
								</tr>
								<tr>
									<th class="text-center">Subject</th>
									<th class="text-center">Period One</th>
									<th class="text-center">Period Two</th>
									<th class="text-center">Period Three</th>
									<th class="text-center">First Exam</th>
								</tr>';
		while ($row = mysqli_fetch_array($result)) {
			# code...
      
			// finding the subject name
      // Here I passed in the subject return by the query id and then get the name for the subject
			$subject = find_subject_by_id($row["subject_id"]);
      // now that I've passed in subject id it returns an error because it is not found in the students table
      //

			$output .= '<tr>';
					$output .= '<th scope="row">'.$subject["subject_name"] .'</th>';
							if($row[2] <= 69)
								$output .= '<td style="color:red;">'.$row[2].'</td>';
							else
								$output .= '<td>'.$row[2].'</td>';
							if($row[3] <= 69)
								$output .= '<td style="color:red;">'.$row[3].'</td>';
							else
								$output .= '<td>'.$row[3].'</td>';
							if($row[4] <= 69)
								$output .= '<td style="color:red;">'.$row[4].'</td>';
							else
								$output .= '<td>'.$row[4].'</td>';
							if($row[5] <= 69)
								$output .= '<td style="color:red;">'.$row[5].'</td>';
							else
								$output .= '<td>'.$row[5].'</td>';
			$output .= '</tr>';
		}
		$output .= '<tr>';
			$output .= '<th scope="row">Periodic Avg</th>';
						// displaying first period average
						while ($average = mysqli_fetch_array($FirstPeriodAvg)){
							$output .= '<td style="text-align:right; font-weight:bold;">'. round($average[0], 2) .'</td>';
						}
						// displaying second period average
						while ($average = mysqli_fetch_array($SecondPeriodAvg)){
							$output .= '<td style="text-align:right; font-weight:bold;">'. round($average[0], 2) .'</td>';
						}
						// displaying third period average
						while ($average = mysqli_fetch_array($ThirdPeriodAvg)){
							$output .= '<td style="text-align:right; font-weight:bold;">'. round($average[0], 2) .'</td>';
						}
		$output .= '</tr>';
		echo $output;
	} else {
		echo "Data not found";
	}
?>