PHP连接数据库,通过接受post请求实现增删改查

1.html文件:

<!DOCTYPE html>
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <meta http-equiv="Cache-Control" content="no-cache">
    <title>PHP连接数据库实现增删改查</title>
</head>
<body>
<div>
    <input type="text" name="daiHao" placeholder="代号">
    <input type="text" name="keMu" placeholder="科目">
    <input type="text" name="jieShu" placeholder="节数">
    <button class="tianJia">添加</button>
    <button class="shanChu">删除</button>
    <button class="xiuGai">修改</button>
    <button class="chaZhao">查找</button>
</div>
<p>提示:添加可以填3个,删除只填代号,修改可以填科目和节数然后需要改哪个填代号,查询填科目和节数。</p>
<table>
    <tbody>
        <!-- <tr>
            <th>代号</th>
            <th>科目</th>
            <th>节数</th>
        </tr> -->
    </tbody>
</table>
<script src="https://libs.baidu.com/jquery/1.11.3/jquery.min.js"></script>
<script>
    function teVal(){
        daiHao = $('[name="daiHao"]').val();
        keMu = $('[name="keMu"]').val();
        jieShu = $('[name="jieShu"]').val();
        arr = '<tr><th>代号</th><th>科目</th><th>节数</th></tr>';
    }
    $.post('1.php',{a: 'synr'},function(ret){
        teVal();
        // console.log(ret);
        if(ret[0].code == '200'){
            // alert(ret[0].msg);
            for(var i = 1;i < ret.length;i++){
                arr += '<tr><td>'+ret[i].c_id+'</td><td>'+ret[i].c_name+'</td><td>'+ret[i].t_id+'</td></tr>';
            }
            $('tbody').html(arr);
        }else{
            alert(ret[0].msg);
        }
    })
    //添加
    $('.tianJia').on('click',function(){
        teVal();
        if(keMu == '' && jieShu == ''){
            alert('请输入你要添加的内容!');
        }else if(daiHao == ''){
            alert('请输入你要添加的内容!');
        }else{
            $.post('1.php',{a: 'tianJia',c_id: daiHao,c_name: keMu,t_id: jieShu},function(ret){
                // console.log(ret);
                if(ret.code == '200'){
                    alert(ret.msg);
                    location.reload();
                }else{
                    alert(ret.msg);
                }
            })
        }
    })
    //删除
    $('.shanChu').on('click',function(){
        teVal();
        if(daiHao != ''){
            var res = confirm('是否删除?');
            if(res){
                $.post('1.php',{a: 'shanChu',c_id: daiHao},function(ret){
                    if(ret.code == '200'){
                        alert(ret.msg);
                        location.reload();
                    }else{
                        alert(ret.msg);
                    }
                })
            }
        }else{
            alert('请输入你要删除的代号!');
        }
    })
    //修改
    $('.xiuGai').on('click',function(){
        teVal();
        if(keMu != '' || jieShu != ''){
            var res = confirm('是否修改?');
            if(res){
                $.post('1.php',{a: 'xiuGai',c_id: daiHao,c_name: keMu,t_id: jieShu},function(ret){
                    // console.log(ret);
                    if(ret.code == '200'){
                        alert(ret.msg);
                        location.reload();
                    }else{
                        alert(ret.msg);
                    }
                })
            }
        }else{
            alert('请输入你要修改的内容!');
        }
    })
    //查找
    $('.chaZhao').on('click',function(){
        teVal();
        if(keMu != '' || jieShu != ''){
            $.post('1.php',{a: 'chaZhao',c_id: daiHao,c_name: keMu,t_id: jieShu},function(ret){
                // console.log(ret)
                if(ret[0].code == '200'){
                    // alert(ret[0].msg);
                    for(var i = 1;i < ret.length;i++){
                        arr += '<tr><td>'+ret[i].c_id+'</td><td>'+ret[i].c_name+'</td><td>'+ret[i].t_id+'</td></tr>';
                    }
                    $('tbody').html(arr);
                }else{
                    alert(ret[0].msg);
                }
            })
        }
    })
</script>
</body>
</html>

1.php文件:(where需要满多个条件才可以找到,可以用and分开,这里我最多用了两个条件!)

<?php
$conn = mysqli_connect('localhost','cs','123456','cs','3306');//主机名或 IP 地址,MySQL用户名,MySQL密码,规定默认使用的数据库,MySQL服务器的端口号,规定 socket 或要使用的已命名 pipe
if (!$conn) { 
    die("连接错误: " . mysqli_connect_error()); 
} 
// var_dump($conn);
mysqli_query($conn,"set names utf8");//设置数据库编码为utf8
header('content-type: application/json;charset=utf-8');//输出为json格式,并设置编码为utf-8

//判断请求的域名
function getTopHost($url){
 $url = strtolower($url);  //首先转成小写
 $hosts = parse_url($url);
 $host = $hosts['host'];
 //查看是几级域名
  $data = explode('.', $host);
  $n = count($data);
  //判断是否是双后缀
  $preg = '/[\w].+\.(com|net|org|gov|edu)\.cn$/';
  /*if(($n > 2) && preg_match($preg,$host)){
   //双后缀取后3位
   $host = $data[$n-3].'.'.$data[$n-2].'.'.$data[$n-1];
  }else if(($n > 1) && preg_match($preg,$host)){
   //非双后缀取后两位
   $host = $data[$n-2].'.'.$data[$n-1];
  }*/
  if($n > 3 && preg_match($preg,$host)){
   //取后4位
   $host = $data[$n-4].'.'.$data[$n-3].'.'.$data[$n-2].'.'.$data[$n-1];
  }else if($n > 2){
    //取后3位
    $host = $data[$n-3].'.'.$data[$n-2].'.'.$data[$n-1];
  }
  return $host;
}
$strUrl = isset($_SERVER['HTTP_REFERER']) ? trim($_SERVER['HTTP_REFERER']) : '';
if($strUrl != ''){
    $strUrl = getTopHost($strUrl);
    // echo $strUrl;
    $all_Array = array('localhost','www.zzwll.cn');//允许请求的域名,多个域名用,分开
    $isContains = in_array($strUrl,$all_Array);
    if(!$isContains){
        echo json_encode(array('code' => '401', 'msg' => '禁止请求'));exit();
    }
}

//全局
$c_id = isset($_POST['c_id']) ? trim($_POST['c_id']) : '';
$c_name = isset($_POST['c_name']) ? trim($_POST['c_name']) : '';
$t_id = isset($_POST['t_id']) ? trim($_POST['t_id']) : '';

//添加
function tianJia(){
    $conn = $GLOBALS['conn'];//引用全局作用域中可用的全部变量
    $c_id = $GLOBALS['c_id'];
    $c_name = $GLOBALS['c_name'];
    $t_id = $GLOBALS['t_id'];
    $sql = "insert into course (c_id,c_name,t_id) values ('$c_id','$c_name','$t_id')";//添加
    $result = mysqli_query($conn,$sql);
    if($result){
        return json_encode(array('code' => '200','msg' => '添加成功'));
    }else{
        return json_encode(array('code' => '400','msg' => '添加失败'));
    }
}

//删除
function shanChu(){
    $conn = $GLOBALS['conn'];
    $c_id = $GLOBALS['c_id'];
    $sql = "delete from course where c_id='$c_id'";//删除
    $result = mysqli_query($conn,$sql);
    if($result){
        return json_encode(array('code' => '200','msg' => '删除成功'));
    }else{
        return json_encode(array('code' => '400','msg' => '删除失败'));
    }
}

//修改
function xiuGai(){
    $conn = $GLOBALS['conn'];
    $c_id = $GLOBALS['c_id'];
    $c_name = $GLOBALS['c_name'];
    $t_id = $GLOBALS['t_id'];
    if($c_name != ''){
        $s = "c_name='$c_name'";
    }else if($t_id != ''){
        $s = "t_id='$t_id'";
    }else if($c_name != '' && $t_id != ''){
        $s = "c_name='$c_name',t_id='$t_id'";
    }
    $sql = "update course set $s where c_id='$c_id'";//修改
    $result = mysqli_query($conn,$sql);
    if($result){
        return json_encode(array('code' => '200','msg' => '修改成功'));
    }else{
        return json_encode(array('code' => '400','msg' => '修改失败'));
    }
}

//查找
function chaZhao(){
    $conn = $GLOBALS['conn'];
    $c_id = $GLOBALS['c_id'];
    $c_name = $GLOBALS['c_name'];
    $t_id = $GLOBALS['t_id'];
    if($c_name != ''){
        $s = "c_name='$c_name'";
    }else if($t_id != ''){
        $s = "t_id='$t_id'";
    }else if($c_name != '' && $t_id != ''){
        $s = "c_name='$c_name' and t_id='$t_id'";
    }
    $sql = "select * from course where $s";//查找
    $result = mysqli_query($conn,$sql);
    $arr = array();//建一个空数组
    $arr[] = array('code' => '200','msg' => '查找成功');//赋值到空数组里
    while ($row = mysqli_fetch_assoc($result)) {//通过while循环获取
        $arr[] = $row;
    }
    if($result){
        return json_encode($arr);
    }else{
        return json_encode(array('code' => '400','msg' => '查找失败'));
    }
}

//所有内容
function synr(){
    $conn = $GLOBALS['conn'];
    $sql = 'select * from course';
    $result = mysqli_query($conn,$sql);
    $arr = array();
    $arr[] = array('code' => '200','msg' => '获取成功');
    while ($row = mysqli_fetch_assoc($result)) {//通过while循环获取
        $arr[] = $row;
    }
    // print_r($arr);
    if($result){
        return json_encode($arr);
    }else{
        return json_encode(array('code' => '400','msg' => '获取失败'));
    }
}

//转换成数组
// $result = mysqli_query($conn,$sql);//对数据库执行一次查询
// $row = mysqli_fetch_array($result,MYSQLI_ASSOC);//函数从结果集中取得一行作为关联数组,或数字数组。 MYSQLI_ASSOC,MYSQLI_NUM,MYSQLI_BOTH(默认)
// print_r($row);
//mysqli_fetch_assoc($result);//关联数组
//mysqli_fetch_row($result);//数字数组

//错误提示
function bc(){
return json_encode(array('code' => '400', 'msg' => '操作失败'));
}

$a = isset($_POST['a']) ? trim($_POST['a']) : '';
switch ($a){
    case "tianJia": // 添加
        echo tianJia();
        break;
    case "shanChu": // 删除
        echo shanChu();
        break;
    case "xiuGai": // 修改
        echo xiuGai();
        break;    
    case "chaZhao": // 查找
        echo chaZhao();
        break;
    case "synr":
        echo synr();
        break;
    default:
        echo bc();
        mysqli_close($conn);//关闭先前打开的数据库连接
}        
?>

数据库表下载地址:https://zhizun.lanzoux.com/iuicqiiigxi

经验jsPHPajaxJQuerypost$_POSTmysqli_querymysqli_fetch_array$GLOBALSjson_encodemysqli_connect

我来吐槽

*

*