mysqli小案例
本章通过学习mysqli的操作，封装了mysqli的公共方法，并通过方法实现增删改查，代码如下：
mysqli.php
<?php
//数据库访问链接
$db = array(
'charset' => 'utf8',
'port' => 3306,
'type' => 'mysql',
'host' => '127.0.0.1',
'user' => 'root',
'pass' => 'root',
'name' => 'test'
);
//调用的函数方法
$eidtName=isset($_GET["edit"])?$_GET["edit"]:"";
if($eidtName=="")
{
return;
}
//获取提交过来的参数
$arr=is_array($_POST)?$_POST:[];
if(count($arr)>0)
{
//调用具体的方法并返回
if(function_exists($eidtName))
{
return $eidtName($arr);
}
}
//连接方法
function connect($db){
# 连接数据库
$conn = @mysqli_connect($db['host'],$db['user'],$db['pass'],$db['name'],'3306');
if(!$conn){
# 连接错误，抛出异常
exit('数据库报错: '.mysqli_connect_error());
}
return $conn;
}
//统计数量
function count_number($db,$table,$where){
$sql = "SELECT COUNT(*) AS count_number FROM ".$table." WHERE ".$where;
$return = mysqli_query($db,$sql);
$return = mysqli_fetch_assoc($return);
# 关闭数据库
mysqli_close($db);
return $return['count_number'];
}
//查询指定条件的单个数据
function find_one($db,$table,$filed,$where){
$sql = "SELECT ".$filed." FROM ".$table.' WHERE '.$where.' LIMIT 1';
$return = mysqli_query($db,$sql);
$return = mysqli_fetch_assoc($return);
# 关闭数据库
mysqli_close($db);
return $return;
}
//根据排序类型查询单条数据
function find($db,$table,$where,$order){
$sql = "SELECT * FROM ".$table.' WHERE '.$where;
if($order){
$sql .= ' ORDER BY '.$order;
}
$sql .= 'LIMIT 1';
$return = mysqli_query($db,$sql);
$return = mysqli_fetch_assoc($return);
# 关闭数据库
mysqli_close($db);
return $return;
}
//查询所有数据
function select($db,$table,$where,$order,$limit){
$sql = "SELECT * FROM ".$table.' WHERE '.$where;
if($order){
$sql .= ' ORDER BY '.$order;
}
if($limit){
$sql .= ' LIMIT '.$limit;
}
$return = mysqli_query($db,$sql);
if($return){
while ( $row = mysqli_fetch_assoc($return)) {
$rows[] = $row;
}
mysqli_free_result($return);
}
# 关闭数据库
mysqli_close($db);
return $rows;
}
//获取演员
function getYanYuan($uid)
{
global $db;
$con=connect($db);
$res=find_one($con,'user','`name`,`phone`,`weight`,`height`','`uid`='.$uid['uid']);
echo json_encode(array('info'=>$res));
}
//插入演员
function insertYanYuan($arr)
{
global $db;
$res=false;
$sql="INSERT INTO `user` SET ";
if(is_array($arr))
{
$arr['add_time']=time();
foreach ($arr as $key=>$value)
{
if($key!='uid')
{
$sql .= "`" . $key . "`='" . $value . "',";
}
}
//去掉最后的,并增加;
$sql=rtrim($sql,',').";";
if(execSQL($db,$sql,'insert'))
{
$res=true;
}
else
{
$res=false;
}
}
else
{
$res=false;
}
if($res){
echo json_encode(array('code'=>1,'text'=>'添加成功！'));
}else{
echo json_encode(array('code'=>0,'text'=>'添加失败！'));
}
}
//更新演员
function updateYanYuan($arr)
{
global $db;
$res=false;
$sql="UPDATE `user` SET ";
if(is_array($arr))
{
$arr['add_time']=time();
$where='';
foreach ($arr as $key=>$value)
{
if($key=='uid')
{
$where=' where uid='.$value;
continue;
}
$sql.="`".$key."`='".$value."',";
}
//去掉最后的
$sql=rtrim($sql,',');
$sql.=$where.';';
if(execSQL($db,$sql,'update'))
{
$res=true;
}
else
{
$res=false;
}
}
else
{
$res=false;
}
if($res){
echo json_encode(array('code'=>1,'text'=>'修改成功！'));
}else{
echo json_encode(array('code'=>0,'text'=>'修改失败！'));
}
}
//删除演员
function deleteYanYuan($arr)
{
global $db;
$res=false;
$sql="DELETE FROM `user` WHERE `uid` in (".$arr['uid'].")";
if(execSQL($db,$sql,'delete'))
{
$res=true;
}
else
{
$res=false;
}
if($res){
echo json_encode(array('code'=>1,'text'=>'删除成功！'));
}else{
echo json_encode(array('code'=>0,'text'=>'删除失败！'));
}
}
//
/**
* @param $db
* @param $sql
* @param $method
* @return bool|int|mysqli_result|string
*/
function execSQL($db,$sql,$method)
{
$con=connect($db);
//执行操作 增、删、改 操作
$res=mysqli_query($con,$sql);
//如果是插入操作，则替换为返回所受影响的函数
if(strtolower($method)=="insert")
{
//执行成功才获取所受影响的函数
if($res){
$res = mysqli_insert_id($con);
}
}
//关闭数据库
mysqli_close($con);
return $res;
}
?>indexPage.php
<?php
require_once 'mysqli.php';
$con=connect($db);
$rows=select($con,'user','1=1','uid',20);
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>日本女演员表</title>
<link rel="stylesheet" href="css/bootstrap.min.css">
<script src="static/js/jquery.js"></script>
<script src="static/js/bootstrap.min.js"></script>
<style type="text/css">
h2
{
color: #666;
}
#tab
{
text-align: center;
}
#tab th,#tab td,#tab caption,#divTitle
{
text-align: center;
padding: 8px;
}
#divBtn
{
text-align: left;
width: 80%;
margin: 0px auto;
}
</style>
</head>
<body>
<?php if (count($rows) > 0) :?>
<div id="divTitle"><h2>日本女演员表</h2></div>
<div id="divBtn">
<button type="button" class="btn btn-primary btn-lg" data-toggle="modal" data-target="#exampleModal" data-whatever="add">添加</button>
<button type="button" class="btn btn-success btn-lg" data-toggle="modal" data-target="#exampleModal" data-whatever="update" id="editYanYuan">修改</button>
<button type="button" class="btn btn-danger btn-lg" id="delYanYuan">删除</button>
</div>
<table id="tab" border="1" cellspacing="0" align="center" width="80%">
<caption></caption>
<tr bgcolor="#add8e6">
<th><input type="checkbox" id="mulSelect"/></th>
<th>ID</th>
<th>姓名</th>
<th>手机号</th>
<th>胸围</th>
<th>身高</th>
<th>添加时间</th>
</tr>
<?php foreach ($rows as $row) : ?>
<tr>
<td><input type="checkbox" name="check<?php echo $row['uid'] ?>" value="<?php echo $row['uid'] ?>"/></td>
<td><?php echo $row['uid'] ?></td>
<td><?php echo $row['name'] ?></td>
<td><?php echo $row['phone'] ?></td>
<td><?php echo $row['weight'] ?></td>
<td><?php echo $row['height'] ?></td>
<td><?php echo date('Y-m-d H:i:s',$row['add_time']) ?></td>
</tr>
<?php endforeach; ?>
</table>
<?php else :?>
<h2 style="color:red">未能加载数据</h2>
<?php endif; ?>
<!-- 模态框-->
<div class="modal fade" id="exampleModal" tabindex="-1" role="dialog" aria-labelledby="exampleModalLabel">
<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="exampleModalLabel"></h4>
</div>
<div class="modal-body">
<form>
<div class="form-group" style="display: none">
<label for="userID" class="control-label">ID:</label>
<input type="text" class="form-control" id="userID">
</div>
<div class="form-group">
<label for="userName" class="control-label">姓名:</label>
<input type="text" class="form-control" id="userName">
</div>
<div class="form-group">
<label for="userPhone" class="control-label">手机号:</label>
<input type="text" class="form-control" id="userPhone">
</div>
<div class="form-group">
<label for="userWeight" class="control-label">胸围:</label>
<input type="text" class="form-control" id="userWeight">
</div>
<div class="form-group">
<label for="userHeight" class="control-label">身高:</label>
<input type="text" class="form-control" id="userHeight">
</div>
</form>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-default" data-dismiss="modal">关闭</button>
<button type="button" class="btn btn-primary" id="sureID"></button>
</div>
</div>
</div>
</div>
</body>
</html>
<script>
$(function(){
// 全选按钮的勾选和取消
$("#mulSelect").on('click', function() {
$("input:checkbox").prop("checked", $(this).prop('checked'));
})
// 全选后，如果子选项有一个取消勾选，则去除全选
$("input:checkbox").on('click', function() {
//当选中的长度等于checkbox的长度的时候,就让控制全选反选的checkbox设置为选中,否则就为未选中
if($("input:checkbox").length === $("input:checked").length) {
$("#mulSelect").prop("checked", true);
} else {
$("#mulSelect").prop("checked", false);
}
})
$("#editYanYuan").on('click', function() {
if($('input:checked').length==0)
{
alert("请选择需要修改的演员");
return;
}
if($('input:checked').length>1)
{
alert("每次只能修改一个演员");
return;
}
})
$("#delYanYuan").on('click', function() {
if($('input:checked').length == 0)
{
alert("请勾选需要删除的数据");
return;
}
var uid='';
$.each($('input:checkbox:checked'), function () {
uid += $(this).val()+",";
});
uid=uid.substring(0,uid.length-1);
$.ajax({
type : "post",
url : "mysqli.php?edit=deleteYanYuan",
dataType : "json",
data:{
uid : uid
},
success : function(result){
alert(result.text);
location.reload();
},
error:function(XMLHttpRequest){
alert(XMLHttpRequest.status);
// alert(XMLHttpRequest.readyState);
alert('添加失败，请重试！');
}
})
})
// 模态框加载事件
$('#exampleModal').on('show.bs.modal', function (event) {
var button = $(event.relatedTarget);
var recipient = button.data('whatever'); //存放点击按钮是添加还是修改
//不符合条件则直接关闭
if(recipient!="add")
{
if ($('input:checked').length == 0 || $('input:checked').length > 1) {
$(this).close();
}
}
var modal = $(this);
var type=(recipient=="add"?"添加":"修改");
modal.find('.modal-title').text(type+"日本女演员"); //修改模态框表头
// modal.find('.modal-body input').val(recipient);
modal.find('.modal-footer button[class="btn btn-primary"]').html(type);
if(recipient!="add")
{
var uid = "0";
$.each($('input:checkbox:checked'), function () {
uid = $(this).val();
});
$.ajax({
type: "post",
url: "mysqli.php?edit=getYanYuan",
dataType: "json",
data: {
uid: uid
},
success: function (result) {
var info = result.info;
$("#userName").val(info.name);
$("#userPhone").val(info.phone);
$("#userWeight").val(info.weight);
$("#userHeight").val(info.height);
$("#userID").val(uid);
},
error: function (XMLHttpRequest) {
//alert(XMLHttpRequest.status);
// alert(XMLHttpRequest.readyState);
alert('添加失败，请重试！');
}
})
}
})
//模态框确认按钮点击事件
$("#sureID").on('click', function() {
var edit=$(this).html();
var url="";
if(edit=="添加")
{
url = "mysqli.php?edit=insertYanYuan";
}
if(edit=="修改")
{
url="mysqli.php?edit=updateYanYuan";
}
$.ajax({
type : "post",
url : url,
dataType : "json",
data:{
name : $("#userName").val(),
phone : $("#userPhone").val(),
weight : $("#userWeight").val(),
height: $("#userHeight").val(),
uid:$("#userID").val()
},
success : function(result){
alert(result.text);
location.reload();
},
error:function(XMLHttpRequest){
alert(XMLHttpRequest.status);
// alert(XMLHttpRequest.readyState);
alert('添加失败，请重试！');
}
})
});
})
</script>效果图：
