PDO 增删改查封装的类

2023-01-28 21:24:08

Selecting Data

你在mysql_*中是这样做的

 <?php

     $result = mysql_query('SELECT * from table') or die(mysql_error());

     $num_rows = mysql_num_rows($result);

     while($row = mysql_fetch_assoc($result)) {

         echo $row['field1'];

     }

你在pdo中可以这个样

<?php

    $stmt = $db->query('SELECT * FROM table');

    while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

        echo $row['field1'];

    }

或者

<?php

    $result = mysql_query('SELECT * from table') or die(mysql_error());

    $num_rows = mysql_num_rows($result);

    while($row = mysql_fetch_assoc($result)) {

        echo $row['field1'];

    }

提示：如果你使用了方法像query（）。这个方法返回一个PDOStatement 对象，如果你想取出结果，请这样使用：

<?php

    foreach($db->query('SELECT * FROM table') as $row) {

        echo $row['field1'];

    }

PDO数据是通过->fetch() 方法获得的，这个方法是你的statement处理的。
在fetch之前。最好告诉PDO这个数据你打算怎么fetch。在下个章节我会解释。

Fetch Modes

PDO::FETCH_ASSOC 可以在fetch() 和 fetchAll()中使用。这个告诉PDO返回关联数组，字段的名字作为key。这还有很多fench方法。

首先我说明怎么选择fench 模式。

$stmt->fetch(PDO::FETCH_ASSOC)

在这里我用的是fetch，你也可以用：

PDOStatement::fetchAll() 返回了一个数组包含了所有选择的行
PDOStatement::fetchColumn() 返回下一行的一列作为结果集
PDOStatement::fetchObject() 返回下一行作为一个对象
PDOStatement::setFetchMode() 设置fetch模式

下面讨论fetch模式

PDO::FETCH_ASSOC 关联数组
PDO::FETCH_BOTH 默认的。返回关联数组和索引数组。
还有更多选项，可以阅读文档。

获取行的数量
不是通过mysql_num_rows 而是通过rowCount()，比如说：

<?php

    $stmt = $db->query('SELECT * FROM table');

    $row_count = $stmt->rowCount();

    echo $row_count.' rows selected';

获取插入的id

<?php

    $result = $db->exec("INSERT INTO table(firstname, lastname) VAULES('John', 'Doe')");

    $insertId = $db->lastInsertId();

Insert and Update or Delete statements

在mysql_*中我们是这样做的

<?php

    $results = mysql_query("UPDATE table SET field='value'") or die(mysql_error());

    echo mysql_affected_rows($result);

在PDO中，同样可以这样：

<?php

    $affected_rows = $db->exec("UPDATE table SET field='value'");

    echo $affected_rows;

在上面的语句中，执行了一个sql语句并且返回受影响的行数。

上面的方法在你查询语句中没有变量时很好用。如果有变量，就应该使用 prepared statement or parameterized statement 。

Prepared Statements

Q：什么是 Prepared Statements，我为什么要用他？

A：Prepared Statement 是一条预编译的sql语句，可以被执行多次。

典型的使用Prepared Statement 工作流：

Prepare：语句（statement）模版被application创建，发送到数据库管理系统（DBMS）。某些值仍然违背置顶，通过参数、占位符进行绑定

INSERT INTO PRODUCT (name, price) VALUES (?, ?)

DBMS进行解析、编译，得到最优的语句，把结果（语句）储存起来，并不执行。
执行。过后，程序为参数提供或绑定值，DBMS执行语句（一般会返回一个结果）。程序或许会执行多次因为它有不同的值，想获得不同的结果。咱这个例子里，把Bread作为第一个参数，1.00作为第二个参数。

你可以通过引入占位符使用预编译语句。

Q：什么是命名占位符（named placeholders ），怎么使用呢？

A：命名占位符，用过一个冒号而不是？这样就不用去操心问号的顺序问题了

$stmt->bindParam(':bla', $bla);

<?php

    $stmt = $db->prepare("SELECT * FROM table WHERE id=:id AND name=:name");

    $stmt->execute(array(':name' => $name, ':id' => $id));

    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

另一个对于OOP（面向对象编程）很友好的就是，命名占位符可以直接插入到你的对象，再然后加入到你的数据库。假设属性与字段相同

class person {

    public $name;

    public $add;

    function __construct($a,$b) {

        $this->name = $a;

        $this->add = $b;

    }

}

$demo = new person('john','29 bla district');

$stmt = $db->prepare("INSERT INTO table (name, add) value (:name, :add)");

$stmt->execute((array)$demo);

Q：好了，什么是匿名占位符(unnamed placeholders)，怎么使用？

A：看个例子。

<?php

    $stmt = $db->prepare("INSERT INTO folks (name, add) values (?, ?)");

    $stmt->bindValue(1, $name, PDO::PARAM_STR);

    $stmt->bindValue(2, $add, PDO::PARAM_STR);

    $stmt->execute();

$stmt = $db->prepare("INSERT INTO folks (name, add) values (?, ?)");

 $stmt->execute(array('john', '29 bla district'));

注意：在匿名占位符我们一定要注意在数组中的顺序，在PDOStatement::execute() 方法中。

SELECT, INSERT, UPDATE, DELETE prepared queries

select

$stmt = $db->prepare("SELECT * FROM table WHERE id=:id AND name=:name");

$stmt->execute(array(':name' => $name, ':id' => $id));

$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

insert

$stmt = $db->prepare("INSERT INTO table(field1,field2) VALUES(:field1,:field2)");

$stmt->execute(array(':field1' => $field1, ':field2' => $field2));

$affected_rows = $stmt->rowCount();

delete

$stmt = $db->prepare("DELETE FROM table WHERE id=:id");

$stmt->bindValue(':id', $id, PDO::PARAM_STR);

$stmt->execute();

$affected_rows = $stmt->rowCount();

update

$stmt = $db->prepare("UPDATE table SET name=? WHERE id=?");

$stmt->execute(array($name, $id));

$affected_rows = $stmt->rowCount();

注意：

PDO和Mysqli也不是完全的安全。有机会会翻译一遍这个文章，这里就简单说一下：

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$pdo->query('SET NAMES GBK');

$stmt = $pdo->prepare("SELECT * FROM test WHERE name = ? LIMIT 1");

$stmt->execute(array(chr(0xbf) . chr(0x27) . " OR 1=1 /*"));

下面是一个封装的类

 class CPdo{

  protected $_dsn = "mysql:host=localhost;dbname=test";

  protected $_name = "root";

  protected $_pass = "";

  protected $_condition = array();

  protected $pdo;

  protected $fetchAll;

  protected $query;

  protected $result;

  protected $num;

  protected $mode;

  protected $prepare;

  protected $row;

  protected $fetchAction;

  protected $beginTransaction;

  protected $rollback;

  protected $commit;

  protected $char;

  private static $get_mode;

  private static $get_fetch_action;

  /**

  *pdo construct

  */

  public function __construct($pconnect = false) {

   $this->_condition = array(PDO::ATTR_PERSISTENT => $pconnect);

   $this->pdo_connect();

  }

  /**

  *pdo connect

  */

  private function pdo_connect() {

   try{

    $this->pdo = new PDO($this->_dsn,$this->_name,$this->_pass,$this->_condition);

   }

   catch(Exception $e) {

    return $this->setExceptionError($e->getMessage(), $e->getline, $e->getFile);

   }

  }

  /**

  *self sql get value action

  */

  public function getValueBySelfCreateSql($sql, $fetchAction = "assoc",$mode = null) {

   $this->fetchAction = $this->fetchAction($fetchAction);

   $this->result = $this->setAttribute($sql, $this->fetchAction, $mode);

   $this->AllValue = $this->result->fetchAll();

   return $this->AllValue;

  }

  /**

  *select condition can query

  */

  private function setAttribute($sql, $fetchAction, $mode) {

   $this->mode = self::getMode($mode);

   $this->fetchAction = self::fetchAction($fetchAction);

   $this->pdo->setAttribute(PDO::ATTR_CASE, $this->mode);

   $this->query = $this->base_query($sql);

   $this->query->setFetchMode($this->fetchAction);

   return $this->query;

  }

  /**

  *get mode action

  */

  private static function getMode($get_style){

   switch($get_style) {

    case null:

     self::$get_mode = PDO::CASE_NATURAL;

    break;

    case true:

     self::$get_mode = PDO::CASE_UPPER;

    break;

    case false;

    self::$get_mode= PDO::CASE_LOWER;

    break;

   }

   return self::$get_mode;

  }

  /**

  *fetch value action

  */

  private static function fetchAction($fetchAction) {

   switch($fetchAction) {

    case "assoc":

     self::$get_fetch_action = PDO::FETCH_ASSOC; //asso array

    break;

    case "num":

     self::$get_fetch_action = PDO::FETCH_NUM; //num array

    break;

    case "object":

     self::$get_fetch_action = PDO::FETCH_OBJ; //object array

    break;

    case "both":

     self::$get_fetch_action = PDO::FETCH_BOTH; //assoc array and num array

    break;

    default:

     self::$get_fetch_action = PDO::FETCH_ASSOC;

    break;

   }

   return self::$get_fetch_action;

  }

  /**

  *get total num action

  */

  public function rowCount($sql) {

   $this->result = $this->base_query($sql);

   $this->num = $this->result->rowCount();

   return $this->num;

  }

  /*

  *simple query and easy query action

  */

  public function query($table, $column = "*",$condition = array(), $group = "",$order = "", $having = "", $startSet = "",$endSet = "",$fetchAction = "assoc",$params = null){

   $sql = "select ".$column." from `".$table."` ";

   if ($condition != null) {

    foreach($condition as $key=>$value) {

     $where .= "$key = '$value' and ";

    }

    $sql .= "where $where";

    $sql .= "1 = 1 ";

   }

   if ($group != "") {

    $sql .= "group by ".$group." ";

   }

   if ($order != "") {

    $sql .= " order by ".$order." ";

   }

   if ($having != "") {

    $sql .= "having '$having' ";

   }

   if ($startSet != "" && $endSet != "" && is_numeric($endSet) && is_numeric($startSet)) {

    $sql .= "limit $startSet,$endSet";

   }

   $this->result = $this->getValueBySelfCreateSql($sql, $fetchAction, $params);

   return $this->result;

  }

  /**

  *execute delete update insert and so on action

  */

  public function exec($sql) {

   $this->result = $this->pdo->exec($sql);

   $substr = substr($sql, 0 ,6);

   if ($this->result) {

    return $this->successful($substr);

   } else {

    return $this->fail($substr);

   }

  }

  /**

  *prepare action

  */

  public function prepare($sql) {

   $this->prepare = $this->pdo->prepare($sql);

   $this->setChars();

   $this->prepare->execute();

   while($this->rowz = $this->prepare->fetch()) {

    return $this->row;

   }

  }

  /**

  *USE transaction

  */

  public function transaction($sql) {

   $this->begin();

   $this->result = $this->pdo->exec($sql);

   if ($this->result) {

    $this->commit();

   } else {

    $this->rollback();

   }

  }

  /**

  *start transaction

  */

  private function begin() {

   $this->beginTransaction = $this->pdo->beginTransaction();

   return $this->beginTransaction;

  }

  /**

  *commit transaction

  */

  private function commit() {

   $this->commit = $this->pdo->commit();

   return $this->commit;

  }

  /**

  *rollback transaction

  */

  private function rollback() {

   $this->rollback = $this->pdo->rollback();

   return $this->rollback;

  }

  /**

  *base query

  */

  private function base_query($sql) {

   $this->setChars();

   $this->query = $this->pdo->query($sql);

   return $this->query;

  }

  /**

  *set chars

  */

  private function setChars() {

   $this->char = $this->pdo->query("SET NAMES 'UTF8'");

   return $this->char;

  }

  /**

  *process sucessful action

  */

  private function successful($params){

   return "The ".$params." action is successful";

  }

  /**

  *process fail action

  */

  private function fail($params){

   return "The ".$params." action is fail";

  }

  /***进程异常动作

  *process exception action

  */

  private function setExceptionError($getMessage, $getLine ,$getFile) {

   echo "Error message is ".$getMessage."<br /> The Error in ".$getLine." line <br /> This file dir on ".$getFile;

   exit();

  }

 }

封装类2

 <?php

 /**

  */

 //使用pdo连接数据库 封装增删改查

 class DB{

 //定义私有属性

 private $host;

 private $port;

 private $username;

 private $password;

 private $dbname;

 private $charset;

 private $dbtype;

 private $pdo;

 //定义构造函数自动加载配置文件

 function __construct(){

 //加载配置文件

 include_once('./config/config.php');

 //给属性赋值

 $this->dbtype = $config['db'];

         $this->host  = $config['host'];

         $this->username = $config['username'];

         $this->password = $config['password'];

         $this->charset = $config['charset'];

         $this->port = $config['port'];

         $this->dbname = $config['dbname'];

 //pdo连接数据库

 $this->pdo = new PDO("$this->dbtype:host=$this->host;dbname=$this->dbname","$this->username","$this->password");

 //发送编码

 $this->pdo->query("set names $this->charset");

     }

 /**

      *   定义执行查询sql语句的方法

      *   参数： 查询sql语句

      *   返回： 二维关联数组

      */

 public function query($sql){

         $res = $this->pdo->query($sql);

         $res->setFetchMode(PDO::FETCH_ASSOC);

         $arr = $res->fetchAll();

 return $arr;

     }

 /**

      *   查询一行记录的方法

      *   参数：表名  条件(不包含where)

      *   返回：一维关联数组

      */

 public function getRow($tablename,$where){

 //组装sql语句

 $sql = "select * from $tablename where $where";

 //查询

 $res = $this->pdo->query($sql);

         $res->setFetchMode(PDO::FETCH_ASSOC);

         $arr = $res->fetch();

 return $arr;

     }

 /**

      *   查询全部记录

      *   参数：表名

      *   返回：二维关联数组

      */

 public function getAll($tablename){

         $res = $this->pdo->query("select * from $tablename");

         $res->setFetchMode(PDO::FETCH_ASSOC);

         $arr = $res->fetchAll();

 return $arr;

     }

 /**

      *   查询某个字段

      *   参数： 字段名(多个的话用逗号隔开) 表名 条件（不含where）

      *   返回： 二维关联数组

      */

 public function getOne($column,$tablename,$where="1"){

 //拼接sql语句

 $sql = "select $column from $tablename where $where";

         $rs = $this->pdo->query($sql);

         $rs->setFetchMode(PDO::FETCH_ASSOC);

 //$col = $rs->fetchColumn();

 $col = $rs->fetchAll();

 return  $col;

     }

 /**

      *   查询最后一次插入的数据

      *   参数：表名

      *   返回：数组

     */

 public function getlastone($tablename){

         $sql = "select * from $tablename where id=(select max(id) from $tablename)";

         $res = $this->pdo->query($sql);

         $res->setFetchMode(PDO::FETCH_ASSOC);

         $arr = $res->fetch();

 return $arr;

     }

 /**

      *  向数据库中添加一条信息

      *  参数：表名 一维关联数组

      *  返回: 布尔值

      */

 public function insert($tablename,$arr){

 //拿到数组之后先处理数组  过滤字段

         //取出表中的字段

 $sql = "select COLUMN_NAME from information_schema.COLUMNS where table_name = '$tablename' and table_schema ='$this->dbname'";

         $columns = $this->pdo->query($sql);

         $columns->setFetchMode(PDO::FETCH_ASSOC);

         $columns = $columns->fetchAll();

         $cols = array(); //存储表中的全部字段

 foreach($columns as $key=>$val){

             $cols[] = $val['COLUMN_NAME'];

         }

 //将要入库的数组进行键值分离

 $keys = array();

         $values = '';

 foreach($arr as $k=>$v){

 if(!in_array($k,$cols)){

 unset($arr[$k]);

             }else{

                 $keys[] = $k;

                 $values .= "'".$v."',";

             }

         }

         $column = implode(',',$keys);

         $values = substr($values,0,-1);

 //拼接sql语句

 $sql = "insert into $tablename($column) values ($values)";

         $res = $this->pdo->exec($sql);

 return $res;

     }

 /**

      *   删除数据 其实就是改变字段值使之不被查询

      *   参数：表名 条件(不含where)

      *   返回：布尔

     */

 public function delete($tablename,$where){

         $sql = "update $tablename set is_del=1 where $where";

         $res = $this->pdo->exec($sql);

 return $res;

     }

 /**

      *   修改数据

      *   参数：表名  要修改的数据的数组

      *   返回：布尔

     */

 public function update($tablename,$arr,$where){

 //处理传过来的数组

 $str = "";

 foreach($arras $k=>$v){

             $str .= "$k='".$v."',";

         }

 //截取字符串

 $str = substr($str,0,-1);

 //拼接sql语句

 $sql = "update $tablename set $str where $where";

         $res = $this->pdo->exec($sql);

 return $res;

     }

 }

码农公寓

Selecting Data

Fetch Modes

Insert and Update or Delete statements

Prepared Statements

SELECT, INSERT, UPDATE, DELETE prepared queries

相关文章