資料庫程式設計 – 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_courseselect_coursestudents 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);

?>

主講人:粘添壽博士

 

資料庫程式設計 - PHP+MySQL: