01.<?PHP
02. $query = " SELECT GROUP_CONCAT(stName SEPARATOR ',') as student_name
03. , GROUP_CONCAT(sc_name SEPARATOR ',') as school_name
04. , count(id) AS total
05. FROM (
06. SELECT tbl_data1.id
07. , CONCAT(m.m_firstname,'',m.m_name,' ',m.m_lastname) as stName
08. , t.sc_name
09. , tbl_data1.m_id
10. FROM tbl_data1
11. INNER JOIN tbl_member AS m ON (tbl_data1.m_id = m.m_id)
12. INNER JOIN tbl_school as t ON (t.sc_id = m.m_scho)
13. ) as a
14. GROUP BY m_id ";
15. $result = mysqli_query($conn, $query);
16. $numStudent = mysqli_num_rows($result);
17.?>
18.<h1>จำนวนสมาชิก <?PHP echo number_format($numStudent); ?> คน</h1>
19.<table>
20. <thead>
21. <tr>
22. <th>รายชื่อนักเรียน</th>
23. <th>ชื่อโรงเรียน</th>
24. <th>จำนวนนักเรียน</th>
25. </tr>
26. </thead>
27. <tbody>
28. <?PHP
29. while ($row = mysqli_fetch_array($result)) {
30. ?>
31. <tr>
32. <td>
33. <?PHP
34. echo str_replace(',', '<br>', $row['student_name']);
35. ?>
36. </td>
37. <td>
38. <?PHP
39. echo $row['school_name'];
40. ?>
41. </td>
42. <td>
43. <?PHP
44. echo number_format($row['total']);
45. ?>
46. </td>
47. </tr>
48. <?PHP } ?>
49. </tbody>
50.</table>
