資料庫程式設計 – PHP+MySQL ：第 八章 合併查詢程式  上一頁      下一頁

 

8-4 複雜的多表格合併

內容：

  • 8-4-1 範例研討：教師授課班級成績

好像合併查詢能解決許多問題，但有些題目還是要多用腦筋才能達成。以下我們取一個經典範例來說明。

8-4-1 範例研討：教師授課班級成績

(A) 系統功能：Ex8_6

學務處需要一個可供查詢列印教師授課班級名冊及成績的網頁，期望查詢網頁如下：

執行結果的網頁如下：

 (B) SQL 製作技巧

下圖是由 course_db 關係圖查出相關性資料表，系統要求查詢某位老師所授課的某一門課程的名單與成績，可見輸入查詢有：教師姓名與課程名稱等兩種選項。我們由 course.db 的關聯圖中找出與本題有關的資料流程繪成下圖所示。首先利用教師姓名與課程名稱分別由 teachers 與 all_course 資料表中查詢到 teacher_ID 與 course_ID，如果這兩個訊息同時存在於 open_course 資料表中，我們就可以查出該課程的 open_course_ID，並對應到 select_course 資料表中查出哪些學生修讀該課程，並可以得到修課學生的成績(final 欄位)，但我們還要學生資料，因此，在利用 student_ID 到 students 資料表查詢學生資料，很不幸的，它僅註明班級代碼(class_ID)，還要到 classes 資料表查詢該學生班級的名稱。

這個題目很難用巢狀查詢技巧來達成，我們還是用合併查詢最簡單。但我們將查詢 teachers 與 all_course 兩表格之工作由『表單網頁』達成，其餘由『執行網頁』利用合併查詢達成，合併後暫存檔中每一筆紀錄有21 ( 4 + 6 + 7 + 4)個欄位，我們再篩選符合條件的記錄，並擷取需要的欄位，篩選條件如圖中有箭頭標示的。

『執行網頁』的  SQL 程式如下：

Select D.class_name, C.student_ID, C.name, B.final

From open_course as A, select_course as B, students as C, classes as D

Where (A.course_ID = '$course_ID')

And (A.teacher_ID = '$teacher_ID')

And (A.open_course_ID = B.open_course_ID)

And (B.student_ID = C.student_ID)

And (C.class_ID = D.class_ID)

 (C) 網頁製作技巧

由上圖 SQL 程式規劃，網頁設計如下：

(D) 表單網頁程式範例：Ex8_6-form.php

程式範例如下：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

<?php

        $select_db = mysqli_connect("localhost", "root", "12345678", "course_db")

                or die("MySQL 伺服器連結失敗 <br>");

        // 查詢教師編碼

        $sql_query1 = "select teacher_ID, name

                  from teachers";

        $result1 = mysqli_query($select_db, $sql_query1);

        // 查詢課程代碼

        $sql_query2 = "select course_ID, course_name

                   from all_course";

        $result2 = mysqli_query($select_db, $sql_query2);

   echo "<p><font size='3'> 查詢教師授課班成績 </font></p>";

   echo "<form name='表單' method='post' action='Ex8_6-action.php'>";

        //選擇授課教師

        echo "選擇授課教師：";

        echo "<select name='teacher_ID'>";

        while($row=mysqli_fetch_row($result1)) {

            echo "<option value='$row[0]'> $row[1]($row[0])</option><BR>";

            }

        echo "</select>";

                echo "<BR><BR>";

                //選擇授課科目

                echo "選擇授課科目：";

                echo "<select name='course_ID'>";

        while($row=mysqli_fetch_row($result2)) {

            echo "<option value='$row[0]'> $row[1]($row[0])</option><BR>";

            }

        echo "</select>";

        echo "<BR><BR>";

                echo "<input type='submit' value='送出'>";

        echo "</form>";

    mysql_close($db_link);

?>

(E) 執行網頁的程式範例：Ex7_5-action.php

收到 teacher_ID 與 course_ID 兩個訊息後，再合併 open_course、select_course、students 與 classes 等 4 張資料表，範例如下：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

<?php

        $teacher_ID = $_POST["teacher_ID"];

        $course_ID = $_POST["course_ID"];

        $select_db = mysqli_connect("localhost", "root", "12345678", "course_db")

                or die("MySQL 伺服器連結失敗 <br>");

        // 查詢班級開課與授課教師

    $sql_query = "Select D.class_name, C.student_ID, C.name, B.final

                  From open_course as A, select_course as B,

                                       students as C, classes as D

                  Where (A.course_ID = '$course_ID')

                  And (A.teacher_ID = '$teacher_ID')

                  And (A.open_course_ID = B.open_course_ID)

                  And (B.student_ID = C.student_ID)

                  And (C.class_ID = D.class_ID)";

        $result = mysqli_query($select_db, $sql_query);

        //echo "result = $result<br>";

        $items = array("課程名稱", "學號", "姓名", "學期成績");

        echo "<table border = '2'><tr align='center'>";

        // 利用 foreach 迴圈印出陣列

        foreach ($items as $value) {

                echo "<td> $value </td>";

        }

        echo "</tr>";

        while($row=mysqli_fetch_row($result)) {

                echo "<tr>";

                for($j=0; $j<mysqli_num_fields($result); $j++) {

                        echo "<td>$row[$j]</td>";

                }

                echo "</tr>";

        }

        echo "</table>";

        mysql_close($db_link);

?>

翻轉工作室：粘添壽

 

資料庫程式設計：

 

 

 

翻轉電子書系列：