在php中怎样进行数据库的增删改查
先看下基本的html页面代码
<h3>学生管理</h3>
<button class="btn btn-info" data-toggle="modal" data-target="#addStudentModal">新增</button>
<div>
<input type="text" class=" info" placeholder="Search">
</div>
<button class="search">Submit</button>
<table class="table">
<thead>
<tr>
<th>学生姓名</th>
<th>学生性别</th>
<th>出生年月</th>
<th>班级班号</th>
<th>操作</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
<div class="modal fade" id="addStudentModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel">
<div class="modal-dialog" role="document">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-label="Close"><span
aria-hidden="true">×</span></button>
<h4 class="modal-title" id="myModalLabel">新增学生</h4>
</div>
<div class="media-body">
<form>
<div class="col-md-6 myIpt">
<input id="no" name="no" type="text" class="form-control" placeholder="编号" />
</div>
<div class="col-md-6 myIpt">
<input id="username" name="name" type="text" class="form-control" placeholder="姓名" />
</div>
<div class="col-md-6 myIpt">
<input id="sex" name="sex" type="text" class="form-control" placeholder="性别" />
</div>
<div class="col-md-6 myIpt">
<input id="birthday" name="birthday" type="text" class="form-control" placeholder="出生年月" />
</div>
<div class="col-md-6 myIpt">
<input id="classN" name="class" type="text" class="form-control" placeholder="班级" />
</div>
<div class="col-md-6 myIpt">
<input id="pwd" type="text" name="pwd" class="form-control" placeholder="密码" />
</div>
</form>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-default tijiao" data-dismiss="modal">提交</button>
</div>
</div>
</div>
</div>
公用的php文件
这个文件为func.php
<?php
header("content-type:text/html;charset=utf-8");
//连接数据库 设置字符编码
function conn($host="127.0.0.1",$user="root",$password="root",$database="school"){
$link=mysqli_connect($host, $user, $password, $database);
if($link===FALSE){
exit("数据库连接失败,请检查参数配置是否正确!");
}
mysqli_set_charset($link, "utf8");
return $link;
}
function select($link,$sql){
$result = mysqli_query($link,$sql);
if($result === false){
exit("数据库连接失败,请检查参数配置!");
}
//获取结果并解析
$arr = mysqli_fetch_all($result);
return $arr;//释放结果集并关闭数据库
mysqli_free_result($result);
mysqli_close($link);
}
function add($link,$sql){
$result = mysqli_query($link, $sql);
if($result===false){
echo "ERROR".mysqli_error($link);
exit;
}
//3.获取结果
return mysqli_insert_id($link);//0代表成功
//4.释放结果集,关闭数据库
mysqli_free_result($result);
mysqli_close($link);
}
?>
新增:
在进行新增的时候,首先在html文件上通过ajax进行一个数据的交互,获取到新增的数据,通过ajax交互提交到php后台数据库
//html页面的获取数据及提交到php后台
$(".tijiao").click(function() {
$.ajax({
url: "../../server/addStudent.php",
//data里面的内容为自己新增的数据
data: {
no: $("#no").val(),
username: $("#username").val(),
sex: $("#sex").val(),
birthday: $("#birthday").val(),
classN: $("#classN").val(),
pwd: $("#pwd").val()
},
success: function(res) {
window.open("student.html", "_self");
}
});
});
下面为提交数据结果
<?php
header("content-type:text/html;charset=utf-8");
include "func.php";
//1.连接数据库,设置字符编码
$link = conn();
//2.操作数据库
$no = $_REQUEST["no"];
$name = $_REQUEST["username"];
$sex = $_REQUEST["sex"];
$birthday = $_REQUEST["birthday"];
$class = $_REQUEST["classN"];
$mypwd = $_REQUEST["pwd"];
$query = "insert into student(Sno,Sname,Ssex,Sbrithday,Class,Spwd) values('$no','$name','$sex','$birthday','$class','$mypwd');";
//add函数return出一个结果
echo add($link, $query);
?>
修改
根据特定的项进行修改一条数据,如学生id等
新增和修改的代码部分在html文件上是一样的,不同的只是php文件,这里就给出php文件
<?php
header("content-type:text/html;charset=utf-8");
include "func.php";
$link = conn();
//请求操作数据库
//获取html文件上的输入内容
$no = $_GET["no"];
$name = $_GET["username"];
$sex = $_GET["sex"];
$brithday = $_GET["brithday"];
$class = $_GET["classN"];
$mypwd = $_GET["pwd"];
//sql操作语句,通过Sno进行一个数据的修改
$query = "update student set Sname = '".$name."',Ssex = '".$sex."',Sbrithday = '".$brithday."',Class = '".$class."',Spwd = '".$mypwd."' where Sno = '".$no."'";
$result = mysqli_query($link,$query);
if($result === false){
echo "ERROR".mysqli_error($link);
exit;
}
//获取结果
$row = mysqli_affected_rows($link);
if($row > 0){
echo "修改成功";
}else {
echo "修改失败";
}
?>
数据的查询
查询的话,只需要通过一个条件(可以是ID,姓名,性别等)进行查询,sql语句可以是模糊查询也可以是精确查询,
html页面代码
<div>
<input type="text" class=" info" placeholder="Search">
</div>
<button class="search">Submit</button>
在html页面的ajax交互代码
$(".search").click(function() {
var val = $(".info").val();
console.log(val);
$.ajax({
url: "../../server/serach.php",
type: "get",
data: {
name: $(".info").val(),
},
success: function(res) {
res = JSON.parse(res);
console.log(res);
$("table tbody tr").remove();
var str = '';
str += "<tr><td>" + res[1] + "</td><td>" + res[2] + "</td><td>" + res[3] + "</td><td>" + res[4] + "</td><td><button class = 'btn btn-info studentEditBtn' mark = '" + res[0] + "'>编辑</button><button class = 'btn btn-danger del' mark = '" + res[0] + "'>删除</button></td></tr>"
$("tbody").html(str);
}
});
});
删除数据
删除数据可以直接点击页面上的删除按钮进行删除整条数据
$(".del").click(function() {
//可以在点击的时候获取到当前数据的id,通过该id来进行数据删除
//将其id设置通过localStorage.setItem保存
localStorage.setItem("sno", $(this).attr("mark"));
console.log(localStorage.getItem("sno"));
$.ajax({
url: "../../server/del.php",
data: {
//获取localStorage.getItem数据
no: localStorage.getItem("sno"),
},
success: function() {
window.open("student.html", "_self");
}
})
})
php中的删除文件
<?php
header("content-type:text/html;charset=utf-8");
include "func.php";
$link = conn();
//请求操作数据库
$no = $_GET["no"];
//通过Sno = '".$no."'来进行数据删除
$query = "delete from student where Sno = '".$no."'";
$result = mysqli_query($link,$query);
if($result === false){
echo "ERROR".mysqli_error($link);
exit;
}
//获取结果
$row = mysqli_affected_rows($link);
if($row > 0){
echo "删除成功";
}else {
echo "删除失败";
}
?>