search

博客列表 >php 数据库操作mysqli

php 数据库操作mysqli

王娇
王娇原创
2020年05月09日 21:33:271331浏览

学习总结

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

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

  1. <?php
  3. namespace compotents\conn
  4. {
  5.     use Exception;
  6.     use mysqli;
  7.     class DBconn
  8.     {
  9.         private $config = [];
  10.         protected $dbConn;
  11.         public function __construct($dbName = 'db_phpstudy',$userName = 'root',$passWord ='root')
  12.         {
  13.             $this ->config['type'] = 'mysql';
  14.             $this ->config['host'] = 'localhost';
  15.             $this ->config['dbName'] = $dbName;
  16.             $this ->config['userName'] = $userName;
  17.             $this ->config['passWord'] = $passWord;
  18.             $this ->config['charSet'] = 'utf8';
  19.             $this ->config['port'] = '3306';
  20.             $this ->connect();
  22.         }
  23.         public function connect()
  24.         {
  25.             //拆分数组，键名当做变量名，值当做变量的值,拆分成数据库连接的变量
  26.            extract($this->config,EXTR_PREFIX_SAME,'config');
  27.            try
  28.            {
  29.                //1.创建一个mysqli的数据库连接
  30.                $this->dbConn = new mysqli($host,$userName,$passWord,$dbName);
  31.                //2. 判断是否连接成功?
  32.                 if ($this->dbConn->connect_errno) echo $this->dbConn->connect_error;
  33.                //3.设置数据库连接的字符集
  34.                $this->dbConn->set_charset($charSet);
  35.            }
  36.            catch(Exception $e)
  37.            {
  38.                 die($e->getMessage());
  39.            }
  40.         }
  42.         //查询返回查询结果集
  43.         public function select($table,$where)
  44.         {
  45.             if ($where === '*'):
  46.                 $sql = "SELECT * FROM `$table`";
  47.             else:
  48.                 $sql = "SELECT * FROM `$table` WHERE $where";
  49.             endif;
  51.             $info = $this ->dbConn->query($sql);
  52.             $records = $info->fetch_all(MYSQLI_ASSOC);
  53.             return $records;
  54.         }
  55.         //插入记录，输出是否成功添加记录
  56.         public function insert($table,$insData)
  57.         {
  58.             //把传入的添加数据的数组转换为一个SQL添加字符串
  59.             $insertSet = $this->toSqlStr($insData);
  61.             $sql = "INSERT `$table` SET $insertSet";
  62.             $flag =  $this->dbConn->query($sql);
  63.             $rowCount = $this->dbConn->affected_rows;//返回受影响的记录数
  64.             if ($flag) {
  65.                 //$this->dbConn->insert_id返回最后一条插入语句的自增id
  66.                 if ($rowCount > 0) {
  67.                     echo '成功添加了 ' . $rowCount . ' 条记录, 新增记录主键ID: ' . $this->dbConn->insert_id;
  68.                 } else {
  69.                     echo '没有添加新记录';
  70.                 }
  71.             } else {
  72.                 //$this->dbConn->errno返回最近函数调用的错误代码
  73.                 //$this->dbConn->error返回最后一次调用的错误信息
  74.                 die('添加失败'. $this->dbConn->errno . ' : ' . $this->dbConn->error);
  75.             }
  76.         }
  77.         //更新记录，输出更新几条记录
  78.         public function update($table,$data,$where)
  79.         {
  80.             //把传入的添加数据的数组转换为一个SQL添加字符串
  81.             $updateSet = $this->toSqlStr($data);   
  82.             $sql = "UPDATE `$table` SET $updateSet WHERE $where";
  83.             $flag =  $this->dbConn->query($sql);
  84.             $rowCount = $this->dbConn->affected_rows;//返回受影响的记录数
  85.             if ($flag) {
  86.                 //$this->dbConn->insert_id返回最后一条插入语句的自增id
  87.                 if ($rowCount > 0) {
  88.                     echo '成功更新了 ' . $rowCount . ' 条记录';
  89.                 } else {
  90.                     echo '没有更新记录';
  91.                 }
  92.             } else {
  93.                 //$this->dbConn->errno返回最近函数调用的错误代码
  94.                 //$this->dbConn->error返回最后一次调用的错误信息
  95.                 die('更新失败'. $this->dbConn->errno . ' : ' . $this->dbConn->error);
  96.             }
  98.         }
  99.         //删除记录，输出是否删除成功
  100.         public function delete($table,$where)
  101.         {
  102.             $sql = "DELETE FROM $table WHERE $where";
  103.             $flag =  $this->dbConn->query($sql);
  104.             $rowCount = $this->dbConn->affected_rows;//返回受影响的记录数
  105.             if ($flag) {
  106.                 //$this->dbConn->insert_id返回最后一条插入语句的自增id
  107.                 if ($rowCount > 0) {
  108.                     echo '成功删除了 ' . $rowCount . ' 条记录';
  109.                 } else {
  110.                     echo '没有删除记录';
  111.                 }
  112.             } else {
  113.                 //$this->dbConn->errno返回最近函数调用的错误代码
  114.                 //$this->dbConn->error返回最后一次调用的错误信息
  115.                 die('删除失败'. $this->dbConn->errno . ' : ' . $this->dbConn->error);
  116.             }
  117.         }
  118.         public function toSqlStr($arr):string
  119.         {
  120.             //把数组的键提取到一个数组中
  121.             $keys = array_keys($arr);
  122.             //把数组的值提取到一个数组中
  123.             $value = array_values($arr);
  125.             $con = count($keys);
  126.             $sqlStr ='';
  127.             for ($i=0;$i<$con;$i++):
  128.                 if($i===$con-1):
  129.                     $sqlStr .= " `$keys[$i]`='$value[$i]'";
  130.                 else:
  131.                     $sqlStr .= " `$keys[$i]`='$value[$i]' ,";
  132.                 endif;   
  133.             endfor; 
  134.             return $sqlStr;
  135.         }
  136.     }
  137. }
  138. ?>

2.后台首页index.html

  2. <!DOCTYPE html>
  3. <html lang="en">
  5. <head>
  6.     <meta charset="UTF-8">
  7.     <meta name="viewport" content="width=device-width, initial-scale=1.0">
  8.     <link rel="stylesheet" href="style/style.css">
  9.     <title>商品后台管理</title>
  10. </head>
  12. <body>
  13.     <div class="container">
  14.         <div class="row">
  15.             <div class="header col-12">
  16.                 <span>商品后台管理</span>
  17.                 <div><a href="">返回首页</a> </div>
  18.                 <div><a href="">退出</a></div>
  19.             </div>
  20.         </div>
  21.         <div class="row">
  22.             <div class="aside col-3">
  23.                 <div><a href="select.php" target="main"><span>查询商品</span> </a></div>
  24.                 <div><a href="insert.php" target="main"><span>添加商品</span> </a></div>
  25.                 <div><a href="update.php" target="main"><span>修改商品</span> </a></div>
  26.                 <div><a href="delete.php" target="main"><span>删除商品</span> </a></div>
  28.             </div>
  29.             <div class="main col-9">
  30.                 <iframe src="select.php" frameborder="0" name="main" width="680px" height="510px"></iframe>
  31.             </div>
  32.         </div>
  33.         <div class="row">
  34.             <div class="footer col-12">
  35.                 <div>联系邮箱：<a href="mailto:573661083@qq.com">573661083@qq.com</a></div>
  36.                 <div>联系电话：<a href="tel:15010046927">1501004xxxx</a></div>
  37.                 <div>Copyright 1998 - 2020 Tencent. All Rights Reserved</div>
  38.             </div>
  39.         </div>
  41.     </div>
  42. </body>
  44. </html>

3.首页样式表 style.css

  2. @import "reset.css";
  4. /* 整页布局 */
  5. .container {
  6.   max-width: 920px;
  7.   min-height: 650px;
  8.   margin-left: auto;
  9.   margin-right: auto;
  10.   background-color: white;
  11.   display: grid;
  12.   gap: 5px;
  13. }
  14. /* 整页中的每行分成12列 */
  15. .container > .row {
  16.   display: grid;
  17.   grid-template-columns: repeat(12, 1fr);
  18.   gap: 5px;
  19. }
  20. /* 头部布局 */
  21. .container > .row > .header {
  22.   margin-top: 5px;
  23.   background-color: #58c4f2;
  24.   max-height: 56px;
  25.   border-radius: 3px;
  26.   padding: 0px 10px;
  27.   display: flex;
  28.   flex-flow: row nowrap;
  29.   align-items: center;
  30. }
  31. .container > .row > .header > span {
  32.   letter-spacing: 2px;
  33.   margin-left: 20px;
  34.   font-size: 1.5rem;
  35.   font-weight: bolder;
  36. }
  37. .container > .row > .header > div > a {
  38.   margin: 0px 10px;
  39. }
  40. .container > .row > .header > div > a:hover {
  41.   color: lightgreen;
  42. }
  43. .container > .row > .header > div:nth-of-type(1) {
  44.   margin-left: auto;
  45. }
  47. /* 侧边栏  */
  48. .container > .row > .aside {
  49.   min-height: 500px;
  50.   background-color: #ccc;
  51.   border-radius: 3px;
  52.   display: flex;
  53.   flex-flow: column nowrap;
  54.   padding: 5px;
  55. }
  56. /* 侧边栏导航 */
  57. .container > .row > .aside > div {
  58.   background-color: #58c4f2;
  59.   height: 40px;
  60.   margin: 2px 8px;
  61.   border-radius: 10px;
  62. }
  63. .container > .row > .aside > div > a {
  64.   width: 100%;
  65.   height: 100%;
  66.   display: flex;
  67.   justify-content: center;
  68.   align-items: center;
  69. }
  70. .container > .row > .aside > div > a > span {
  71.   font-size: 1.2rem;
  72.   letter-spacing: 2px;
  73. }
  75. .container > .row > .aside > div:hover {
  76.   background-color: lightgreen;
  77.   box-shadow: 0 0 5px #555;
  78. }
  79. /* 主体内容显示区 */
  80. .container > .row > .main {
  81.   min-height: 500px;
  82. }
  83. /* 页脚 */
  84. .container > .row > .footer {
  85.   max-height: 80px;
  86.   margin-bottom: 5px;
  87.   background-color: #58c4f2;
  88.   border-radius: 3px;
  89.   display: flex;
  90.   flex-flow: column nowrap;
  91.   justify-content: center;
  92.   align-items: center;
  93. }
  94. .container > .row > .footer > div > a:hover {
  95.   color: lightgreen;
  96. }
  97. /* 12列栅格布局 */
  98. .col-1 {
  99.   grid-column-end: span 1;
  100. }
  101. .col-2 {
  102.   grid-column-end: span 2;
  103. }
  104. .col-3 {
  105.   grid-column-end: span 3;
  106. }
  107. .col-4 {
  108.   grid-column-end: span 4;
  109. }
  110. .col-5 {
  111.   grid-column-end: span 5;
  112. }
  113. .col-6 {
  114.   grid-column-end: span 6;
  115. }
  116. .col-7 {
  117.   grid-column-end: span 7;
  118. }
  119. .col-8 {
  120.   grid-column-end: span 8;
  121. }
  122. .col-9 {
  123.   grid-column-end: span 9;
  124. }
  125. .col-10 {
  126.   grid-column-end: span 10;
  127. }
  128. .col-11 {
  129.   grid-column-end: span 11;
  130. }
  131. .col-12 {
  132.   grid-column-end: span 12;
  133. }

4.前端综合处理handle.php

  2. <?php
  3. require 'autoLoad.php';
  5. use compotents\conn\DBconn;
  7. $user =new  DBconn();
  8. $table = 'tb_goods';//表名
  9. $where =''; //判断的条件
  10. $data =[];//添加或者更新的数据
  12. $action = $_GET['action'];
  13. switch ($action)
  14. {
  15.     case 'insert':
  16.         $name = $_POST['goodsName'];
  17.         $price = $_POST['goodsPrice'];
  18.         $unit = $_POST['goodsUnit'];
  19.         $date = $_POST['goodsSdate'];
  20.         $data = ['name'=>"$name",'price'=>"$price",'unit'=>"$unit",'sdate'=>"$date"];
  21.         $user->insert($table,$data);
  22.     break;
  23.     case 'update':
  24.         $id = $_GET['id'];
  25.         $name = $_POST['goodsName'];
  26.         $price = $_POST['goodsPrice'];
  27.         $unit = $_POST['goodsUnit'];
  28.         $sdate = $_POST['goodSdate'];
  30.         $where = "`id`=$id";
  31.         $data = ['name'=>"$name",'price'=>"$price",'unit'=>"$unit",'sdate'=>"$sdate"];
  32.         $user->update($table,$data,$where);
  33.     break;
  34.     case 'delete':
  35.         $id = $_GET['id'];
  36.         $where = "`id`=$id";
  37.         $user->delete($table,$where);
  38.     break;
  39.     default:
  40.         echo '不支持此操作';
  41. }
  43. ?>

5.查询商品select.php

  2. <!DOCTYPE html>
  3. <html lang="en">
  5. <head>
  6.     <meta charset="UTF-8">
  7.     <meta name="viewport" content="width=device-width, initial-scale=1.0">
  8.     <link rel="stylesheet" href="style/select.css">
  9.     <title>商品查询页</title>
  10. </head>
  12. <body>
  13.     <div class="show">
  14.         <div class="row">
  15.             <div>商品编号</div>
  16.             <div>商品名称</div>
  17.             <div>商品价格</div>
  18.             <div>上架时间</div>
  19.         </div>
  20.         <?php
  21.             require 'autoLoad.php';
  22.             use compotents\conn\DBconn;
  23.             $user =new  DBconn();
  24.             $table = 'tb_goods';//表名
  25.             $where ='*'; //判断的条件 如果选择所有数据则为*
  26.             //显示所有用户信息
  27.             $records = $user->select($table,$where);
  28.             foreach($records as $res):
  29.         ?>
  30.         <div class="row">
  31.             <div><?php echo $res['id']; ?></div>
  32.             <div><?php echo $res['name']; ?></div>
  33.             <div><?php echo $res['price'],'元/',$res['unit']; ?></div>
  34.             <div><?php echo $res['sdate']; ?></div>
  35.         </div>
  36.         <?php
  37.             endforeach;
  38.         ?>
  39.     </div>
  40. </body>
  42. </html>
  • 查询效果图

5.添加商品insert.php

  2. <!DOCTYPE html>
  3. <html lang="en">
  5. <head>
  6.     <meta charset="UTF-8" />
  7.     <meta name="viewport" content="width=device-width, initial-scale=1.0" />
  8.     <link rel="stylesheet" href="style/insert.css" />
  9.     <title>商品添加页</title>
  10. </head>
  12. <body>
  13.     <div class="add">
  14.         <h2>商品添加</h2>
  15.         <form action="handle.php?action=insert" method="POST">
  16.             <div>
  17.                 <label for="goodsName">商品名称：</label>
  18.                 <input type="text" name="goodsName" placeholder="商品名称不能为空" require />
  19.             </div>
  20.             <div>
  21.                 <label for="goodsPrice">商品单价：</label>
  22.                 <input type="text" name="goodsPrice" placeholder="输入商品价格" />
  23.             </div>
  24.             <div>
  25.                 <label for="goodsUnit">商品单位：</label>
  26.                 <select name="goodsUnit" id="goodsUnit">
  27.                     <option value="斤" selected>斤</option>
  28.                     <option value="盒">盒</option>
  29.                     <option value="袋">袋</option>
  30.                     <option value="捆">捆</option>
  31.                     <option value="筐">筐</option>
  32.                     <option value="箱">箱</option>
  33.                     <option value="桶">桶</option>
  34.                 </select>
  35.             </div>
  36.             <div>
  37.                 <label for="goodsSdate">上架时间：</label>
  38.                 <input type="date" name="goodsSdate" id="goodsSdate" value="<?php echo date('Y-m-d'); ?>" />
  39.             </div>
  40.             <div>
  41.                 <button type="submit">添加</button>
  42.             </div>
  43.         </form>
  44.     </div>
  45. </body>
  47. </html>

  • 添加商品

  • 添加成功

5.修改商品update.php

  2. <!DOCTYPE html>
  3. <html lang="en">
  5. <head>
  6.     <meta charset="UTF-8">
  7.     <meta name="viewport" content="width=device-width, initial-scale=1.0">
  8.     <link rel="stylesheet" href="style/select.css">
  9.     <title>商品修改页</title>
  10. </head>
  12. <body>
  13.     <div class="show">
  14.         <div class="row">
  15.             <div>商品编号</div>
  16.             <div>商品名称</div>
  17.             <div>价格/单位</div>
  18.             <div>上架时间</div>
  19.             <div>更新操作</div>
  20.         </div>
  21.         <?php
  22.             require 'autoLoad.php';
  23.             use compotents\conn\DBconn;
  24.             $user =new  DBconn();
  25.             $table = 'tb_goods';//表名
  26.             $where ='*'; //判断的条件 如果选择所有数据则为*
  27.             //显示所有用户信息
  28.             $records = $user->select($table,$where);
  29.             foreach($records as $res):
  30.         ?>
  31.         <div class="row">
  32.             <form action="handle.php?action=update&id=<?php echo $res['id']; ?>" method="POST">
  33.                 <div style="width:100px"><?php echo $res['id']; ?></div>
  34.                 <input type="text" name="goodsName" id="goodsName" style="width:120px"
  35.                     value="<?php echo $res['name']; ?>">
  36.                 <div>
  37.                     <input type="text" name="goodsPrice" id="goodsPrice" style="width:50px"
  38.                         value="<?php echo $res['price']; ?>">/
  39.                     <input type="text" name="goodsUnit" id="goodsUnit" style="width:50px"
  40.                         value="<?php echo $res['unit']; ?>">
  41.                 </div>
  42.                 <input type="date" name="goodSdate" id="goodSdate" style="width:120px"
  43.                     value="<?php echo $res['sdate']; ?>">
  44.                 <div style="width:120px"><button type="submit">修改</button></div>
  45.             </form>
  46.         </div>
  47.         <?php
  48.             endforeach;
  49.         ?>
  50.     </div>
  51. </body>
  53. </html>

  • 修改商品

  • 修改成功

5.删除商品delete.php

  2. <!DOCTYPE html>
  3. <html lang="en">
  5. <head>
  6.     <meta charset="UTF-8">
  7.     <meta name="viewport" content="width=device-width, initial-scale=1.0">
  8.     <link rel="stylesheet" href="style/select.css">
  9.     <title>商品删除页</title>
  10. </head>
  12. <body>
  13.     <div class="show">
  14.         <div class="row">
  15.             <div>商品编号</div>
  16.             <div>商品名称</div>
  17.             <div>商品价格</div>
  18.             <div>上架时间</div>
  19.             <div>删除操作</div>
  20.         </div>
  21.         <?php
  22.             require 'autoLoad.php';
  23.             use compotents\conn\DBconn;
  24.             $user =new  DBconn();
  25.             $table = 'tb_goods';//表名
  26.             $where ='*'; //判断的条件 如果选择所有数据则为*
  27.             //显示所有用户信息
  28.             $records = $user->select($table,$where);
  29.             foreach($records as $res):
  30.         ?>
  31.         <div class="row">
  32.             <div><?php echo $res['id']; ?></div>
  33.             <div><?php echo $res['name']; ?></div>
  34.             <div><?php echo $res['price'],'元/',$res['unit']; ?></div>
  35.             <div><?php echo $res['sdate']; ?></div>
  36.             <div><a href="handle.php?action=delete&id=<?php echo $res['id']; ?>">删除</a></div>
  37.         </div>
  38.         <?php
  39.             endforeach;
  40.         ?>
  41.     </div>
  42. </body>
  44. </html>

  • 删除商品

  • 删除成功

上一条：php中命名空间与自动加载下一条：cmd命令操作Mysql数据库，命令行操作Mysql
声明：本文内容转载自脚本之家，由网友自发贡献，版权归原作者所有，如您发现涉嫌抄袭侵权，请联系admin@php.cn 核实处理。
全部评论
文明上网理性发言，请遵守新闻评论服务协议

相关文章

查看更多