nclude ('config.php');
$strSQL1="SELECT tb_students.id_student, tb_parent.id_parent , tb_parent.id_teacher,
(SELECT SUM(IF(FIND_IN_SET(id_question,'1,2,5') ,score_p,0))
as score_p1 FROM tbp_answer ) as p1,
(SELECT SUM(IF(FIND_IN_SET(id_question,'1,2,5') ,score_s,0))
as score_s1 FROM tb_answer ) as s1,
(SELECT SUM(IF(FIND_IN_SET(id_question,'3,4') ,score_p,0))
as score_p2 FROM tbp_answer ) as p2
(SELECT SUM(IF(FIND_IN_SET(id_question,'3,4') ,score_s,0))
as score_s2 FROM tb_answer ) as s2
FROM tbp_answer left join tbs_answer
on tbs_answer.id_student = tbp_answer.id_student
Where tbp_answer.id_student = tbs_answer.id_student
group by tbs_answer.id_student ";
$objQuery1 = mysql_query($strSQL1) or die ("Error Query [".$strSQL1."]");
while($row = mysql_fetch_array($objQuery1)) {
?>
<tr>
<td>คะแนนรวม ชุดที่ 1<?= $row['p1']+$row['s1']; ?></td>
<td>คะแนนรวม ชุดที่ 2<?= $row['p2']+$row['s2']; ?></td>
<?}?>
ลองเอาไปรันดูก่อน
ของครูก็ทำเหมือนกัน แล้วค่อยเอาสองตัวมา JOIN กัน เพื่อ เอา total มาบวกกัน
/*------คะแนนผู้ปกครอง--------------*/
SELECT
'125' AS num
,t1.id_student
,t1.id_parent
, t1.id_teacher
,SUM(t.score_s) AS total_score_s
FROM tbp_answer t1
WHERE id_question IN ('1','2','5')
GROUP BY
,t1.id_student
,t1.id_parent
, t1.id_teacher
UNION
SELECT
'34' AS num
,t2.id_student
,t2.id_parent
, t2.id_teacher
,SUM(t.score_s) AS total_score_s
FROM tbp_answer t2
WHERE id_question IN ('3','4')
GROUP BY
t1.id_student
,t1.id_parent
, t1.id_teacher