單一資料表查詢

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

翻轉工作室：粘添壽 course_db E-R 關聯圖 <開啟>

第六章巢狀查詢程式

6-1 單一資料表查詢

Select 是 SQL 最基礎的命令，格式看起來非常簡單，但它的變化萬千，是否能在資料庫中萃取資訊，完全要看 select 語句靈活運用的能力，我們需用許多範例來探討。Select 敘述的意思是：依照某些條件底下(Where)，要由某資料表 (From)，擷取哪些欄位(Select)的資料。另外條件也許由若干個條件的邏輯組合(And、or)，查詢結果可將分組設定(Group by)，也可依照某條件分組(Having)，甚至也可由大到小(Asc、Desc)，或由小到大排列(Order by)。查詢語法如下：

Select 想要擷取的欄位(欄位_1, 欄位_2, ..)

From 想要查詢的資料表

Where 查詢條件 (條件敘述 | And 條件敘述 | or 條件敘述)

Group By 分組設定(Asc 或 Desc)

Having 分組條件

Order By 排序設定

Limit 限制設定

6-1-2 範例研討 – 查詢學生資料

(A) 程式功能：Ex6_1

學務處需要一個供查詢學生資料的網頁，可以由學號或姓名查詢，期望查詢網頁如下：

查詢結果的網頁如下：

(B) 製作技巧

題目是希望能由學號或姓名來查詢學生資料。首先我們須知道學生資料儲存哪一個資料表，從系統架構圖中可以看出是儲存於 students 資料表內，用簡單的 select 命令就可以達到目的。

Select *

From students

Where student = “查詢學號” 或 name = “查詢姓名”

(C) 查詢網頁的程式：Ex6_1-form.html

系統要求可以用學號或姓名查詢，因此表單 (<form>) 裡我們增加了兩個按鈕選項 (<input type=”radio”>)，為學號與姓名的選項，兩則的輸出名稱都相同(name=”method”)，由內容(value=”no”、value=”name”) 辨識使用者按哪一個鍵(學號或姓名)。程式範例如下：

<head>

<title>查詢學生資料</title>

</head>

<body>

請輸入：

</form>

</body>

</html>

(D) 執行網頁的程式：Ex6_1-action.php

查詢網頁傳遞過來兩個訊息，其中 $method是使用者選擇學號(no) 或姓名(name)，因此$value 有可能是學號或姓名內容。如果是學號，則將查詢敘述設定為 where condition 的條件 $condition = [“ student_ID = $value”]，否則設定為 $condition = [“name = $value”]。但牽涉到字串連接的問題，我們需要寫成 $condition “條件式”，條件式為：

Student_ID = ”.””.$value.”，意思是 student_ID = 連結(“.”)、再連結 $value(“.$value”)

再將所有語句用雙引號包起來 (“ …”)，程式範例如下：

<! Ex6_1-action.php>

<?php

$method = $_POST["method"];

$value = $_POST["value"];

if ($method == "no") {

$condition = "student_ID = "."'".$value."'";

} else{

$condition = "name = "."'".$value."'";

}

$db_link = mysql_connect("localhost", "root", "123456")

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

$select_db = mysql_select_db("course_db");

$sql_query = "select *

from students

where $condition";

mysql_query("SET NAMES 'utf8'");

$result = mysql_query($sql_query);

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

for($i=0; $i<mysql_num_fields($result); $i++) {

echo "<td>".mysql_field_name($result, $i)."</td>";

}

echo "</tr>";

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

echo "<tr>";

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

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

}

echo "<tr/>";

}

mysql_close($db_link);

6-1-3 範例研討 – 人性化顯示學生資料

(A) 程式功能：Ex6_2

學務處看過 Ex6_1 網頁後，認為顯示資料項目應該可供選擇較理想，且查詢結果就與電腦儲存一樣，也不夠人性化，期望查詢網頁如下：

執行結果的網頁如下：

(B) 製作技巧

Ex6_1 的做法是直接將查詢結果的動態資料表顯示出來。目前系統是要求各項資料分別顯示。首先須知道資料表各欄位的順序(如上圖所示)，我們建立一個陣列儲存各欄位的中文名稱，如，$fields = array("學號","姓名","班級代碼","性別", "電話","電子郵件","地址")，當讀出陣列內每一元素內容時，再配對欄位內容即可。

(C) 查詢網頁的程式：Ex6_2-form.html(與 Ex6_1-form.html 相同)

<head>

<title>查詢學生資料</title>

</head>

<body>

<p><font size="3"> 查詢學生資料(學號或姓名) </font></p>

請輸入學生姓名或學號：

</form>

</body>

</html>

(D) 執行網頁的程式：Ex6_2-action.php

(1) 第 23 ~ 24 行：讀取 $fields[$i] 與 $row[$i] 同一位置($i) 的元素。

<! Ex6_2-action.php>

<?php

$method = $_POST["method"];

$value = $_POST["value"];

if ($method == "no") {

$condition = "student_ID = "."'".$value."'";

} else{

$condition = "name = "."'".$value."'";

}

$db_link = mysql_connect("localhost", "root", "123456")

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

$select_db = mysql_select_db("course_db");

$sql_query = "select *

from students

where $condition";

mysql_query("SET NAMES 'utf8'");

$result = mysql_query($sql_query);

$fields = array("學號","姓名","班級代碼","性別", "電話","電子郵件","地址");

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

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

echo "$fields[$j] = $row[$j] <BR>";

}

echo "<BR>";

}

mysql_close($db_link);

6-1-4 自我挑戰 – 選擇性查詢老師資料

(A) 系統需求：PM6_1

人事處需要製作一套可供查詢老師個資的網頁，期望選擇所欲查詢老師與資料都用選單方式比較人性化，期望查詢網頁如下：

(B) 執行結果網頁如下：

(C) 製作技巧

教師資料儲存於 teachers 資料表內，我們可以利用教師代碼查詢出所要的資料。

(D) 查詢網頁的提示(PM6_1-form.php)

系統要求教師名稱必須利用下拉式選單，因此，我們必須由 teachers 資料表內讀取第 1、2 欄位，前者是教師代碼(teacher_ID)，後者是教師姓名 (name)。另外，所欲查詢的資料也是要下拉式選單。因此，在查詢網頁上的表單( <form>) 就需要兩個選單(<select>)，一個由 PHP 讀取 teachers 資料表，另一個直接編寫個資料項目選項，由變數 $item 記錄查詢哪一項資料。。程式提示如下：

….

<?php

…

$sql_query = "select teacher_ID, name

from teachers";

$result = mysql_query($sql_query);

……

<?php

echo "選擇教師：";

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

mysql_close($db_link);

<p>選擇查詢資料：</p>

</select>

<BR>

</form>

…..

(E) 執行網頁的提示如下：(PM6_1-action.php)

查詢網頁傳遞過來 $teacher_ID 與 $item 表示查詢哪位老師，與查詢哪一項目資料。程式如下：

<!PM6_1-action>

<?php

$teacher_ID = $_POST["teacher_ID"];

$item = $_POST["item"];

…

$sql_query = "select name, $item

from teachers

where teacher_ID = '$teacher_ID'";

$result = mysql_query($sql_query);

$row=mysql_fetch_row($result);

echo "查詢結果：<BR>";

echo "$row[0] 老師的資料是： $row[1] <BR>";

….

(F) 進階顯示結果的網頁：(PM6_1-action-1.php)

上述顯示結果好像很奇怪，沒有註明是甚麼資料，您是否可以再將它改成下列結果：

提示：

….

switch($item) {

case "Dep_ID":

$sql_query_1 = "select dep_name

from department

where dep_ID = '$row[1]'";

$result_1 = mysql_query($sql_query_1);

$row_1= mysql_fetch_row($result_1);

$data = $row_1[0];

$message = "系別名稱";

break;

case "title":

$message = "職務";

$data = $row[1];

break;

case "tel":

…………………….

default:

$message = "錯誤要求";

$data = "沒有資料";

}

……

6-1-5 自我挑戰 – 查詢學生平均分數

(A) 系統需求：PM6_2

學務處需要製作一只以學號查詢該生各科(以開課代碼表示)修課成績與平均分數(先不考慮學分數)，期望查詢網頁如下：

查詢結果的網頁如下：

(B) 製作技巧

系統要求列印學生修哪些課、各科成績與學期總平均分數。學生修課與各科成績登錄於『select_course』資料表內，但表內僅登錄學生修課與各科成績，並沒有記錄總平均分數。因此，我們需要兩個查詢動作，第一個查詢修哪些科目及成績，第二個查詢平均分數。

第一個查詢比較簡單，不再敘述。第二個查詢平均分數，需利用 Group by 語句與求平均值函數。我們以 student_ID 為群組，再計算群組學期成績(final) 的分均值(avg(final))即可，如下：(假設查詢 student_ID = “90211302” )

select student_ID, "平均分數", avg(final)

from select_course

where student_ID = "90211302"

group by student_ID;

(C) 執行網頁的提示：(PM6_2-action.php)

<!filename: PM6_2-action.php>

<?php

…. //查詢各科成績

$sql_query_1 = "select open_course_ID as '開課代碼',final as '學期成績'

from select_course

where student_ID='$student_ID'";

// 查詢平均分數

$sql_query_2 = "select student_ID, avg(final)

from select_course

where student_ID='$student_ID'

group by final";

$result_1 = mysql_query($sql_query_1);

$result_2 = mysql_query($sql_query_2);

// 列印各科成績

// 列印總平均分數

$row=mysql_fetch_row($result_2);

echo "$row[0] 學生總平均分數 = $row[1]";

…

<GOTOP>