PHP-PHP操作MySQL
PHP操作MySQL
说明：下面在MySQL中使用的utf8mb4的编码，PHP文件的保存格式为utf-8。
如果出现乱码的问题，或者由于乱码导致无法建表的参考：https://mathiasbynens.be/notes/mysql-utf8mb4#utf8-to-utf8mb4
亲测有用，可仔细阅读（虽然是英文的，但是应该是可以看懂的）操作。
一、建库建表
- SY8_1_CREATE-TABLE.php
<?php
//drop database if exists pxscj;
$conn= mysqli_connect('localhost','root','') or die('连接失败');
//die()--操作失败时输出提示信息
//良好的编程--这里应该if($conn)
$sql="create database if not exists pxscj";
$result=mysqli_query($conn, $sql) or die('表创建失败：'.mysqli_error($conn));
//mysql_error()返回上一个MySQL操作产生错误信息
mysqli_select_db($conn, 'PXSCJ') or die('选择数据库失败');
mysqli_query($conn, "SET NAMES utf8mb4");
//设置字符集为utf8mb4
$sql="create table if not exists XSB( 学号 CHAR(6) NOT NULL PRIMARY KEY, 姓名 CHAR(8) NOT NULL, 性别 TINYINT(1) NULL DEFAULT 1, 出生时间 DATE NULL, 专业 CHAR(12) NULL, 总学分 INT(4) NULL DEFAULT 0, 备注 TEXT NULL)";
$result=mysqli_query($conn, $sql) or die('表创建失败：'.mysqli_error($conn));
$sql="create table if not exists KCB( 课程号 CHAR(3) NOT NULL PRIMARY KEY, 课程名 CHAR(16) NOT NULL, 开课学期 TINYINT(1) NULL DEFAULT 1, 学时 TINYINT(1) NULL, 学分 TINYINT(1) NOT NULL)";
$result=mysqli_query($conn, $sql) or die('表创建失败：'.mysqli_error($conn));
$sql="create table if not exists CJB( 学号 CHAR(6) NOT NULL, 课程号 CHAR(3) NOT NULL, 成绩 INT(4) NULL, PRIMARY KEY(学号,课程号))";
$result=mysqli_query($conn, $sql) or die('表创建失败：'.mysqli_error($conn));
$sql="create table if not exists userinfo( username VARCHAR(20) NOT NULL PRIMARY KEY, password VARCHAR(20) NOT NULL, sex TINYINT NOT NULL, age INT NULL, email CHAR(30) NULL)";
$result=mysqli_query($conn, $sql) or die('表创建失败：'.mysqli_error($conn));
?>
- SY8_1_INSERT.php
<?php
$conn= mysqli_connect('localhost','root','') or die('连接失败');
//die()--操作失败时输出提示信息
mysqli_select_db($conn, 'PXSCJ') or die('选择数据库失败');
mysqli_query($conn, "SET NAMES utf8mb4");
//设置字符集为utf8mb4
$sql="insert into XSB values('081101', '王林', 1, '1990-02-10', '计算机', 50, NULL)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into XSB values('081102', '程明', 1, '1991-02-01', '计算机', 50, NULL)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into XSB values('081103', '王燕', 0, '1989-10-06', '计算机', 50, NULL)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into XSB values('081104', '韦严平', 1, '1990-08-26', '计算机', 50, NULL)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into XSB values('081106', '李方方', 1, '1990-11-20', '计算机', 50, NULL)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into XSB values('081107', '李明', 1, '1990-05-01', '计算机', 54, NULL)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into XSB values('081108', '林一帆', 1, '1989-08-05', '计算机', 52, NULL)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into XSB values('081109', '张强民', 1, '1989-08-11', '计算机', 50, NULL)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into XSB values('081110', '张蔚', 0, '1991-07-22', '计算机', 50, NULL)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into XSB values('081111', '赵琳', 0, '1990-03-18', '计算机', 50, NULL)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into XSB values('081113', '严红', 0, '1991-08-11', '计算机', 48, NULL)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into XSB values('081201', '王敏', 1, '1989-06-10', '通信工程', 42, NULL)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into XSB values('081202', '王林', 1, '1989-01-29', '通信工程', 40, NULL)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into XSB values('081203', '王玉民', 1, '1990-03-26', '通信工程', 42, NULL)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into XSB values('081204', '马琳琳', 0, '1989-02-10', '通信工程', 42, NULL)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into XSB values('081206', '李计', 1, '1989-09-20', '通信工程', 42, NULL)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into XSB values('081210', '李红庆', 1, '1989-05-01', '通信工程', 44, NULL)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into XSB values('081216', '孙祥欣', 1, '1989-03-19', '通信工程', 42, NULL)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into XSB values('081218', '孙研', 1, '1990-10-09', '通信工程', 42, NULL)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into XSB values('081220', '吴薇华', 0, '1990-03-08', '通信工程', 42, NULL)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into XSB values('081221', '刘燕敏', 0, '1989-11-12', '通信工程', 42, NULL)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into XSB values('081241', '罗林琳', 0, '1989-01-30', '通信工程', 50, NULL)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into KCB values('101', '计算机基础', 1, 80, 5)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into KCB values('102', '程序设计与语言', 2, 68, 4)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into KCB values('206', '离散数学', 4, 68, 4)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into KCB values('208', '数据结构', 5, 68, 4)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into KCB values('210', '计算机原理', 5, 85, 5)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into KCB values('209', '操作系统', 6, 68, 4)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into KCB values('212', '数据库原理', 7, 68, 4)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into KCB values('301', '计算机网络', 7, 51, 3)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into KCB values('302', '软件工程', 7, 51, 3)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081101', '101', 80)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081101', '102', 78)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081101', '206', 59)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081103', '101', 62)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081103', '102', 70)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081103', '206', 81)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081104', '101', 90)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081104', '102', 84)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081104', '206', 65)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081102', '102', 78)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081102', '206', 78)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081106', '101', 65)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081106', '102', 71)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081106', '206', 80)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081107', '101', 78)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081107', '102', 80)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081107', '206', 68)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081108', '101', 85)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081108', '102', 64)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081108', '206', 87)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081109', '101', 66)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081109', '102', 83)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081109', '206', 70)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081110', '101', 95)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081110', '102', 90)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081110', '206', 89)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081111', '101', 91)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081111', '102', 70)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081111', '206', 76)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081113', '101', 63)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081113', '102', 79)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081113', '206', 60)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081201', '101', 80)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081202', '101', 65)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081203', '101', 87)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081204', '101', 91)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081210', '101', 76)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081216', '101', 81)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081218', '101', 70)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081220', '101', 82)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081221', '101', 76)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into CJB values('081241', '101', 90)";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
$sql="insert into userinfo values('php1','123456',1,23,'123456@qq.com'),('php2','654321',0,21,'654321@qq.com')";
$result=mysqli_query($conn, $sql) or die('数据插入失败：'.mysqli_error($conn));
?>
二、获取数据中女学生的总学分信息
<?php
$conn=mysqli_connect('localhost','root','') or die('连接失败');
//mysql_connect() $conn为资源型
mysqli_select_db($conn, 'PXSCJ') or die('选择数据库失败');
//mysql_select_db() 选择数据库
mysqli_query($conn, "SET NAMES utf8mb4");
//mysql_query() 执行MySQL的SQL语句
$sql="select * from XSB where 性别=0";
$result=mysqli_query($conn, $sql);
//执行SQL语句 -- $sql
echo "<table border=1 align='center'>";
echo "<tr><td>学号</td><td>姓名</td><td>总学分</td></tr>";
while($row=mysqli_fetch_row($result)) //从结果集中逐行获取记录
{
//返回的是一个数组
list($XH,$XM,$XB,$CSSJ,$ZY,$ZXF,$BZ)=$row;
//list()将数组中的单元值赋值给变量
echo "<tr><td>$XH</td><td>$XM</td><td>$ZXF</td></tr>";
//以表格形式-输出-学号-姓名-总学分
}
echo "</table>";
?>
三、分页显示—— 一页显示三条记录
<!DOCTYPE html>
<html>
<head>
<title>课程列表页</title>
</head>
<body>
<center> <font size=5 face="华文楷体" color="blue">PHP分页显示</font></center>
<div align="center">
<form action="" method="post">
<?php
$conn = mysqli_connect('localhost', 'root', '') or die('连接失败');
//mysql_connect()链接MySQL服务器
mysqli_select_db($conn, 'pxscj');
//mysql_select_db()选择数据库
mysqli_query($conn, "SET NAMES utf8mb4");
//mysql_query()执行SQL语句
$result = mysqli_query($conn, "select count(*) from kcb order by 课程号"); //统计记录总数
$row =mysqli_fetch_row($result);
//mysql_fetch_row()从结果集中逐行获取记录
$num=$row[0]; //这里只有一行一列--获取记录总数
$page_count=ceil($num/3); //总页数--每页三条记录
if(empty($_GET['page'])) {
$page_curr=1; } //刚打开页面URL没有page参数
else $page_curr=$_GET['page']; //否则获取当前页码
$page = $page_curr;
?>
<table border="1">
<tr><td>课程号</td><td>课程名</td><td>开课学期</td><td>学时</td><td>学分</td><td>编辑</td><td>删除</td></tr>
<?php
$result = mysqli_query($conn, "select * from kcb limit ".(($page_curr - 1)*3).", 3");
//分页--获取当前页的结果集
while($row = mysqli_fetch_row($result)) {
//遍历结果集中的每一行
?>
<tr>
<td><?php echo $row[0];?></td>
<td><?php echo $row[1];?></td> <!-- 以表格形式 输出一条记录 -->
<td><?php echo $row[2];?></td>
<td><?php echo $row[3];?></td>
<td><?php echo $row[4];?></td>
<td><a href="#">编辑</a></td>
<td><a href="#">删除</a></td>
</tr>
<?php } ?>
</table>
<br>
<!-- 页面跳转通过URL传递page数据 -->
<a href="SY8_3_PageCount.php?page=1">首页</a>
<a href="SY8_3_PageCount.php?page=<?php echo $page_curr<=1?1:($page-1) ?>" >上一页</a>
<a href="SY8_3_PageCount.php?page=<?php echo $page_curr>=$page_count?$page_count:($page+1) ?>" >下一页</a>
<a href="SY8_3_PageCount.php?page=<?php echo $page_count ?>" >末页</a>
</form>
</div>
</body>
</html>
四、SQL注入漏洞(攻击)与防范–了解魔术引号 magic_quotes_gpc的设置，了解addslashes( )函数的使用。
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>注入漏洞</title>
</head>
<body>
<center>
<font size=5 face="华文楷体" color="blue">SQL注入漏洞(攻击)与防范</font>
</center>
<div align="center">
<form action="" method="post">
<table width="300" border="1" align="center">
<tr align="center"><td>用户名：</td><td><input type="text" name="username" value=""></td></tr>
<tr align="center"><td>密 码：</td><td><input type="text" name="password" value=""></td></tr>
<tr align="center"><td colspan="2"><input type="submit" name="Submit" value="提交" /></td></tr>
</table>
</form>
<?php
if (isset($_POST['Submit'])) {
$username=addslashes($_POST['username']);
$password=addslashes($_POST['password']);
//magic_quotes_gpc设为off的情况下
$conn = mysqli_connect("localhost", "root", "") or die("连接失败");
//mysql_connect()链接MySQL服务器
mysqli_select_db($conn, 'PXSCJ') or die("选择数据库失败");
//mysql_select_db()选择数据库
mysqli_query($conn, "set names utf8mb4");
//设置字符集为utf8mb4
$str="select * from userinfo where username='$username' and password='$password'";
echo "<br>";
echo $str;
$result = mysqli_query($conn, $str);
//执行SQL语句--$str
?>
<table border="1">
<tr>
<td>用户</td>
<td>密码</td>
<td>性别</td>
<td>年龄</td>
<td>邮箱</td>
</tr>
<?php
while ($row = mysqli_fetch_row($result)) {
//遍历结果集中的每一行
?>
<tr>
<td><?php echo $row[0]; ?></td>
<td><?php echo $row[1]; ?></td>
<!-- //将记录以表格形式输出 -->
<td><?php echo $row[2]; ?></td>
<td><?php echo $row[3]; ?></td>
<td><?php echo $row[4]; ?></td>
</tr>
<?php } ?>
</table>
<?php } ?>
</div>
</body>
</html>
五、使用PHP操作MySQL中的PXSCJ数据库，对课程信息实现查询、增加、删除、修改的功能。建立触发器，如果删除一条记录，则同时删除成绩表中该课程信息。
- 在MySQL客户端中输入一下代码创建触发器：
# mysql -uroot -h127.0.0.1 -p # 连接数据库
# set names utf8mb4; # 设置编码
# use pxscj; # 选择数据库
# 创建触发器
delimiter $$
create trigger kc_delete after delete
on kcb for each row
begin
delete from cjb where 课程号=old.课程号;
end$$
delimiter ;
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>课程信息更新</title>
<style>
.STYLE1 {
font-size: 15px;
font-family: "幼圆";
}
div{
text-align:center;
font-family:"幼圆";
font-size:24px;
font-weight:bold;color:"#008000";
}
table{
width:300px;
}
</style>
</head>
<body>
<div>课程表操作</div>
<form name="frm1" method="post">
<table align="center">
<tr>
<td width="120">
<span class="STYLE1">根据课程号查询:</span>
</td>
<td>
<input type="text" name="KCNumber" id="KCNumber" size="10">
<input type="submit" name="test" class="STYLE1" value="查找">
</td>
</tr>
</table>
</form>
<?php
$conn = mysqli_connect("localhost", "root", "") or die("链接失败");
//连接服务器
mysqli_select_db($conn, "PXSCJ") or die("连接数据失败");
//选择数据库
mysqli_query($conn, "set names utf8mb4");
//设置字符集
$KCNumber=@$_POST['KCNumber'];
//取得用户输入的查询参数值(课程号)
$sql="select * from KCB where 课程号='$KCNumber'";
//按课程号查找课程信息的SQL语句
$result = mysqli_query($conn, $sql);
//执行$sql(SQL语句)
$row = @mysqli_fetch_array($result);
//处理结果集(保存到一个数组$row中-可以用数字也可以用字段名作键名)
if(($KCNumber !== NULL) && (!$row)) {
//判断课程是否存在
echo "<script>alert('没有该课程信息!')</script>";
}
?>
<form name="frm2" method="post">
<table bgcolor="#CCCCCC" border="1" align="center" cellpadding="0" cellspacing="0">
<tr>
<td bgcolor="#CCCCCC" width="90">
<span class="STYLE1">课程号:</span>
</td>
<td>
<input name="KCNum" type="text" class="STYLE1" value="<?php echo $row['课程号']; ?>">
<input name="h_KCNum" type="hidden" value="<?php echo $row['课程号']; ?>">
<!--一个隐藏文本框-->
</td>
</tr>
<tr>
<td bgcolor="#CCCCCC" width="90">
<span class="STYLE1">课程名:</span>
</td>
<td>
<input name="KCName" type="text" class="STYLE1" value="<?php echo $row['课程名']; ?>">
</td>
</tr>
<tr>
<td bgcolor="#CCCCCC">
<span class="STYLE1">开课学期:</span>
</td>
<td>
<input name="KCTerm" type="text" class="STYLE1" value="<?php echo $row['开课学期']; ?>">
</td>
</tr>
<tr>
<td bgcolor="#CCCCCC">
<span class="STYLE1">学时:</span>
</td>
<td>
<input name="KCtime" type="text" class="STYLE1" value="<?php echo $row['学时']?>">
</td>
</tr>
<tr>
<td bgcolor="#CCCCCC">
<span class="STYLE1">学分:</span>
</td>
<td>
<input name="KCCredit" type="text" class="STYLE1" value="<?php echo $row['学分'];?>">
</td>
</tr>
<tr>
<td align="center" colspan="2" bgcolor="#CCCCCC">
<input name="b" type="submit" value="修改" class="STYLE1">
<input name="b" type="submit" value="添加" class="STYLE1" />
<input name="b" type="submit" value="删除" class="STYLE1"> </td>
</tr>
</table>
</form>
</body>
</html>
<?php
$KCH=@$_POST['KCNum']; //课程号
$h_KCH=@$_POST['h_KCNum']; //课程号(表单中隐藏文本框中的课程号--修改课程信息用--课程号不能修改)
$KCM=@$_POST['KCName']; //课程名
$KKXQ=@$_POST['KCTerm']; //开课学期
$XS=@$_POST['KCtime']; //学时
$XF=@$_POST['KCCredit']; //学分
function test($KCH,$KCM,$KKXQ,$XF) //简单的验证函数，验证表单数据的正确性
{
if(!$KCH)
echo "<script>alert('课程号不能为空!');location.href='SY8_5_PHP-MySQL.php';</script>"; //判断课程号是否为空
elseif(!$KCM)
echo "<script>alert('课程名不能为空!');location.href='SY8_5_PHP-MySQL.php';</script>"; //判断课程名是否为空
elseif($KKXQ>8||$KKXQ<1)
echo "<script>alert('开课学期必须为1-8的数字!');location.href='SY8_5_PHP-MySQL.php';</script>"; //判断开课学期是否在1-8之间
elseif(!is_numeric($XF))
echo "<script>alert('学分必须为数字!');location.href='SY8_5_PHP-MySQL.php';</script>"; //判断学分是否为数字
}
if(@$_POST["b"]=='修改')
//单击【修改】按钮
{
test($KCH,$KCM,$KKXQ,$XF);
//检查输入信息
if($KCH != $h_KCH)
//判断用户是否修改了原来的课程号值
echo "<script>alert('课程号与原数据有异，无法修改!');</script>";
//课程号不能修改
else
{
$update_sql="update KCB set 课程名='$KCM',开课学期=$KKXQ,学时=$XS,学分=$XF WHERE 课程号='$KCH'"; //update set
$update_result=mysqli_query($conn, $update_sql);
//mysql_query()
if(mysqli_affected_rows($conn) != 0)
echo "<script>alert('修改成功!');</script>";
//mysql_affected_rows()
else
echo "<script>alert('信息未修改!');</script>";
//获取最后执行的INSERT-UPDATE-DELETE-所影响行数
}
}
if(@$_POST["b"]=='添加')
//单击【添加】按钮
{
test($KCH,$KCM,$KKXQ,$XF);
$s_sql="select 课程号 from KCB where 课程号='$KCH'";
$s_result=mysqli_query($conn, $s_sql);
$s_row=mysqli_fetch_array($s_result);
if($s_row)
//若要添加的课程已经存在则提示
echo "<script>alert('课程已存在，无法添加!');</script>";
else
{
$insert_sql="insert into KCB(课程号,课程名,开课学期,学时,学分) values('$KCH', '$KCM', $KKXQ, $XS, $XF)";
$insert_result=mysqli_query($conn, $insert_sql) or die('添加失败！');
if( mysqli_affected_rows($conn) != 0 )
//判断insert是否成功
echo "<script>alert('添加成功!');</script>";
}
}
if(@$_POST["b"]=='删除')
//单击【删除】按钮
{
if(!$KCH)
echo "<script>alert('请输入要删除的课程号!');</script>";
else
{
$d_sql="select 课程号 from KCB where 课程号='$KCH'";
$d_result=mysqli_query($conn, $d_sql);
$d_row=mysqli_fetch_array($d_result);
if(!$d_row)
//课程如果不存在则提示
echo "<script>alert('课程号不存在，无法删除!');</script>";
else
{
$del_sql="delete from KCB where 课程号='$KCH'";
//删除KCB中课程号为$KCH的记录
$del_result=mysqli_query($conn, $del_sql) or die('删除失败！');
if(mysqli_affected_rows($conn)!=0)
echo "<script>alert('删除课程".$KCH."成功!');</script>";
}
}
}
?>
创作不易，喜欢的话加个关注点个赞，谢谢谢谢
查看2道真题和解析
投递中国石化等公司10个岗位 >