php 数据库操作mysqli

程序员文章站 2022-05-16 21:44:27

...

学习总结

mysqli连接的时候不需要type参数
mysqli执行时只需要调用query()方法，返回个结果集
但是如果使用mysqli连接数据库时，如果更换数据库引擎，则所有的方法都得进行更改。

1.数据库连接和增删改查类DBconn.php


<?php

namespace compotents\conn
{
    use Exception;
    use mysqli;
    class DBconn
    {
        private $config = [];
        protected $dbConn;
        public function __construct($dbName = 'db_phpstudy',$userName = 'root',$passWord ='root')
        {
            $this ->config['type'] = 'mysql';
            $this ->config['host'] = 'localhost';
            $this ->config['dbName'] = $dbName;
            $this ->config['userName'] = $userName;
            $this ->config['passWord'] = $passWord;
            $this ->config['charSet'] = 'utf8';
            $this ->config['port'] = '3306';
            $this ->connect();

        }
        public function connect()
        {
            //拆分数组，键名当做变量名，值当做变量的值,拆分成数据库连接的变量
           extract($this->config,EXTR_PREFIX_SAME,'config');
           try
           {
               //1.创建一个mysqli的数据库连接
               $this->dbConn = new mysqli($host,$userName,$passWord,$dbName);
               //2. 判断是否连接成功?
                if ($this->dbConn->connect_errno) echo $this->dbConn->connect_error;
               //3.设置数据库连接的字符集
               $this->dbConn->set_charset($charSet);
           }
           catch(Exception $e)
           {
                die($e->getMessage());
           }
        }

        //查询返回查询结果集
        public function select($table,$where)
        {
            if ($where === '*'):
                $sql = "SELECT * FROM `$table`";
            else:
                $sql = "SELECT * FROM `$table` WHERE $where";
            endif;

            $info = $this ->dbConn->query($sql);
            $records = $info->fetch_all(MYSQLI_ASSOC);
            return $records;
        }
        //插入记录，输出是否成功添加记录
        public function insert($table,$insData)
        {
            //把传入的添加数据的数组转换为一个SQL添加字符串
            $insertSet = $this->toSqlStr($insData);

            $sql = "INSERT `$table` SET $insertSet";
            $flag =  $this->dbConn->query($sql);
            $rowCount = $this->dbConn->affected_rows;//返回受影响的记录数
            if ($flag) {
                //$this->dbConn->insert_id返回最后一条插入语句的自增id
                if ($rowCount > 0) {
                    echo '成功添加了 ' . $rowCount . ' 条记录, 新增记录主键ID: ' . $this->dbConn->insert_id;
                } else {
                    echo '没有添加新记录';
                }
            } else {
                //$this->dbConn->errno返回最近函数调用的错误代码
                //$this->dbConn->error返回最后一次调用的错误信息
                die('添加失败'. $this->dbConn->errno . ' : ' . $this->dbConn->error);
            }
        }
        //更新记录，输出更新几条记录
        public function update($table,$data,$where)
        {
            //把传入的添加数据的数组转换为一个SQL添加字符串
            $updateSet = $this->toSqlStr($data);   
            $sql = "UPDATE `$table` SET $updateSet WHERE $where";
            $flag =  $this->dbConn->query($sql);
            $rowCount = $this->dbConn->affected_rows;//返回受影响的记录数
            if ($flag) {
                //$this->dbConn->insert_id返回最后一条插入语句的自增id
                if ($rowCount > 0) {
                    echo '成功更新了 ' . $rowCount . ' 条记录';
                } else {
                    echo '没有更新记录';
                }
            } else {
                //$this->dbConn->errno返回最近函数调用的错误代码
                //$this->dbConn->error返回最后一次调用的错误信息
                die('更新失败'. $this->dbConn->errno . ' : ' . $this->dbConn->error);
            }

        }
        //删除记录，输出是否删除成功
        public function delete($table,$where)
        {
            $sql = "DELETE FROM $table WHERE $where";
            $flag =  $this->dbConn->query($sql);
            $rowCount = $this->dbConn->affected_rows;//返回受影响的记录数
            if ($flag) {
                //$this->dbConn->insert_id返回最后一条插入语句的自增id
                if ($rowCount > 0) {
                    echo '成功删除了 ' . $rowCount . ' 条记录';
                } else {
                    echo '没有删除记录';
                }
            } else {
                //$this->dbConn->errno返回最近函数调用的错误代码
                //$this->dbConn->error返回最后一次调用的错误信息
                die('删除失败'. $this->dbConn->errno . ' : ' . $this->dbConn->error);
            }
        }
        public function toSqlStr($arr):string
        {
            //把数组的键提取到一个数组中
            $keys = array_keys($arr);
            //把数组的值提取到一个数组中
            $value = array_values($arr);

            $con = count($keys);
            $sqlStr ='';
            for ($i=0;$i<$con;$i++):
                if($i===$con-1):
                    $sqlStr .= " `$keys[$i]`='$value[$i]'";
                else:
                    $sqlStr .= " `$keys[$i]`='$value[$i]' ,";
                endif;   
            endfor; 
            return $sqlStr;
        }
    }
}
?>

2.后台首页index.html



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

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link rel="stylesheet" href="style/style.css">
    <title>商品后台管理</title>
</head>

<body>
    <div class="container">
        <div class="row">
            <div class="header col-12">
                <span>商品后台管理</span>
                <div><a href="">返回首页</a> </div>
                <div><a href="">退出</a></div>
            </div>
        </div>
        <div class="row">
            <div class="aside col-3">
                <div><a href="select.php" target="main"><span>查询商品</span> </a></div>
                <div><a href="insert.php" target="main"><span>添加商品</span> </a></div>
                <div><a href="update.php" target="main"><span>修改商品</span> </a></div>
                <div><a href="delete.php" target="main"><span>删除商品</span> </a></div>

            </div>
            <div class="main col-9">
                <iframe src="select.php" frameborder="0" name="main" width="680px" height="510px"></iframe>
            </div>
        </div>
        <div class="row">
            <div class="footer col-12">
                <div>联系邮箱：<a href="mailto:573661083@qq.com">573661083@qq.com</a></div>
                <div>联系电话：<a href="tel:15010046927">1501004xxxx</a></div>
                <div>Copyright 1998 - 2020 Tencent. All Rights Reserved</div>
            </div>
        </div>

    </div>
</body>

</html>

3.首页样式表 style.css



@import "reset.css";

/* 整页布局 */
.container {
  max-width: 920px;
  min-height: 650px;
  margin-left: auto;
  margin-right: auto;
  background-color: white;
  display: grid;
  gap: 5px;
}
/* 整页中的每行分成12列 */
.container > .row {
  display: grid;
  grid-template-columns: repeat(12, 1fr);
  gap: 5px;
}
/* 头部布局 */
.container > .row > .header {
  margin-top: 5px;
  background-color: #58c4f2;
  max-height: 56px;
  border-radius: 3px;
  padding: 0px 10px;
  display: flex;
  flex-flow: row nowrap;
  align-items: center;
}
.container > .row > .header > span {
  letter-spacing: 2px;
  margin-left: 20px;
  font-size: 1.5rem;
  font-weight: bolder;
}
.container > .row > .header > div > a {
  margin: 0px 10px;
}
.container > .row > .header > div > a:hover {
  color: lightgreen;
}
.container > .row > .header > div:nth-of-type(1) {
  margin-left: auto;
}

/* 侧边栏  */
.container > .row > .aside {
  min-height: 500px;
  background-color: #ccc;
  border-radius: 3px;
  display: flex;
  flex-flow: column nowrap;
  padding: 5px;
}
/* 侧边栏导航 */
.container > .row > .aside > div {
  background-color: #58c4f2;
  height: 40px;
  margin: 2px 8px;
  border-radius: 10px;
}
.container > .row > .aside > div > a {
  width: 100%;
  height: 100%;
  display: flex;
  justify-content: center;
  align-items: center;
}
.container > .row > .aside > div > a > span {
  font-size: 1.2rem;
  letter-spacing: 2px;
}

.container > .row > .aside > div:hover {
  background-color: lightgreen;
  box-shadow: 0 0 5px #555;
}
/* 主体内容显示区 */
.container > .row > .main {
  min-height: 500px;
}
/* 页脚 */
.container > .row > .footer {
  max-height: 80px;
  margin-bottom: 5px;
  background-color: #58c4f2;
  border-radius: 3px;
  display: flex;
  flex-flow: column nowrap;
  justify-content: center;
  align-items: center;
}
.container > .row > .footer > div > a:hover {
  color: lightgreen;
}
/* 12列栅格布局 */
.col-1 {
  grid-column-end: span 1;
}
.col-2 {
  grid-column-end: span 2;
}
.col-3 {
  grid-column-end: span 3;
}
.col-4 {
  grid-column-end: span 4;
}
.col-5 {
  grid-column-end: span 5;
}
.col-6 {
  grid-column-end: span 6;
}
.col-7 {
  grid-column-end: span 7;
}
.col-8 {
  grid-column-end: span 8;
}
.col-9 {
  grid-column-end: span 9;
}
.col-10 {
  grid-column-end: span 10;
}
.col-11 {
  grid-column-end: span 11;
}
.col-12 {
  grid-column-end: span 12;
}

4.前端综合处理handle.php



<?php
require 'autoLoad.php';

use compotents\conn\DBconn;

$user =new  DBconn();
$table = 'tb_goods';//表名
$where =''; //判断的条件
$data =[];//添加或者更新的数据

$action = $_GET['action'];
switch ($action)
{
    case 'insert':
        $name = $_POST['goodsName'];
        $price = $_POST['goodsPrice'];
        $unit = $_POST['goodsUnit'];
        $date = $_POST['goodsSdate'];
        $data = ['name'=>"$name",'price'=>"$price",'unit'=>"$unit",'sdate'=>"$date"];
        $user->insert($table,$data);
    break;
    case 'update':
        $id = $_GET['id'];
        $name = $_POST['goodsName'];
        $price = $_POST['goodsPrice'];
        $unit = $_POST['goodsUnit'];
        $sdate = $_POST['goodSdate'];

        $where = "`id`=$id";
        $data = ['name'=>"$name",'price'=>"$price",'unit'=>"$unit",'sdate'=>"$sdate"];
        $user->update($table,$data,$where);
    break;
    case 'delete':
        $id = $_GET['id'];
        $where = "`id`=$id";
        $user->delete($table,$where);
    break;
    default:
        echo '不支持此操作';
}

?>

5.查询商品select.php



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

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link rel="stylesheet" href="style/select.css">
    <title>商品查询页</title>
</head>

<body>
    <div class="show">
        <div class="row">
            <div>商品编号</div>
            <div>商品名称</div>
            <div>商品价格</div>
            <div>上架时间</div>
        </div>
        <?php
            require 'autoLoad.php';
            use compotents\conn\DBconn;
            $user =new  DBconn();
            $table = 'tb_goods';//表名
            $where ='*'; //判断的条件 如果选择所有数据则为*
            //显示所有用户信息
            $records = $user->select($table,$where);
            foreach($records as $res):
        ?>
        <div class="row">
            <div><?php echo $res['id']; ?></div>
            <div><?php echo $res['name']; ?></div>
            <div><?php echo $res['price'],'元/',$res['unit']; ?></div>
            <div><?php echo $res['sdate']; ?></div>
        </div>
        <?php
            endforeach;
        ?>
    </div>
</body>

</html>

查询效果图

5.添加商品insert.php



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

<head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <link rel="stylesheet" href="style/insert.css" />
    <title>商品添加页</title>
</head>

<body>
    <div class="add">
        <h2>商品添加</h2>
        <form action="handle.php?action=insert" method="POST">
            <div>
                <label for="goodsName">商品名称：</label>
                <input type="text" name="goodsName" placeholder="商品名称不能为空" require />
            </div>
            <div>
                <label for="goodsPrice">商品单价：</label>
                <input type="text" name="goodsPrice" placeholder="输入商品价格" />
            </div>
            <div>
                <label for="goodsUnit">商品单位：</label>
                <select name="goodsUnit" id="goodsUnit">
                    <option value="斤" selected>斤</option>
                    <option value="盒">盒</option>
                    <option value="袋">袋</option>
                    <option value="捆">捆</option>
                    <option value="筐">筐</option>
                    <option value="箱">箱</option>
                    <option value="桶">桶</option>
                </select>
            </div>
            <div>
                <label for="goodsSdate">上架时间：</label>
                <input type="date" name="goodsSdate" id="goodsSdate" value="<?php echo date('Y-m-d'); ?>" />
            </div>
            <div>
                <button type="submit">添加</button>
            </div>
        </form>
    </div>
</body>

</html>

添加商品
添加成功

5.修改商品update.php



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

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link rel="stylesheet" href="style/select.css">
    <title>商品修改页</title>
</head>

<body>
    <div class="show">
        <div class="row">
            <div>商品编号</div>
            <div>商品名称</div>
            <div>价格/单位</div>
            <div>上架时间</div>
            <div>更新操作</div>
        </div>
        <?php
            require 'autoLoad.php';
            use compotents\conn\DBconn;
            $user =new  DBconn();
            $table = 'tb_goods';//表名
            $where ='*'; //判断的条件 如果选择所有数据则为*
            //显示所有用户信息
            $records = $user->select($table,$where);
            foreach($records as $res):
        ?>
        <div class="row">
            <form action="handle.php?action=update&id=<?php echo $res['id']; ?>" method="POST">
                <div style="width:100px"><?php echo $res['id']; ?></div>
                <input type="text" name="goodsName" id="goodsName" style="width:120px"
                    value="<?php echo $res['name']; ?>">
                <div>
                    <input type="text" name="goodsPrice" id="goodsPrice" style="width:50px"
                        value="<?php echo $res['price']; ?>">/
                    <input type="text" name="goodsUnit" id="goodsUnit" style="width:50px"
                        value="<?php echo $res['unit']; ?>">
                </div>
                <input type="date" name="goodSdate" id="goodSdate" style="width:120px"
                    value="<?php echo $res['sdate']; ?>">
                <div style="width:120px"><button type="submit">修改</button></div>
            </form>
        </div>
        <?php
            endforeach;
        ?>
    </div>
</body>

</html>

修改商品
修改成功

5.删除商品delete.php



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

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link rel="stylesheet" href="style/select.css">
    <title>商品删除页</title>
</head>

<body>
    <div class="show">
        <div class="row">
            <div>商品编号</div>
            <div>商品名称</div>
            <div>商品价格</div>
            <div>上架时间</div>
            <div>删除操作</div>
        </div>
        <?php
            require 'autoLoad.php';
            use compotents\conn\DBconn;
            $user =new  DBconn();
            $table = 'tb_goods';//表名
            $where ='*'; //判断的条件 如果选择所有数据则为*
            //显示所有用户信息
            $records = $user->select($table,$where);
            foreach($records as $res):
        ?>
        <div class="row">
            <div><?php echo $res['id']; ?></div>
            <div><?php echo $res['name']; ?></div>
            <div><?php echo $res['price'],'元/',$res['unit']; ?></div>
            <div><?php echo $res['sdate']; ?></div>
            <div><a href="handle.php?action=delete&id=<?php echo $res['id']; ?>">删除</a></div>
        </div>
        <?php
            endforeach;
        ?>
    </div>
</body>

</html>