如何从表中选择具有相同查询但条件不同的数据库中的数据?
I have two tables:
- student
- attendance
I want to select
the attendance of students by conditions of year and exam_type (midterm, final) and show in one table.
$student_attendance1 = mysqli_query($con, "SELECT *
FROM student_attendance
INNER JOIN student
ON student.student_id = student_attendance.student_id
WHERE attendance_year=$attendance_year
AND exam_type=1");
$row_studend_attendance1 = mysqli_fetch_assoc($student_attendance1);
$studend_attendance2 = mysqli_query($con, "SELECT *
FROM student_attendance
INNER JOIN student
ON student.student_id = student_attendance.student_id
WHERE attendance_year=$attendance_year
AND exam_type=1");
$row_studend_attendance2 = mysqli_fetch_assoc($student_attendance2);
How can I do this? DATA
我有两个表: p>
- 学生 li>
- 出勤率 li>
ol>
我想
选择 code>学生按年份和考试类型(期中考试,期末考试)的出勤情况 并显示在一个表格中。 p>
$ student_attendance1 = mysqli_query($ con,“SELECT * FROM student_attendance INNER JOIN student ON student.student_id = student_attendance.student_id WHERE attendance_year = $ attendance_year AND exam_type = 1“); $ row_studend_attendance1 = mysqli_fetch_assoc($ student_attendance1); $ studend_attendance2 = mysqli_query($ con,”SELECT * FROM student_attendance INNER JOIN student ON student .student_id = student_attendance.student_id WHERE attendance_year = $ attendance_year AND exam_type = 1“); $ row_studend_attendance2 = mysqli_fetch_assoc($ student_attendance2); code> pre>
我怎样才能做到这一点? DATA p> div>
I will suppose you always have one, and only one, row per student for exam_type = 1 or exam_type = 2(?) (By the way, your two queries, as you wrote them, are absolutely identical...)
You should add a clause like "ORDER BY student.student_id ASC" to be sure you retrieve the datas from your two queries in the same order.
Then, all you have to do is display your datas into a table :
<?php
$student_attendance1 = mysqli_query($con, "SELECT *
FROM student_attendance
INNER JOIN student
ON student.student_id = student_attendance.student_id
WHERE attendance_year=$attendance_year
AND exam_type=1 ORDER BY student.student_id ASC");
$studend_attendance2 = mysqli_query($con, "SELECT *
FROM student_attendance
INNER JOIN student
ON student.student_id = student_attendance.student_id
WHERE attendance_year=$attendance_year
AND exam_type=2 ORDER BY student.student_id ASC");
while($row_studend_attendance1 = mysqli_fetch_assoc($student_attendance1)) {
$row_studend_attendance2 = mysqli_fetch_assoc($student_attendance2);
?> // Close your PHP tag.
<table>
<tr>
<th>ID</th>
<th>Name</th>
<th>F/Name</th>
<th>Exams</th>
<th>Year days</th>
<th>Present</th>
<th>Absent</th>
<th>Sickness</th>
<th>Permission</th>
</tr>
<tr>
<td rowspan="3"><?= $row_studend_attendance1['student_id'] ?></td>
<td rowspan="3"><?= $row_studend_attendance1['surname'] ?></td>
<td rowspan="3"><?= $row_studend_attendance1['firstname'] ?></td>
<td>Midterm</td>
<td><?= $row_studend_attendance1['year_days'] ?></td>
<td><?= $row_studend_attendance1['present'] ?></td>
<td><?= $row_studend_attendance1['absent'] ?></td>
<td><?= $row_studend_attendance1['sickness'] ?></td>
<td><?= $row_studend_attendance1['permission'] ?></td>
</tr>
<tr>
<td>Final</td>
<td><?= $row_studend_attendance2['year_days'] ?></td>
<td><?= $row_studend_attendance2['present'] ?></td>
<td><?= $row_studend_attendance2['absent'] ?></td>
<td><?= $row_studend_attendance2['sickness'] ?></td>
<td><?= $row_studend_attendance2['permission'] ?></td>
</tr>
<tr>
<td>Sum</td>
<td><?= $row_studend_attendance1['year_days'] + $row_studend_attendance2['year_days'] ?></td>
<td><?= $row_studend_attendance1['present'] + $row_studend_attendance2['present'] ?></td>
<td><?= $row_studend_attendance1['absent'] + $row_studend_attendance2['absent'] ?></td>
<td><?= $row_studend_attendance1['sickness'] + $row_studend_attendance2['sickness'] ?></td>
<td><?= $row_studend_attendance1['permission'] + $row_studend_attendance2['permission'] ?></td>
</tr>
</table>
<?php } ?>
I hope I answer your question. If not, feel free to ask again.