<?php

$objConnect = mysql_connect("localhost", "root", "P@ssw0rdMySQL0") or die("Error Connect to Database");
$objDB = mysql_select_db("quiz67");
mysql_query("SET character_set_results=utf8");
mysql_query("SET character_set_client=utf8");
mysql_query("SET character_set_connection=utf8");

$strSQL = "SELECT year, member, email, subject  FROM quiz_pass ";
//$strSQL .= "WHERE member='สพม.ราชบุรี' "; // เปิด comment บรรทัดนี้ เพื่อ เอาฉเพาะ สพม.รบ.
$strSQL .= "GROUP by  member, email,`year`,subject  ORDER BY  `member` , email, year";

$objQuery = mysql_query($strSQL) or die("Error Query [" . $strSQL . "]");
while ($row = mysql_fetch_assoc($objQuery)) {
    if ($row['year'] == "" || $row['member'] == "") continue;
    $list[$row['member']][$row['email']][$row['year']][$row['subject']] = $row;
}
//print_r($list);
echo "<table border=1>";
foreach ($list as $kmember => $member) {
    //print_r($year);
    foreach ($member as $kemail => $email) {

        if (sizeof($email['2565-2566']) >= 10) {
            echo "<tr><td>2565-2566</td><td>$kmember</td><td>$kemail</td>";
            echo "<td>" . sizeof($email['2565-2566']) . "</td>";
            echo "<td>";
            $i = 1;
            foreach ($email['2565-2566'] as $subject) {
                //print_r($subject);
                echo $i++ . ". " . $subject['subject'] . "<br>";
            }
            echo "</td></tr>";
            $count_all['g65-66'] += 1;
            if ($kmember == 'สพม.ราชบุรี') $count_rb['g65-66'] += 1;
        }
        if (sizeof($email['2567']) >= 10) {
            echo "<tr><td>2567</td><td>$kmember</td><td>$kemail</td>";
            echo "<td>" . sizeof($email['2567']) . "</td>";
            echo "<td>";
            $i = 1;
            foreach ($email['2567'] as $subject) {
                echo $i++ . ". " . $subject['subject'] . "<br>";
            }
            echo "</td></tr>";
            $count_all['g67'] += 1;
            if ($kmember == 'สพม.ราชบุรี') $count_rb['g67'] += 1;
        }
        if (sizeof($email['2565-2566']) >= 10 && sizeof($email['2567']) >= 10) {
            //print_r($email);
            echo "<tr><td colspan=5><font color=red>เพชร</font></td></tr>";
            $count_all['d67'] += 1;
            if ($kmember == 'สพม.ราชบุรี') $count_rb['d67'] += 1;
        }
        //break;
    }
}
//print_r($count_all);
echo "ทอง ปี 2565-2566 จำนวน " . $count_all['g65-66'] . "คน<br>";
echo "ทอง ปี 2567 จำนวน " . $count_all['g67'] . "คน<br>";
echo "เพชร ปี 2567 จำนวน " . $count_all['d67'] . "คน<br>";
echo "เฉพาะ สพม.ราชบุรี <br>";
echo "ทอง ปี 2565-2566 จำนวน " . $count_rb['g65-66'] . "คน<br>";
echo "ทอง ปี 2567 จำนวน " . $count_rb['g67'] . "คน<br>";
echo "เพชร ปี 2567 จำนวน " . $count_rb['d67'] . "คน<br>";