mysql增删改查的实例
2018-05-10 本文已影响0人
新世界的冒险
根据如下mysql数据表增删改查
person信息.png
公共文件common.php
<?php
header("content-type:text/html;charset=utf-8");
//配置信息
$db_host='localhost'; //主机名
$db_port='3306'; //端口号
$db_user='root'; //用户名
$db_pass='123456'; //密码
$db_name='students'; //数据库名称
$charset='utf8'; //字符集
//php连接mysql服务器
$link=@mysql_connect($db_host.':'.$db_port,$db_user,$db_pass);
if(!$link)
die('php数据库连接失败'.mysql_error());
//选择当前数据库
if(!mysql_select_db($db_name))
die('选择数据库失败'.mysql_error());
//设置客户端字符集
mysql_query("set charset {$charset}");
数据表格显示list.php
<?php
//包含连接数据库的公共文件
require_once("./common.php");
//构建查询的sql语句
$sql="select * from person order by id ASC";
//执行sql语句，并返回结果集资源
$result=mysql_query($sql);
//获取记录
$recodes=mysql_num_rows($result);
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>学生信息管理中心</title>
<script type='text/javascript'>
function comfirmDel(id){
//询问是否要删除
if(window.confirm('你真的要删除吗？'))
{
//如果单击'确定'，跳转到delete.php页面
location.href='./delete.php?id='+id;
}
}
</script>
</head>
<body>
<div class="content" style="text-align: center;margin-bottom:20px;">
<h2>学生信息管理中心</h2>
<a href="add.php">添加学生</a>
共有<b style='color:red;'><?php echo $recodes ?></b>个学生
</div>
<table width='600' border='1' bordercolor='#ccc' align='center' cellpadding='5' cellspacing='0'>
<tr bgcolor="#f0f0f0">
<th>编号</th>
<th>姓名</th>
<th>年龄</th>
<th>性别</th>
<th>学历</th>
<th>爱好</th>
<th>工资</th>
<th>奖金</th>
<th>籍贯</th>
<th>操作选项</th>
</tr>
<?php
//循环从结果集中取数据
while($arr = mysql_fetch_array($result,MYSQL_ASSOC)){
?>
<tr text-align='center'>
<td><?php echo $arr['id'] ?></td>
<td><?php echo $arr['name'] ?></td>
<td><?php echo $arr['age'] ?></td>
<td><?php echo $arr['sex'] ?></td>
<td><?php echo $arr['edu'] ?></td>
<td><?php echo $arr['hobby'] ?></td>
<td><?php echo $arr['salary'] ?></td>
<td><?php echo $arr['bonus'] ?></td>
<td><?php echo $arr['city'] ?></td>
<td>
<a href="edit.php?id=<?php echo $arr['id'] ?>">修改</a>
<a href="javascript:void(0);" onclick="comfirmDel(<?php echo $arr['id'] ?>)">删除</a>
</td>
</tr>
<?php } ?>
</table>
</body>
</html>
微信截图_20180510145712.png
增加数据 add.php
<?php
//包含连接数据库的公共文件
require_once("./common.php");
//判断表单是否提交
if(isset($_POST['ac']) && $_POST['ac']=='add')
{
//获取表单提交值
$name =$_POST['name'];
$age =$_POST['age'];
$sex =$_POST['sex'];
$edu =$_POST['edu'];
$hobby =$_POST['hobby'];
$salary =$_POST['salary'];
$bonus =$_POST['bonus'];
$city =$_POST['city'];
//构建插入的sql语句
$sql="insert into person values(null,'$name',$age,'$sex','$edu','$hobby',$salary,$bonus,'$city')";
if(mysql_query($sql))
{
echo "<h2>学生信息添加成功！</h2>";
header("refresh:3;url=./list.php");
die();
}
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>学生信息管理中心</title>
</head>
<body>
<div class="content" style="text-align: center;margin-bottom:20px;">
<h2>添加学生信息</h2>
<a href="javascript:history.go(-1)">返回首页</a>
</div>
<form method="post" action=''>
<table width='400' border='1' bordercolor='#ccc' align="center" cellpadding="5" cellspacing="0">
<tr>
<td width='80' align="right">姓名：</td>
<td><input type="text" name="name"></td>
</tr>
<tr>
<td width='80' align="right">年龄：</td>
<td><input type="text" name="age" size='2' maxlength="2"></td>
</tr>
<tr>
<td width='80' align="right">性别：</td>
<td>
<input type="radio" name="sex" value="1" checked>男
<input type="radio" name="sex" value="2">女
</td>
</tr>
<tr>
<td width='80' align="right">学历：</td>
<td>
<select name='edu'>
<option value='1'>大专</option>
<option value='2'>本科</option>
<option value='3'>研究生</option>
</select>
</td>
</tr>
<tr>
<td width='80' align="right">爱好：</td>
<td>
<input type="checkbox" name="hobby" value="1">读书
<input type="checkbox" name="hobby" value="2">电影
<input type="checkbox" name="hobby" value="3">游戏
</td>
</tr>
<tr>
<td width='80' align="right">工资：</td>
<td><input type="text" name="salary"></td>
</tr>
<tr>
<td width='80' align="right">奖金：</td>
<td><input type="text" name="bonus"></td>
</tr>
<tr>
<td width='80' align="right">籍贯</td>
<td><input type="text" name="city"></td>
</tr>
<tr>
<td></td>
<td>
<input type="submit" value='提交'>
<input type="hidden" name="ac" value="add">
<input type="reset" value='重置'>
</td>
</tr>
</table>
</form>
</body>
</html>
删除数据 delete.php
<?php
//包含连接数据库的公共文件
require_once("./common.php");
//获取地址栏传递的id
$id=$_GET['id'];
//构建删除的sql语句
$sql="delete from person where id=$id";
//判断记录是否删除成功
if(mysql_query($sql))
{
echo "<h2>id={$id}的记录删除成功！</h2>";
header("refresh:3;url=./list.php"); //等待3秒，并跳转
die();
}
**修改数据 edit.php**
<?php
require_once("../common.php");
if(isset($_POST['ac'])&&$_POST['ac']=='edit')
{
//获取表单提交值
$id=$_POST['id'];
$name =$_POST['name'];
$age =$_POST['age'];
$sex =$_POST['sex'];
$edu =$_POST['edu'];
$hobby_arr=array();
$hobby_arr =$_POST["hobby"];
$hobby = implode(',', $hobby_arr);//把数组转换为字符串
$salary =$_POST['salary'];
$bonus =$_POST['bonus'];
$city =$_POST['city'];
$sql="update person set name='$name',age=$age,sex='$sex',edu='$edu',hobby='$hobby',salary=$salary,bonus=$bonus,city='$city' where id={$id}";
if(mysql_query($sql))
{
echo "<h2>学生信息添加成功！</h2>";
header("refresh:3;url=./list.php");
die();
}
}else
{
$id=$_GET['id'];
$sql="select * from person where id=$id";
$result=mysql_query($sql);
$arr=mysql_fetch_array($result,MYSQL_ASSOC);
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>学生信息管理中心</title>
</head>
<body>
<div class="content" style="text-align: center;margin-bottom:20px;">
<h2>修改学生信息</h2>
<a href="javascript:history.go(-1)">返回首页</a>
</div>
<form method="post" action=''>
<table width='400' border='1' bordercolor='#ccc' align="center" cellpadding="5" cellspacing="0">
<tr>
<td width='80' align="right">姓名：</td>
<td><input type="text" name="name" value="<?php echo $arr['name'] ?>"></td>
</tr>
<tr>
<td width='80' align="right">年龄：</td>
<td><input type="text" name="age" value="<?php echo $arr['age'] ?>" size='2' maxlength="2"></td>
</tr>
<tr>
<td width='80' align="right">性别：</td>
<td>
<input type="radio" name="sex" value="1" <?php if($arr['sex']=='男') echo 'checked'; ?>>男
<input type="radio" name="sex" value="2" <?php if($arr['sex']=='女') echo 'checked'; ?>>女
</td>
</tr>
<tr>
<td width='80' align="right">学历：</td>
<td>
<select name='edu'>
<option value='1' <?php if($arr['edu']=='大专') echo 'selected'; ?>>大专</option>
<option value='2' <?php if($arr['edu']=='本科') echo 'selected'; ?>>本科</option>
<option value='3' <?php if($arr['edu']=='研究生') echo 'selected'; ?>>研究生</option>
</select>
</td>
</tr>
<tr>
<td width='80' align="right">爱好：</td>
<td>
<input type="checkbox" name="hobby[]" value="读书">读书
<input type="checkbox" name="hobby[]" value="电影">电影
<input type="checkbox" name="hobby[]" value="游戏" >游戏
</td>
</tr>
<tr>
<td width='80' align="right">工资：</td>
<td><input type="text" name="salary" value="<?php echo $arr['salary'] ?>"></td>
</tr>
<tr>
<td width='80' align="right">奖金：</td>
<td><input type="text" name="bonus" value="<?php echo $arr['bonus'] ?>"></td>
</tr>
<tr>
<td width='80' align="right">籍贯</td>
<td><input type="text" name="city" value="<?php echo $arr['city'] ?>"></td>
</tr>
<tr>
<td></td>
<td>
<input type="submit" value='提交'>
<input type="hidden" name="ac" value="edit">
<input type="hidden" name="id" value="<?php echo $id ?>">
<input type="reset" value='重置'>
</td>
</tr>
</table>
</form>
</body>
</html>
