Community Learn Tools Library Leisure
search
English
Home > List of blog posts > PDO、类自动加载、数据库用户表增删改查小案例
Blogger Information
Blog 9
fans 0
comment 0
visits 7981
Special Recommendation
More>
Related recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
PDO、类自动加载、数据库用户表增删改查小案例
若是梦终会醒
Original
727 people have browsed it

用户表增删改查小案例

1.1 index.php

  1. <?php
  3. namespace pdo_edu;
  5. use MyPDO;
  6. //自动加载类
  7. spl_autoload_register(function ($class_name) {
  8.     require "./{$class_name}.class.php";
  9. });
  10. //连接数据库
  11. $config = require 'config/database.php';
  12. $mypdo = MyPDO::getInstance($config);
  13. //获取用户数据
  14. $list = $mypdo->fetchAll('select * from data_user');
  15. //渲染视图
  16. require './data_user_list.html';
  17. //进行编辑操作
  18. //获取编辑时提交的数据
  19. @$username = trim($_POST['user_name']);
  20. @$password = trim($_POST['user_pass']);
  21. @$id = array_flip($_POST);
  22. @$id = $id['编辑'];
  23. if ($_POST) {
  24.     $sql = "UPDATE `data_user` SET `user_name` = '{$username}',`user_pass`='{$password}'  WHERE `user_id`={$id}";
  25.     $update = $mypdo->exec($sql);
  26. }
  27. //添加用户
  28. $booladd = $_GET['add_user'] ?? 0;
  29. if ($booladd) {
  30.     $addname = time();
  31.     $sql = "INSERT `data_user` SET `user_name` = '{$addname}',`user_pass`='0000' ";
  32.     $insert = $mypdo->exec($sql);
  33. }
  34. //删除用户
  35. $booldel = $_GET['del'] ?? 0;
  36. if ($booldel) {
  37.     $sql = "DELETE FROM  `data_user` WHERE `user_id`={$booldel}";
  38.     $del = $mypdo->exec($sql);
  39. }

1.2 data_user_list.html 渲染视图

  1. <!DOCTYPE html>
  2. <html lang="en">
  4. <head>
  5.     <meta charset="UTF-8">
  6.     <meta name="viewport" content="width=device-width, initial-scale=1.0">
  7.     <title>用户管理</title>
  8. </head>
  10. <body>
  11.     <a href="?">刷新当前页</a>
  12.     <a href="?add_user=1">添加用户</a>
  13.     <table border='1' width='980' bordercolor='#000'>
  15.         <tr>
  16.             <th>用户ID</th>
  17.             <th>用户名</th>
  18.             <th>用户密码</th>
  19.             <th>操作</th>
  20.         </tr>
  21.         <?php foreach($list as $rows):?>
  22.         <form action="?" method="post">
  23.             <tr>
  24.                 <td>
  25.                     <?=$rows['user_id']?>
  26.                 </td>
  27.                 <td>
  28.                     <input type="text" name="user_name" value=" <?=$rows['user_name']?>">
  30.                 </td>
  31.                 <td>
  32.                     <input type="text" name="user_pass" value=" <?=$rows['user_pass']?>">
  33.                 </td>
  34.                 <td><input type="submit" value="编辑" name="<?=$rows['user_id']?>"><span>-----</span><a href="?del=<?=$rows['user_id']?>">删除</a></td>
  35.             </tr>
  36.         </form>
  37.         <?php endforeach;?>
  38.     </table>
  39. </body>
  41. </html>

1.3 MyPDO.class.php 自定义PDO

  1. <?php
  2. class MyPDO {
  3.     private $type;      //数据库类别
  4.     private $host;      //主机地址
  5.     private $port;      //端口号
  6.     private $dbname;    //数据库名
  7.     private $charset;   //字符集
  8.     private $user;      //用户名
  9.     private $pwd;       //密码
  10.     private $pdo;       //保存PDO对象
  11.     private static $instance;
  12.     private function __construct($param) {
  13.         $this->initParam($param);
  14.         $this->initPDO();
  15.         $this->initException();
  16.     }
  17.     private function __clone() {
  18.     }
  19.     public static function getInstance($param=array()){
  20.         if(!self::$instance instanceof self)
  21.             self::$instance=new self($param);
  22.         return self::$instance;
  23.     }
  24.     //初始化参数
  25.     private function initParam($param){
  26.         $this->type=$param['type']??'mysql';
  27.         $this->host=$param['host']??'127.0.0.1';
  28.         $this->port=$param['port']??'3306';
  29.         $this->dbname=$param['dbname']??'data';
  30.         $this->charset=$param['charset']??'utf8';
  31.         $this->user=$param['user']??'root';
  32.         $this->pwd=$param['pwd']??'root';
  33.     }
  34.     //初始化PDO
  35.     private function initPDO(){
  36.         try{
  37.             $dsn="{$this->type}:host={$this->host};port={$this->port};dbname={$this->dbname};charset={$this->charset}";
  38.             $this->pdo=new PDO($dsn, $this->user, $this->pwd);
  39.         } catch (PDOException $ex) {
  40.             $this->showException($ex);
  41.             exit;
  42.         }
  43.     }
  45.     //显示异常
  46.     private function showException($ex,$sql=''){
  47.         if($sql!=''){
  48.             echo 'SQL语句执行失败<br>';
  49.             echo '错误的SQL语句是：'.$sql,'<br>';
  50.         }
  51.         echo '错误编号：'.$ex->getCode(),'<br>';
  52.         echo '错误行号：'.$ex->getLine(),'<br>';
  53.         echo '错误文件：'.$ex->getFile(),'<br>';
  54.         echo '错误信息：'.$ex->getMessage(),'<br>';
  55.     }
  56.     //设置异常模式
  57.     private function initException(){
  58.         $this->pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
  59.     }
  61.     //执行增、删、改操作
  62.     public function exec($sql){
  63.         try{
  64.             return $this->pdo->exec($sql);
  65.         } catch (PDOException $ex) {
  66.             $this->showException($ex, $sql);
  67.             exit;
  68.         }
  69.     }
  70.     //获取自动增长的编号
  71.     public function lastInsertId(){
  72.         return $this->pdo->lastInsertId();
  73.     }
  75.     //判断匹配的类型
  76.     private function fetchType($type){
  77.         switch ($type){
  78.             case 'num':
  79.                 return PDO::FETCH_NUM;
  80.             case 'both':
  81.                 return PDO::FETCH_BOTH;
  82.             case 'obj':
  83.                 return PDO::FETCH_OBJ;
  84.             default:
  85.                  return PDO::FETCH_ASSOC;
  86.         }
  87.     }
  88.     //获取所有数据 ，返回二维数组
  89.     public function fetchAll($sql,$type='assoc'){
  90.         try{
  91.             $stmt=$this->pdo->query($sql);  //获取PDOStatement对象
  92.             $type= $this->fetchType($type); //获取匹配方法
  93.             return $stmt->fetchAll($type);
  94.         } catch (Exception $ex) {
  95.             $this->showException($ex, $sql);
  96.         }
  97.     }
  98.     //获取一维数组
  99.     public function fetchRow($sql,$type='assoc'){
  100.         try{
  101.             $stmt=$this->pdo->query($sql);  //获取PDOStatement对象
  102.             $type= $this->fetchType($type); //获取匹配方法
  103.             return $stmt->fetch($type);
  104.         } catch (Exception $ex) {
  105.             $this->showException($ex, $sql);
  106.             exit;
  107.         }
  108.     }
  109.     //返回一行一列
  110.     public function fetchColumn($sql){
  111.         try{
  112.              $stmt=$this->pdo->query($sql);
  113.             return $stmt->fetchColumn();
  114.         } catch (Exception $ex) {
  115.             $this->showException($ex, $sql);
  116.             exit;
  117.         }
  119.     }
  121. }

3.4 创建config配置文件夹 写入 database.php 数据库配置文件

  1. <?php
  2. namespace pdo_edu;
  4. // 数据库连接配置参数
  5. return [
  6.     // 数据库的类型
  7.     'type' => $type ?? 'mysql',
  8.     // 数据库默认主机
  9.     'host' => $host?? 'localhost',
  10.     // 默认数据库
  11.     'dbname'=> $dbname ?? 'phpedu',
  12.     // 默认字符编码集
  13.     'charset'=> $charset ?? 'utf8',
  14.     // 默认端口号
  15.     'port'=> $port ?? '3306',
  16.     // 默认的用户名
  17.     'user'=> $username ?? 'root',
  18.     // 默认的用户密码
  19.     'pwd'=> $password ?? 'root',
  20. ];

效果图:

查:

改:

增:

删:

Correcting teacher：天蓬老师天蓬老师

Correction status：qualified

Teacher's comments：完成的相当出色, 但有个建议, 方法名不要用与pdo中重名的方法, 尽管有空间区分也不要这用, 遵循行业规范
Statement of this Website
The copyright of this blog article belongs to the blogger. Please specify the address when reprinting! If there is any infringement or violation of the law, please contact admin@php.cn Report processing!
All comments Speak rationally on civilized internet, please comply with News Comment Service Agreement
2 comments
王小飞 2020-05-08 14:31:52
这里面很多参数都看不懂
2 floor
王小飞 2020-05-08 14:02:09
厉害！好像写出了用户管理中心
1 floor
Author's latest blog post