Detailed explanation of the steps for adding, deleting, modifying and querying mysql database using PDO

This time I will bring you a detailed explanation of the steps for adding, deleting, modifying, and querying operations of PDO on mysql database. What are the precautions for PDO adding, deleting, modifying, and querying operations on mysql database? The following is a practical case. Let’s take a look. .

Preface

PDO is the abbreviation of PHP Data Object.

You cannot use the PDO extension itself to perform any

database operations, you must use a database-specific PDO driver (PDO driver for a specific database) to access the database server.

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

Recently I used PDO to operate mysql database when working on a project, so I wrote a class file myself and named it mysql_class.php. The file code is 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();//开启一个事务
   $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第一个参数；second第二个参数；
  * @ 出口参数：执行成功返回真，否则返回假，查询操作返回结果集数组
  * @ 使用示例：
  $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;//数据库类的实例化
?>

I believe you have mastered the method after reading the case in this article. For more exciting information, please pay attention to other related articles on the php Chinese website!

Recommended reading:

Detailed explanation of the use of php namespace

Detailed explanation of the use of Laravel 5.5 core architecture

The above is the detailed content of Detailed explanation of the steps for adding, deleting, modifying and querying mysql database using PDO. For more information, please follow other related articles on the PHP Chinese website!