| 第15週 PHP-MySQL-兩個資料表的關聯 inner join 與 left join先建立資料庫環境
DROP DATABASE IF EXISTS mydb;
CREATE DATABASE mydb DEFAULT CHARACTER SET utf8;
USE mydb;
CREATE TABLE depart(
depart_class CHAR(6) COMMENT '班級代碼',
depart_name VARCHAR(20) COMMENT '班級名稱',
primary key(depart_class)
) comment '班級代碼表';
LOAD DATA INFILE "c:/AppServ/www/mydb/depart2.txt" INTO TABLE depart FIELDS TERMINATED BY ",";
CREATE TABLE stmd (
depart_class CHAR(6) COMMENT '班級代碼',
student_no CHAR(9) COMMENT '學號',
student_name VARCHAR(20) COMMENT '姓名',
address VARCHAR(60) COMMENT '地址',
primary key(student_no)
) comment '學生基本資料表';
LOAD DATA INFILE "c:/AppServ/www/mydb/stmd2.txt" INTO TABLE stmd FIELDS TERMINATED BY ",";
第一個PHP程式採用 inner joint
<?php // 請注意：本程式不完整，缺插入、更新確認函數
include "config.php";
if (isset($_POST["command"])) $command=$_POST["command"];
else $command="";
if ($command=="" || $command=="返回") {
display_first_page($conn);
}
elseif ($command=="查詢") {
display_search_page($conn);
}
elseif ($command=="新增") {
display_insert_page($conn);
display_first_page($conn);
}
elseif ($command=="更新") {
display_modify_page($conn);
}
elseif ($command=="刪除") {
display_delete_page($conn);
display_first_page($conn);
} mysqli_close($conn);
function display_first_page($conn) { // 第一個頁面
echo "<html><head><title>MySql與PHP結合-兩個資料表的關聯</title>
<link rel='stylesheet' type='text/css' href='style.css'>
</head><body><center>
<table> <form method='post' action=''>
<tr class='alt0'><td colspan=4>MySql與PHP結合-兩個資料表的關聯</td></tr>
<tr><td class='alt1'>班級</td><td>
<select name='depart_class' size='1'>
<option value=''>選擇班級</option>";
$sql="select * from depart order by depart_class";
$result=mysqli_query($conn,$sql);
while ($myrow=mysqli_fetch_array($result)) {
$depart_class=$myrow["depart_class"];
$depart_name=$myrow["depart_name"];
echo "<option value='$depart_class'>$depart_name</option>";
}
echo "</td></tr>
<tr><td class='alt1'>學號</td>
<td><input type='text' id='student_no' name='student_no' /></td></tr>
</table>
<input class='cmd' type='submit' name='command' value='查詢' >
<input class='cmd' type='submit' name='command' value='新增' >
<input class='cmd' type='reset' name='command' value='清除' >
</form></center></body></html>";
}
function display_search_page($conn) {
$today=date("Y-m-d");
$depart_class=trim($_POST["depart_class"]);
if ($depart_class=="") $depart_class="%";
else $depart_class="%".$depart_class."%";
$student_no=trim($_POST["student_no"]);
if ($student_no=="") $student_no="%";
else $student_no="%".$student_no."%";
// 採inner join
$sql ="select stmd.depart_class,depart.depart_name,stmd.student_no,stmd.student_name,";
$sql.="stmd.address from stmd,depart where stmd.depart_class like '$depart_class' and ";
$sql.="student_no like '$student_no' and stmd.depart_class=depart.depart_class ";
$sql.="order by depart_class,student_no";
$result=mysqli_query($conn,$sql);
echo "<html><head><title>MySql與PHP結合-兩個資料表的關聯</title>
<link rel='stylesheet' type='text/css' href='style.css'>
</head><body><center><table>
<form method='post' action=''>
<tr class='alt3'><td colspan=5>[stmd2.php]MySql與PHP結合-兩個資料表的關聯[$today]</td></tr>
<tr class='alt1'><td>序號</td><td>班級代碼</td><td>班級名稱</td><td>學號</td><td>選擇</td></tr>";
$cnt=0;
while ($myrow=mysqli_fetch_array($result)) {
$depart_class=$myrow["depart_class"];
$depart_name=$myrow["depart_name"];
$student_no=$myrow["student_no"];
$cnt++;
$bgc=($cnt % 2) + 2;
echo "<tr class='alt$bgc'><td>$cnt</td><td>$depart_class</td><td>$depart_name</td>
<td>$student_no</td><td>
<input type='radio' name='student_no' value='$student_no'></td></tr>";
}
echo "</table>
<input class='cmd' type='submit' name='command' value='更新'>
<input class='cmd' type='submit' name='command' value='刪除'
onclick='return confirm(\"確定要刪除嗎?\")' >
<input class='cmd' type='submit' name='command' value='返回'>
</center></body></html>";
} function display_modify_page($conn) { // 更新頁面
$student_no=$_POST["student_no"];
if ($student_no=="") return;
$sql ="select stmd.depart_class,depart.depart_name,stmd.student_no,stmd.student_name,";
$sql.="stmd.address from stmd,depart where student_no='$student_no' and ";
$sql.="stmd.depart_class=depart.depart_class";
$result=mysqli_query($conn,$sql);
$myrow=mysqli_fetch_array($result);
$save_depart_class=$myrow["depart_class"];
$save_depart_name=$myrow["depart_name"];
echo "<html><head><title>MySql與PHP結合-兩個資料表的關聯</title>
<link rel='stylesheet' type='text/css' href='style.css'>
</head><body><center>
<table> <form method='post' action=''>
<tr class='alt0'><td colspan=4>MySql與PHP結合-兩個資料表的關聯</td></tr>
<tr><td class='alt1'>班級</td><td>
<select name='depart_class' size='1'>
<option value=''>選擇班級</option>";
$sql="select * from depart order by depart_class";
$result=mysqli_query($conn,$sql);
while ($myrow=mysqli_fetch_array($result)) {
$depart_class=$myrow["depart_class"];
$depart_name=$myrow["depart_name"];
if ($save_depart_class==$depart_class)
echo "<option value='$depart_class' selected>$depart_name</option>";
else
echo "<option value='$depart_class'>$depart_name</option>";
}
echo "</td></tyr>
<tr><td class='alt1'>學號</td>
<td><input type='text' id='student_no' name='student_no'
value='$student_no' /></td></tr>
</table>
<input class='cmd' type='submit' name='command' value='確認'
onclick=\" return confirm('確定要更新嗎?') \" >
<input class='cmd' type='submit' name='command' value='返回' >
</form></center></body></html>";
} function display_delete_page($conn) {
$student_no=trim($_POST["student_no"]);
$sql="delete from stmd where student_no='$student_no' ";
$retcode=mysqli_query($conn,$sql);
if ($retcode===true) {
echo "<script> alert('刪除一筆記錄成功'); </script>";
} else {
echo "<script> alert('刪除一筆記錄失敗'); </script>";
}
} ?>
第二個程式採用 left join
<?php // 請注意：本程式不完整，缺插入、更新確認函數
include "config.php";
if (isset($_POST["command"])) $command=$_POST["command"];
else $command="";
if ($command=="" || $command=="返回") {
display_first_page($conn);
}
elseif ($command=="查詢") {
display_search_page($conn);
}
elseif ($command=="新增") {
display_insert_page($conn);
display_first_page($conn);
}
elseif ($command=="更新") {
display_modify_page($conn);
}
elseif ($command=="刪除") {
display_delete_page($conn);
display_first_page($conn);
} mysqli_close($conn);
function display_first_page($conn) { // 第一個頁面
echo "<html><head><title>MySql與PHP結合-兩個資料表的關聯</title>
<link rel='stylesheet' type='text/css' href='style.css'>
</head><body><center>
<table> <form method='post' action=''>
<tr class='alt0'><td colspan=4>MySql與PHP結合-兩個資料表的關聯</td></tr>
<tr><td class='alt1'>班級</td><td>
<select name='depart_class' size='1'>
<option value=''>選擇班級</option>";
$sql="select * from depart order by depart_class";
$result=mysqli_query($conn,$sql);
while ($myrow=mysqli_fetch_array($result)) {
$depart_class=$myrow["depart_class"];
$depart_name=$myrow["depart_name"];
echo "<option value='$depart_class'>$depart_name</option>";
}
echo "</td></tr>
<tr><td class='alt1'>學號</td>
<td><input type='text' id='student_no' name='student_no' /></td></tr>
</table>
<input class='cmd' type='submit' name='command' value='查詢' >
<input class='cmd' type='submit' name='command' value='新增' >
<input class='cmd' type='reset' name='command' value='清除' >
</form></center></body></html>";
}
function display_search_page($conn) {
$today=date("Y-m-d");
$depart_class=trim($_POST["depart_class"]);
if ($depart_class=="") $depart_class="%";
else $depart_class="%".$depart_class."%";
$student_no=trim($_POST["student_no"]);
if ($student_no=="") $student_no="%";
else $student_no="%".$student_no."%";
// 採 left join
$sql ="select stmd.depart_class,depart.depart_name,stmd.student_no,stmd.student_name from stmd ";
$sql.="left join depart on stmd.depart_class=depart.depart_class where stmd.depart_class like ";
$sql.="'$depart_class' and student_no like '$student_no' order by depart_class,student_no";
$result=mysqli_query($conn,$sql);
echo "<html><head><title>MySql與PHP結合-兩個資料表的關聯</title>
<link rel='stylesheet' type='text/css' href='style.css'>
</head><body><center><table>
<form method='post' action=''>
<tr class='alt3'><td colspan=5>[stmd2.php]MySql與PHP結合-兩個資料表的關聯[$today]</td></tr>
<tr class='alt1'><td>序號</td><td>班級代碼</td><td>班級名稱</td><td>學號</td><td>選擇</td></tr>";
$cnt=0;
while ($myrow=mysqli_fetch_array($result)) {
$depart_class=$myrow["depart_class"];
$depart_name=$myrow["depart_name"];
$student_no=$myrow["student_no"];
$cnt++;
$bgc=($cnt % 2) + 2;
echo "<tr class='alt$bgc'><td>$cnt</td><td>$depart_class</td><td>$depart_name</td>
<td>$student_no</td><td>
<input type='radio' name='student_no' value='$student_no'></td></tr>";
}
echo "</table>
<input class='cmd' type='submit' name='command' value='更新'>
<input class='cmd' type='submit' name='command' value='刪除'
onclick='return confirm(\"確定要刪除嗎?\")' >
<input class='cmd' type='submit' name='command' value='返回'>
</center></body></html>";
} function display_modify_page($conn) { // 更新頁面
$student_no=$_POST["student_no"];
if ($student_no=="") return;
$sql ="select stmd.depart_class,depart.depart_name,stmd.student_no,stmd.student_name,";
$sql.="stmd.address from stmd,depart where student_no='$student_no' and ";
$sql.="stmd.depart_class=depart.depart_class";
$result=mysqli_query($conn,$sql);
$myrow=mysqli_fetch_array($result);
$save_depart_class=$myrow["depart_class"];
$save_depart_name=$myrow["depart_name"];
echo "<html><head><title>MySql與PHP結合-兩個資料表的關聯</title>
<link rel='stylesheet' type='text/css' href='style.css'>
</head><body><center>
<table> <form method='post' action=''>
<tr class='alt0'><td colspan=4>MySql與PHP結合-兩個資料表的關聯</td></tr>
<tr><td class='alt1'>班級</td><td>
<select name='depart_class' size='1'>
<option value=''>選擇班級</option>";
$sql="select * from depart order by depart_class";
$result=mysqli_query($conn,$sql);
while ($myrow=mysqli_fetch_array($result)) {
$depart_class=$myrow["depart_class"];
$depart_name=$myrow["depart_name"];
if ($save_depart_class==$depart_class)
echo "<option value='$depart_class' selected>$depart_name</option>";
else
echo "<option value='$depart_class'>$depart_name</option>";
}
echo "</td></tyr>
<tr><td class='alt1'>學號</td>
<td><input type='text' id='student_no' name='student_no'
value='$student_no' /></td></tr>
</table>
<input class='cmd' type='submit' name='command' value='確認'
onclick=\" return confirm('確定要更新嗎?') \" >
<input class='cmd' type='submit' name='command' value='返回' >
</form></center></body></html>";
} function display_delete_page($conn) {
$student_no=trim($_POST["student_no"]);
$sql="delete from stmd where student_no='$student_no' ";
$retcode=mysqli_query($conn,$sql);
if ($retcode===true) {
echo "<script> alert('刪除一筆記錄成功'); </script>";
} else {
echo "<script> alert('刪除一筆記錄失敗'); </script>";
}
} ?>
作業
完成 stmd 與 depart兩個資料表所有欄位的程式介面
| |
