MENU

PHP操作数据库

• April 16, 2019 • Read: 1410 • Web Program

PHP操作数据

删除数据

语法:delete from tbname where ID=1;

清空表tbname里面ID为1的数据
delete from tbname where pid in(1,2,3);
//删除表tabname里面pid字段为123的数据

两种清空表的差异

表达式作用
delete from tbname清空表数据/保留主键的自增
truncate tbname清空表数据//重置主键的自增

修改数据

语法:updata tb set 字段='XXX',字段='值' where 条件;

修改表tb字段的值

修改朱元璋的性别为女


修改表的字段

1.添加字段

语法:alter table stus add subjects char(30) not null;

给表stus 添加一个字段 subjects

2.修改字段

语法:alter table stus change 原字段 新字段 数据类型 约束;

修改字段

3.删除字段

语法:alter table stus drop 字段

删除 字段

PHP操作数据库

phpinfo();//查看底层环境

数据库的操作步骤

查询操作
mysqli_connect("主机地址","用户名","密码","数据库");//连接数据库
mysqli_set_charset(数据库连接对象,"utf8");//指定操作编码
mysqli_query();//增删改查
mysqli_fetch_all();//解析数据
mysqli_free_result();//释放结果
mysqli_close();//关闭数据库

增删改操作
mysqli_connect("主机地址","用户名","密码","数据库");//连接数据库
mysqli_set_charset(数据库连接对象,"utf8");//指定操作编码
mysqli_query();//增删改查
mysqli_fetch_all();//解析数据
mysql_affected_rows(link_identifier);//查看最后一次影响的行数
mysqli_close();//关闭数据库

实例

<?php
header("Content-type:text/html;charset=utf-8");  
//phpinfo();//查看底层环境
//连接数据库 然后打开xiaohwuei这张表
$link=mysqli_connect("127.0.0.1","root","root","xiaohuwei") or die("数据库连接失败");
//var_dump($link);

//设置编码
mysqli_set_charset($link,"utf8");

//执行语句   查询
$sql = "select * from stus";

$result = mysqli_query($link,$sql);//执行所有的sql语句
//判断返回值
if($result===FALSE){
    exit("sql语句拼写有误,请检查!");
}else { 
    //解析数据
   $arr = mysqli_fetch_all($result,MYSQLI_ASSOC);
   echo "<pre>";
   print_r($arr);
    //var_dump($result);//成功 结果集对象 失败返回false
    echo "</pre>";
    //释放结果集
    mysqli_free_result($result);
    //关闭数据库
    mysqli_close($link);
}

结果

Array
(
    [0] => Array
        (
            [sid] => 1
            [sname] => 张三丰
            [sex] => 男
            [cid] => 1
            [score] => 99
            [subj] => 
        )

    [1] => Array
        (
            [sid] => 2
            [sname] => 敏敏特穆尔
            [sex] => 女
            [cid] => 2
            [score] => 100
            [subj] => 
        )

    [2] => Array
        (
            [sid] => 3
            [sname] => 周芷若
            [sex] => 女
            [cid] => 5
            [score] => 100
            [subj] => 
        )

    [3] => Array
        (
            [sid] => 4
            [sname] => 敏敏
            [sex] => 女
            [cid] => 5
            [score] => 100
            [subj] => 
        )

    [4] => Array
        (
            [sid] => 5
            [sname] => 张无忌
            [sex] => 男
            [cid] => 1
            [score] => 100
            [subj] => 
        )

    [5] => Array
        (
            [sid] => 6
            [sname] => 张翠山
            [sex] => 男
            [cid] => 5
            [score] => 88
            [subj] => 
        )

    [6] => Array
        (
            [sid] => 7
            [sname] => 朱元璋
            [sex] => 女
            [cid] => 10
            [score] => 88
            [subj] => 
        )

    [7] => Array
        (
            [sid] => 8
            [sname] => 张元
            [sex] => 男
            [cid] => 1
            [score] => 88
            [subj] => 
        )

)

补充JQ异步请求

get请求

$.get("url地址?id=1&userName=admin",data,function(res){
    res:服务器响应结果
    data:传递的参数{user: "admin"}   |表单序列化
})

jsonp请求

$.getJSON("url?callback=?",function(res){
    res:服务器响应结果
})

登陆功能

首先创建一张表

mysql>  create table user(
    ->   id int unsigned key auto_increment,
    ->   userName char(20) unique not null,
    ->   nickName char(30) not null,
    ->   pwd char(32) not null,
    ->   regTime timestamp default current_timestamp
    ->    );

html

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>Document</title>
    <script src="https://text.xiaohuwei.cn/weather/jquery-1.12.4.min.js"></script>
</head>

<body>
    <form id="frm">
        用户名:<input type="text" name="userName">
        <br> 密码:
        <input type="text" name="pwd">
        <br>

        <input type="button" id="btn" value="登录">


    </form>
</body>

<script>
    $("#btn").click(function() {
        $.post("1.php", $("#frm").serialize(), function(res) {
            if (res == 1) {
                alert("登陆成功!");

            } else {
                alert("登陆失败!")
            }
        })
    })
</script>

</html>

PHP

<?php

header("Content-type:text/html;charset=utf-8");  

$user = $_POST["userName"];//接受收username
$pwd = $_POST["pwd"];//接受密码
//数据库密码加密过
$pwd = md5($pwd);//把接收的密码md5加密
$link = mysqli_connect("127.0.0.1", "xiaohuwei", "weishao", "xiaohuwei");//连接数据库
mysqli_set_charset($link,"utf8");//设置字符集编码
$sql =  "select * from user where userName='$user' and pwd='$pwd'";//拼接sql语句
$result = mysqli_query($link,$sql);//执行语句
if(is_object($result)){//判断是否为对象
$arr=mysqli_fetch_row($result);//一次解析一条
//释放结果集
// mysqli_free_result($result);登陆页面可要可不要
mysqli_close($link);//关闭数据库
}else {
    exit("sql语句拼写有误");//终止程序
}
if($arr){
    echo "1";//返回给前端

}else{
    echo "2";//同理
}

注册功能

html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>在线钓鱼,愿者上钩。</title>
    <style>
        form{
            color: aliceblue;
            text-align: center;
        }
    body{
        background: orange;
    }
    h1{
        color: aliceblue;
        text-align: center;
    }
    #btn{
        font-size: 20px;
        border:none;
        width: 150px;
        background: #8000801c;
        border-radius: 30px;
        height: 50px;
        color: aliceblue;
        cursor: pointer;
        transition: 0.3s;
    }
    #btn:hover{
        background: #8000804d;
    }
    </style>
    <script src="https://text.xiaohuwei.cn/weather/jquery-1.12.4.min.js"></script>
</head>
<body>
    <h1>在线钓鱼,愿者上钩。</h1>
    <form id="frm">
       尊姓大名:<input type="text" name="nickName">
            <br> <br>
            &nbsp&nbsp&nbsp&nbsp用户名:<input type="text" name="userName">
        <br> <br>
        &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp密码:<input type="password" name="pwd">
        <br> <br> <br>
<input type="button" id="btn" value="提交注册哦~">
    </form>
</body>

<script>
$("#btn").click(function(){
$.post("2.php",$("#frm").serialize(),function(res){//表单序列化
    if(res==1){
        alert("ok!你已经成功上钩!~~~");
        window.location.href="1.html";
    }else{
        alert("注册失败!")
    }
})
})
</script>
</html>

PHP

<?php

header("Content-type:text/html;charset=utf-8");  

$user = $_POST["userName"];//接受收username
$pwd = $_POST["pwd"];//接受密码
$nickName= $_POST["nickName"];
$link = mysqli_connect("127.0.0.1", "xiaohuwei", "weishao", "xiaohuwei");//连接数据库
mysqli_set_charset($link,"utf8");//设置字符集编码
$sql = "insert user(userName,pwd,nickName) values('$user',md5('$pwd'),'$nickName')";//拼接sql语句
$result = mysqli_query($link,$sql);//执行语句
$rows =  mysqli_affected_rows($link);
if($rows > 0){
          
    echo "1";
 }else{
      echo "炸了";
 }

删除功能

admin.php

<?php

header("content-type:text/html;charset=utf-8");
$link = mysqli_connect("127.0.0.1", "baobao", "123456", "baobao");
    mysqli_set_charset($link, "utf8");
    $sql = "select * from user ";
    $result =  mysqli_query($link, $sql); // 执行 sql 语句
    print_r($result);
     if($result === FALSE){
         
        exit("sql语句拼写有误,请检查!");
        
     }else{
        
         $arr = mysqli_fetch_all($result,MYSQLI_ASSOC);
         // 释放结果集 
         mysqli_free_result($result);
         
         // 关闭数据库
         mysqli_close($link);

     }
     
?>

<html>
    <head>
        <title>

            后台管理!
            
        </title>
                    
        <style>
            td{
                text-align:center;
            }
            </style>
    </head>
    <body>
          <table border="1" cellspacing="0"  width="1000" align="center">
              <tr>
        
                  <th>ID</th>
                  <th>姓名</th>
                  <th>用户名</th>
                  <th>密码</th>
                  <th>注册时间</th>
                    <th>操作</th>
              </tr>
              
              <?php 
                 foreach($arr as $v){
               ?>
              <tr>
                    <td><?php echo $v["id"];?></td>
                    <td><?php echo $v["nickName"];?></td>
                  <td><?php echo $v["userName"];?></td>
                  <td><?php echo $v["pwd"];?></td>    
                  <td><?php echo $v["regTime"];?></td>
                  <td><a href="del.php?id=<?php echo $v["id"];?>">删除</a></td>
              </tr>
              <?php
               }
                  ?>
          </table>
    </body>
</html>

del.php

<?php
header("content-type:text/html;charset=utf-8");
   // id 
   
   //print_r($_GET);
   
     $id = $_GET["id"];
     
     // 操作数据库进行删除
     
     $link = mysqli_connect("127.0.0.1", "xiaohuwei", "weishao", "xiaohuwei");
     
     mysqli_set_charset($link,"utf8");

     $sql = "delete from user where id = $id";

       mysqli_query($link, $sql);

      $rows =  mysqli_affected_rows($link);
      
      mysqli_close($link);
      
      
      if($rows > 0){
          
         header("location:admin.php");
      }else{
           header("location:admin.php");
      }

数据库操作常用函数整理

表达式作用
$link=mysqli_connect("地址", "username", "pwd", "tablename");连接数据库
mysqli_set_charset($link,"utf8");设置编码格式
$result=mysqli_query($link, $sql);执行sql语句
$rows = mysqli_affected_rows($link);受影响的行数
mysqli_close($link);断开数据库
mysqli_free_result($result);释放结果集
$arr=mysqli_fetch_row($result);解析结果为数组
$arr = mysqli_fetch_all($result,MYSQLI_ASSOC);解析全部为完整数据
is_object($result)判断是否为数组

在线测试

项目地址

Last Modified: April 18, 2019
Leave a Comment