Self written operation class of adding deleting modifying and checking mysql database by using PDO

Preface

PDO1 is the abbreviation of PHP Data Object (PHP Data Object).

You cannot use the PDO extension itself to perform any database operations, and you must use one database-specific PDO driver (the PDO driver for a specific database) to access the database server.

PDO does not provide database abstraction. It does not override SQL or provide functionality missing from the database itself. If you need this functionality, you need to use a more mature abstraction layer.

Recently, I used PDO to operate mysql database when I was doing the project, so I wrote a class file named mysql_class. php file code as follows:

Sample code

<?php
class mysql{
 //常量声明
 const DSN = "mysql:host=[数据库地址];dbname=[数据库名];charset=utf8";//数据库地址与数据库名及编码
 const USERNAME = "[数据库用户名]";//用户名
 const PASSWD = "[数据库密码]";//密码
 
 //私有变量声明
 private $sql = NULL;//sql语句缓存
 private $link = NULL;//数据库连接
 private $result = NULL;//结果
 
 /*******************************************************************************
  * @ 名称：建立连接
  * @ 属性：私有
 *******************************************************************************/
 private function connect(){
  try {
   $this->link = new \PDO(self::DSN, self::USERNAME, self::PASSWD, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));//创建连接
  }
  catch (PDOException $e) {
   die ("Error!:".$e->getMessage()."<hr/>");
  }
 }
 
 /*******************************************************************************
  * @ 名称：执行sql语句
  * @ 属性：私有
  * @ 入口参数：无
  * @ 出口参数:执行成功返回真，否则返回假，查询语句存储结果集数组
 *******************************************************************************/
 private function sql(){
  try {
   $this->connect();
   $this->link->beginTransaction();//开启1个事务
   $prepare = $this->link->prepare($this->sql);//准备查询语句
   $prepare->execute();//执行查询语句并返回结果集
   $cmd = strtolower(substr(trim($this->sql),0,6));//截取命令字符
   if($cmd == "select"){
    $array = $prepare->fetch(PDO::FETCH_ASSOC);//获取结果集中的所有数据
    if(count($array)){
     $this->result = NULL;
     $this->result = $array;//存储结果集
     return true;//查询到结果返回真
    }else{
     return false;//否则返回假
    }
   }else if($cmd == "insert" || $cmd == "delete" || $cmd == "update"){
    if($prepare){
     return true;//执行成功返回真
    }else{
     return false;//否则返回假
    }
   }
   $this->link->commit(); //如果正确执行完成那么确认commit
  } catch (PDOException $e) {
   $this->link->rollBack();//如果执行中有错误的情况下回滚
   die ("Error!:".$e->getMessage()."<hr/>");
  }
 }
 
 /*******************************************************************************
  * @ 名称：sql语句处理
  * @ 属性：公有
  * @ 入口参数：cmd增删改查字符命令；dsname数据表名；first第1个参数；second第2个参数；
  * @ 出口参数：执行成功返回真，否则返回假，查询操作返回结果集数组
  * @ 使用示例：
  $mysql->handle("insert","abc","openid,nickname","'123','abc'");//增加
  $mysql->handle("delete","abc","openid='123'");//删除
  $mysql->handle("update","abc","nickname='def'","openid='123'");//更新
  $res = $mysql->handle("select","abc","*","openid='123'");//查询
  if(is_array($res) == true){
   foreach($res as $key=>$val){
    echo $key."=".$val."<hr>";
   }
  }//遍历查询结果数组
 *******************************************************************************/
 public function handle($cmd,$dsname,$first,$second=NULL){
  switch($cmd){
   case 'insert'://插入
    $this->sql = "insert into $dsname ($first) values ($second)";
    break;
   case 'delete'://删除
    $this->sql = "delete from $dsname where $first";
    break;
   case 'update'://更新
    $this->sql = "update $dsname set $first where $second";
    break;
   case 'select'://查询
    $this->sql = "select $first from $dsname where $second";
    break;
   default:
    die ("Syntax Error!");//提示语法错误
    break;
  }
  
  $res = $this->sql();//执行sql语句
  if($res){
   if($cmd == 'select'){
    return $this->result;//返回查询结果
   }else{
    return true;//执行成功返回真
   }
  }else{
   return false;//否则返回假
  }
  $this->link=NULL;;//关闭数据库
 }
}
$mysql = new mysql;//数据库类的实例化
?>

Summarize