資料庫程式設計 – PHP+MySQL 第 七章 巢狀查詢程式  上一頁      下一頁

 

7-4 巢狀查詢:in 連結

內容:

  • 7-4-1 in 連結語法

  • 7-4-2 範例研討:查詢班級開班

  • 7-4-3 範例研討:查詢課程教師

  • 7-4-4 範例研討:查詢系所開課

7-4-1 In 連結語法

當子查詢的結果超過一個的時候,則需用 in 連結,基本語法如下:

Select  欄位_1, 欄位_2, ..               // 父查詢敘述

From 資料表_1

Where 欄位 in (select item               // 子查詢敘述

                       From 資料表_2

                        Where 條件敘述);

譬如,子查詢的結果有 3 (ABC),則一個(A)接一個(B)進入父查詢執行,總共執行 3 次。

7-4-2 範例研討:查詢班級開課

(A) 系統需求:Ex7_7

教務處希望製作一個可提供查詢班級開課課程網頁,期望查詢網頁如下:

查詢結果的網頁如下:

(B) SQL 製作技巧

全校所有開課課程登錄於 open_course 表內,由 class_ID 表示開在哪一班,course_ID 為課程代碼。首先須由 classes 表查出哪一班級的班級代碼 (class_ID),又利用 course_ID all_course 表查出課程資料,但每班課程會超過一門,因此用『in』連結,SQL語法如下: (假設查詢『進四資管二丙』。

select course_ID, course_name, required, credit

from all_course

where course_ID in (select course_ID

                                 from open_course

                                 where class_ID = $class_ID);

(C) 網頁設計技巧

此題目需要三個查詢命令連結,吾人可將第一個命令 (查詢 class_ID) 由『選單網頁』完成,它再將 class_ID 傳遞給『執行網頁』,再執行兩個查詢命令,如下圖:

(D) 表單網頁程式:Ex7_7-form.php

我們開啟 classes 表查詢 class_ID class_name,將它製作成下拉式選單,使用者點選後,傳遞 class_ID 給處理網頁。範例如下: ( Ex7_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

<?php

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

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

        // 查詢所有班級名稱與班級代碼

        $sql_query = "select class_ID, class_name

                  from classes";

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

   echo "<p><font size='3'> 查詢班級名冊 </font></p>";

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

        echo "選擇班級:";

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

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

            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_7-action.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

<?php

 

        $class_ID = $_POST["class_ID"];

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

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

        // 查詢班級名冊

    $sql_query = "select course_ID, course_name, required, credit

                from all_course

                where course_ID in (select course_ID

                                 from open_course

                                 where class_ID = $class_ID)";

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

        // 列印班級名冊

        $items = array("課程代碼","課程名稱","必選修","學分數");

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

        for($i=0; $i<4; $i++) {

                echo "<td>".$items[$i]."</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>";

        }

        mysql_close($db_link);

?>

7-4-3範例研討:查詢課程教師

備註:『執行網頁』使用 foreach 迴圈。

(A) 程式功能:Ex7-8

請製作一個用『課程名稱』查詢這學期有那些老師開這門課,期望『表單網頁』如下:課程有哪幾位老師的姓名,結果如下:

 

查詢結果的『執行網頁』查詢結果如下:

 

(B) SQL 製作技巧

全校這學期所開的課程都登錄於 open_course 資表內,但裡面僅記錄課程編碼 course_ID,因此需利用課程名稱由 all_course 表查出其 course_ID。另一方面,由 course_ID 查出開課教師編碼,該課程可能有多位老師開,因此需用『in』連結 teachers 表查出教師名稱,SQL語法如下: (假設查詢『資料庫管理系統』。

select teacher_ID, name

from teachers

where teacher_ID in (select teacher_ID

                                 from open_course

                                where course_ID = (select course_ID

                                                         from all_course

                                           where course_name = "資料庫管理系統");

(C) 網頁設計技巧

此題目需要三個查詢命令連結,吾人可將第一個命令 (查詢 course_ID) 由『選單網頁』完成,它再將 course_ID 傳遞給『執行網頁』,再執行兩個查詢命令,如下圖:

(D) 表單網頁程式:Ex7_8-form.php

我們開啟 all_course 表查詢 course_ID course_name,將它製作成下拉式選單,使用者點選後,傳遞 course_ID 給處理網頁。範例如下: ( Ex7_7-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

<?php

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

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

        // 查詢所有班級名稱與班級代碼

        $sql_query = "select course_ID, course_name

                  from all_course";

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

   echo "<p><font size='3'> 查詢開課課程教師 </font></p>";

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

        echo "選擇課程名稱:";

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

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

            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_8-action.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

<?php

        $course_ID = $_POST["course_ID"];   

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

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

        // 查詢開課課程教師

    $sql_query = "select teacher_ID, name

                from teachers

                where teacher_ID in (select teacher_ID

                                  from open_course

                                  where course_ID = '$course_ID')";

       

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

        $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);

?>

7-4-4 範例研討:查詢系所開課

備註:兩只 in 連結。

(A) 系統需求:Ex7_9

請製作一個可以查詢某一系這學期開了哪些課程(列出課程名稱,與相關資料)。期望查詢網頁如下:

查詢結果的網頁如下:

 (B) SQL 製作技巧

SQL 查詢技巧如下圖所示:使用者輸入『系別名稱』(dep_name) department 表查詢出 Dep_ID,再利用 Dep_ID teachers 表內查詢出該系所所有教師的 teacher_ID,應該多位教師,因此用 in 連結到 open_course 表查詢該系所開課程的 course_ID,也應該有多門課,用 in 連結 all_course 表查詢課程資料。

SQL 程式如下:

Select *

Form all_course

Where course_ID in (Select course_ID

    from open_course as A

    Where teacher_ID in (Select teacher_ID

                                    From teachers

                                    Where Dep_ID = ‘$dep_ID’));

 (C) 網頁製作技巧

『表單網頁』製作下拉式選單由 department 表查詢出 Dep_ID,傳送給『執行網頁』,由它利用兩個 in 連結由 teachersopen_courseall_course 查詢出結果,如下圖所示:

(D) 表單網頁:Ex7_9-form.php

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

<?php

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

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

        // 查詢系所編碼

        $sql_query = "select Dep_ID, dep_name

                  from department";

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

   echo "<p><font size='3'> 查詢系所開課課程資料 </font></p>";

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

        echo "選擇系別名稱:";

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

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

            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_9-action.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

<?php

        $Dep_ID = $_POST["Dep_ID"];       

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

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

        // 查詢系所開課課程

    $sql_query = "select course_ID, course_name, required, credit

                from all_course

                where course_ID in (select course_ID

                             from open_course

                             where teacher_ID in (select teacher_ID

                                                                      from teachers

                                                                         where Dep_ID = '$Dep_ID'))";

       

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

        $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);

?>

 

主講人:粘添壽博士

course_db 關聯圖

 

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