企業資訊與管理系
助理教授/日導
羅德興

歷程檔案 Portfolio

Unit 9- MySQL-PHP-RADIO-新增-查詢-刪除-更新範例

壹、建立 www/mydb/radio9.sql 儲存建立資料庫與表格的命令

DROP DATABASE IF EXISTS mydb;

CREATE DATABASE mydb DEFAULT CHARACTER SET utf8;

USE mydb;

CREATE TABLE stmd (

oid INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '遞增識別碼',

depart_class VARCHAR(16) COMMENT '甲班、乙班、丙班、丁班',

student_no VARCHAR(16) COMMENT '1號、2號、3號、4號',

student_name VARCHAR(16) COMMENT '陳生、林生、張生、謝生',

address VARCHAR(16) COMMENT '台北、新北、台中、高雄',

primary key(oid)

);

貳、建立 www/mydb/style.css

body { width:100%; margin:0 padding:0; font-family:標楷體; }

table { border-collapse:collapse; border:1px solid black;

empty-cells:show; width:100%; }

th { background-color:ccffff; border: 1px solid; font-family:標楷體;}

td { border: 1px solid; text-align : center; font-family:標楷體; }

.alt0 { background-color:#99ffff; font-weight:bold; font-family:標楷體; }

.alt1 { background-color:#ccccff; font-family:標楷體; }

.alt2 { background-color:#ccffff; font-family:標楷體; }

.alt3 { background-color:#fff8c6; font-family:標楷體; }

a: { background:ccccff; border:1px solid ccc; color:000;

padding:.3em .5em; margin-top:1px; margin-bottom:1px; text-align:center;

text-decoration:none; display: inline-block; font-family:標楷體; }

a:link {background-color:ff88ee }

a:visited {background-color:FFFF85;}

a:hover {background-color:FF704D; }

a:active {background-color:FF704D; }

p {background-color: #81F781; display:inline;font-family:標楷體; font-weight:bold;}

.cmd {font-family:標楷體; font-size:18px; background-color:ccccff; width:4em; }

.cmd1 {font-family:標楷體; font-size:18px; background-color:ccccff; width:10em; }

.cmd2 {font-family:標楷體; font-size:18px; background-color:ccccff; width:5em; }

</style>

叁、建立 www/mydb/config.php

<?php

$host = "127.0.0.1";

$user = "root";

$pwd = "12345678";

$dbname="mydb";

$conn=mysqli_connect($host,$user,$pwd) or die("無法連接主機");

mysqli_query($conn,'SET NAMES utf8');

mysqli_select_db($conn,$dbname) or die("無法連接資料庫");

肆、建立PHP程式 www/mydb/radio9.php

<?php

include "config.php";

if (isset($_POST["command"])) $command=$_POST["command"];

else $command="";

if ($command=="" || $command=="返回") {

display_first_page($conn);

}

elseif ($command=="新增") {

display_insert_page($conn);

display_first_page($conn);

}

elseif ($command=="查詢") {

display_search_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>MySql與PHP結合-RADIO-新增-查詢-更新-刪除範例</title>

</head><body><center>

<tr class='alt0'><td colspan=2>MySql與PHP結合-RADIO-新增-查詢-更新-刪除範例</td></tr>

<td><input type='radio' name='depart_class' value='甲班'/>甲班

<input type='radio' name='depart_class' value='乙班'/>乙班

<input type='radio' name='depart_class' value='丙班'/>丙班

<input type='radio' name='depart_class' value='丁班'/>丁班

</td></tr>

<td><input type='radio' name='student_no' value='1號'/>1號

<input type='radio' name='student_no' value='2號'/>2號

<input type='radio' name='student_no' value='3號'/>3號

<input type='radio' name='student_no' value='4號'/>4號

</td></tr></table>

</form></center></body></html>";

}

function display_search_page($conn) {

$depart_class=$_POST["depart_class"];

if ($depart_class=="") $depart_class="%";

$student_no=$_POST["student_no"];

if ($student_no=="") $student_no="%";

$sql="select * from stmd where depart_class like '$depart_class'

and student_no like '$student_no' order by oid";

$result=mysqli_query($conn,$sql);

echo "<html><head><title>MySql與PHP結合-RADIO-新增-查詢-更新-刪除範例</title>

</head><body><center>

<tr class='alt0'><td colspan=3>MySql與PHP結合-RADIO-新增-查詢-更新-刪除範例</td></tr>

<tr class='alt1'><td>班級代碼</td><td>學號</td><td>選擇</td></tr>";

$cnt=0;

while ($myrow=mysqli_fetch_array($result)) {

$oid=$myrow["oid"];

$depart_class=$myrow["depart_class"];

$student_no=$myrow["student_no"];

$bgcolor=$cnt % 2+ 2;

echo "<tr class='alt$bgcolor'><td>$depart_class</td><td>$student_no</td>

<td><input type='radio' name='oid' value='$oid'></td></tr>";

$cnt++;

}

echo "</table>

<input class='cmd' type='submit' name='command' value='刪除'

onclick=\"return confirm('確定要刪除嗎');\" >

</form></center></body></html>";

}

function display_insert_page($conn) {

$depart_class=$_POST["depart_class"];

$student_no=$_POST["student_no"];

if ($depart_class=="" && $student_no=="") {

display_first_page($conn); exit;

}

$sql="insert into stmd values(null,'$depart_class','$student_no','','')";

mysqli_query($conn,$sql);

}

function display_modify_page($conn) {

$oid=$_POST["oid"];

if ($oid=="") {

display_first_page($conn); exit();

}

$sql="select * from stmd where oid=$oid";

$result=mysqli_query($conn,$sql);

$myrow=mysqli_fetch_array($result);

$depart_class=$myrow["depart_class"];

$student_no=$myrow["student_no"];

echo "<html><head><title>MySql與PHP結合-RADIO-新增-查詢-更新-刪除範例</title>

</head><body><center>

<tr class='alt0'><td colspan=2>MySql與PHP結合-RADIO-新增-查詢-更新-刪除範例</td></tr>

<tr><td class='alt1'>班級代碼</td><td>";

if ($depart_class=="甲班")

echo "<input type='radio' name='depart_class' value='甲班' checked />甲班";

else

echo "<input type='radio' name='depart_class' value='甲班' />甲班";

if ($depart_class=="乙班")

echo "<input type='radio' name='depart_class' value='乙班' checked />乙班";

else

echo "<input type='radio' name='depart_class' value='乙班' />乙班";

if ($depart_class=="丙班")

echo "<input type='radio' name='depart_class' value='丙班' checked />丙班";

else

echo "<input type='radio' name='depart_class' value='丙班' />丙班";

if ($depart_class=="丁班")

echo "<input type='radio' name='depart_class' value='丁班' checked />丁班";

else

echo "<input type='radio' name='depart_class' value='丁班' />丁班";

echo "</td></tr>

<tr><td class='alt1'>學號</td><td>";

if ($student_no=="1號")

echo "<input type='radio' name='student_no' value='1號' checked />1號";

else

echo "<input type='radio' name='student_no' value='1號'/>1號";

if ($student_no=="2號")

echo "<input type='radio' name='student_no' value='2號' checked />2號";

else

echo "<input type='radio' name='student_no' value='2號'/>2號";

if ($student_no=="3號")

echo "<input type='radio' name='student_no' value='3號' checked />3號";

else

echo "<input type='radio' name='student_no' value='3號'/>3號";

if ($student_no=="4號")

echo "<input type='radio' name='student_no' value='4號' checked />4號";

else

echo "<input type='radio' name='student_no' value='4號'/>4號";

echo "</td></tr></table>

<input class='cmd1' type='submit' name='command' value='更新確認'

onclick=\"return confirm('確定要更新嗎');\" >

</form></center></body></html>";

}

function display_delete_page($conn) {

$oid=$_POST["oid"];

if ($oid=="") {

display_first_page($conn); exit();

}

$sql="delete from stmd where oid=$oid";

mysqli_query($conn,$sql);

}

function display_confirm_page($conn) {

$oid=$_POST["oid"];

$depart_class=$_POST["depart_class"];

$student_no=$_POST["student_no"];

$sql="update stmd set depart_class='$depart_class',student_no='$student_no'

where oid=$oid";

mysqli_query($conn,$sql);

}

伍、

依據資料表寫一個完整的程式

在前面的例子中, 如果班級很多時, 程式就會變得冗長且被資料綁死

假設班級有第1班, 第2班, 到第20班, 請將班級數儲存為

$depart_class_list=array("第1班","第2班",....,"第20班");

或

$depart_class_str="第1班,第2班,....,第20班";

改寫程式

再進一步以下列資料表作關聯改寫程式

CREATE TABLE class (

class_no CHAR(6) COMMENT '班級代碼' ,

class_name VARCHAR(20) COMMENT '班級名稱',

PRIMARY KEY(class_no)

);

全部共 0則留言

登入帳號密碼代表遵守學術網路規範

文章分類 Labels

最新文章 Top10