第12週-PHP-MySQL-SELECT範例利用上週的資料表
CREATE TABLE base (
oid int(5) AUTO_INCREMENT COMMENT '識別碼',
gender CHAR(1) NOT NULL COMMENT '1表男性 2表女性',
name VARCHAR(20) NOT NULL COMMENT '姓名',
PRIMARY KEY(oid)
) COMMENT '性別姓名表'; 瞭解PHP程式範例後在stmd資料表中加入性別欄gender(利用Select)
另外, 班級代碼也改用select由164D21(四技資管二甲), 164D22(四技資管二乙),
164D23(四技資管二丙)選擇一個
完成stmd資料表的應用程式界面
<?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_confirm_page($conn);
display_first_page($conn);
}
elseif ($command=="刪除") {
display_delete_page($conn);
display_first_page($conn);
} mysqli_close($conn);
function display_first_page($conn) {
echo "<html><head><title></title>
<link rel='stylesheet' type='text/css' href='style.css'>
<script>
function check() {
var gender=document.getElementById('gender').value;
if (gender=='') {
alert('請選擇性別'); return false;
}
return true;
}
</script>
</head><body><center>
<table> <form method='post' action=''>
<tr class='alt0'><td colspan=4>Select範例</td></tr>
<tr><td class='alt1'>性別</td><td>
<select id='gender' name='gender' size='1'>
<option value=''>性別</option>
<option value='1'>男</option>
<option value='2'>女</option>
</select></td></tr>
<tr><td class='alt1'>姓名</td>
<td><input type='text' id='name' name='name' /></td></tr>
</table>
<input class='cmd' type='submit' name='command' value='查詢' >
<input class='cmd' type='submit' name='command' value='新增'
onclick='return check()' >
<input class='cmd' type='reset' name='command' value='清除' >
</form></center></body></html>";
} function display_insert_page($conn) {
$gender=trim($_POST["gender"]);
$name=trim($_POST["name"]);
$sql="insert into base values(null,'$gender','$name')";
$retcode=mysqli_query($conn,$sql);
if ($retcode===true) {
echo "<script> alert('插入一筆記錄成功'); </script>";
} else {
echo "<script> alert('插入一筆記錄失敗'); </script>";
}
}
function display_search_page($conn) {
$today=date("Y-m-d");
$gender=trim($_POST["gender"]);
if ($gender=="") $gender="%";
else $gender="%".$gender."%";
$name=trim($_POST["name"]);
if ($name=="") $name="%";
else $name="%".$name."%";
$sql="select * from base where gender like '$gender' and ";
$sql.="name like '$name' order by gender,name";
$result=mysqli_query($conn,$sql);
echo "<html><head><title></title>
<link rel='stylesheet' type='text/css' href='style.css'>
</head><body><center><table>
<form method='post' action=''>
<tr class='alt3'><td colspan=4>[base.php]Select範例 [$today]</td></tr>
<tr class='alt1'><td>序號</td><td>性別</td><td>姓名</td>
<td>選擇</td></tr>";
$cnt=0;
while ($myrow=mysqli_fetch_array($result)) {
$oid=$myrow["oid"];
$gender=$myrow["gender"];
$name=$myrow["name"];
$cnt++;
$bgc=($cnt % 2) + 2;
echo "<tr class='alt$bgc'><td>$cnt</td><td>";
if ($gender=="1") echo "男";
else if ($gender=="2") echo "女";
echo "</td>
<td>$name</td><td>
<input type='radio' name='oid' value='$oid'></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) { // 更新頁面
$oid=$_POST["oid"];
if ($oid=="") return;
$sql="select * from base where oid=$oid ";
$result=mysqli_query($conn,$sql);
$myrow=mysqli_fetch_array($result);
$gender=$myrow["gender"];
$name=$myrow["name"];
echo "<html><head><title>Select範例</title>
<link rel='stylesheet' type='text/css' href='style.css'>
<script>
function check() {
var gender=document.getElementById('gender').value;
if (gender=='') {
alert('請選擇性別'); return false;
}
var con=confirm('確定要更新嗎?');
return con;
}
</script>
</head><body><center>
<table> <form method='post' action=''>
<tr class='alt0'><td colspan=4>Select範例</td></tr>
<tr><td class='alt1'>性別</td><td>
<select id='gender' name='gender' size=1>
<option value=''>性別</option>";
if ($gender=="1")
echo "<option selected value='1'/>男</option>";
else
echo "<option value='1'/>男</option>";
if ($gender=="2")
echo "<option selected value='2'/>女</option>";
else
echo "<option value='2'/>女</option>";
echo "</select></td></tr>
<tr><td class='alt1'>姓名</td>
<td><input type='text' id='name' name='name'
value='$name' /></td></tr>
</table>
<input class='cmd' type='submit' name='command' value='確認'
onclick=\" return check() \" >
<input class='cmd' type='submit' name='command' value='返回' >
<input type='hidden' name='oid' value='$oid' >
</form></center></body></html>";
} function display_confirm_page($conn) {
$oid=trim($_POST["oid"]);
$gender=trim($_POST["gender"]);
$name=trim($_POST["name"]);
$sql="update base set gender='$gender',name='$name' where oid=$oid";
$retcode=mysqli_query($conn,$sql);
if ($retcode===true) {
echo "<script> alert('更新一筆記錄成功'); </script>";
} else {
echo "<script> alert('更新一筆記錄失敗'); </script>";
}
} function display_delete_page($conn) {
$oid=trim($_POST["oid"]);
$sql="delete from base where oid=$oid ";
$retcode=mysqli_query($conn,$sql);
if ($retcode===true) {
echo "<script> alert('刪除一筆記錄成功'); </script>";
} else {
echo "<script> alert('刪除一筆記錄失敗'); </script>";
}
} ?>
| 
