I am facing an issue with displaying student reports on my website. I have a database where students upload their reports. Each student can upload multiple reports, but when I try to display these reports on their profiles, only one record is appearing, even if they have uploaded several reports.
I am currently fetching data from two tables: ‘student’ and ‘reports’. The ‘reports’ table has a foreign key ‘student_id’ linking it to the ‘student’ table. I’m using a MySQL query to fetch the records, but it seems to fetch only one report per student.
The image I provided, what happened to the output:
Student 1st row in table “student” student_id [2]
Student 2nd row in table “student” student_id [5]
Here’s a snippet of my code:
<?php
include("../../include/config.php");
if(isset($_GET["id"])){
$id = mysqli_real_escape_string($conn, $_GET["id"]);
$sql = "SELECT reports.id AS report_id, reports.title, reports.objective, reports.content, reports.report_date, reports.report_time,
student.student_id, student.username, student.ic_number, student.email, student.address, student.status, student.phone_number, student.programme
FROM reports
JOIN student ON reports.student_id = student.id
WHERE student.id = $id";
$result = mysqli_query($conn, $sql);
if(mysqli_num_rows($result) > 0) {
$student = mysqli_fetch_assoc($result);
} else {
die("Student not found.");
}
}
function getFormattedDate($timestamp, $timezone) {
date_default_timezone_set($timezone);
$dateObj = new DateTime($timestamp);
$currentYear = date("Y");
if ($dateObj->format("Y") == $currentYear) {
$formattedDate = $dateObj->format("d-M");
} else {
$formattedDate = $dateObj->format("d M Y");
}
return $formattedDate;
}
?>
<table class="table">
<tr>
<th>Username</th>
<td><?php echo $student["username"];?></td>
</tr>
<tr>
<th>Student ID</th>
<td><?php echo $student["student_id"];?></td>
</tr>
<tr>
<th>Email</th>
<td><?php echo $student["email"];?></td>
</tr>
<tr>
<th>No. Tel</th>
<td><?php echo empty($student["phone_number"]) ? '-': $student['phone_number'];?></td>
</tr>
<tr>
<th>IC Number</th>
<td><?php echo $student["ic_number"];?></td>
</tr>
<tr>
<th>Address</th>
<td><?php echo empty($student["address"]) ? '-': $student["address"] ;?></td>
</tr>
<tr>
<th>Programme</th>
<td><?php echo empty($student["programme"]) ? '-': $student["programme"];?></td>
</tr>
<tr>
<th>Status</th>
<td>
<?php
if ($student["status"] == 1) {
echo "<span class='badge text-bg-success'>Active</span>";
} else {
echo "<span class='badge text-bg-danger'>No Active</span>";
}
?>
</td>
</tr>
</table>
</div>
</div>
<h2 class="mt-5">Reports</h2>
<table class="table table-striped">
<thead>
<tr>
<th>#</th>
<th>Title</th>
<th>Content</th>
<th>Date Sent</th>
</tr>
</thead>
<tbody>
<?php
$counter = 1;
$j=1;
while ($row = mysqli_fetch_assoc($result)) {
echo "<tr class='clickable-row' data-href='../view-report?id=" . $row["report_id"] . "'>";
echo "<td>" . $counter . "</td>";
echo "<td>" . $row["title"] . "</td>";
echo "<td>" . $row["content"] . "</td>";
$formattedDate = getFormattedDate($row["report_date"], "UTC");
echo "<td>" . $formattedDate . "</td>";
echo "</tr>";
$counter++;
$j++;
}
?>
</tbody>
</table>
table “reports”
To address this issue, needed to modify the code to fetch all the reports associated with a specific student. Achieved this by adding an additional SQL query $reportSql inside the main if block. This query retrieved all reports related to the specific student using their student_id. By executing this query and iterating through the results in a separate while loop, were able to display all the reports associated with the student on their profile page. This approach ensured that all relevant information was accurately displayed for each student.
![Student 1st row in table “student” student_id [2]](https://i.stack.imgur.com/4CkYm.jpg){kind=link}
![Student 2nd row in table “student” student_id [5]](https://i.stack.imgur.com/NTpUk.jpg){kind=link}
