当前位置：Gxlcms > 数据库问题 > [实例]php中PDO方式实现数据库的增删改查

[实例]php中PDO方式实现数据库的增删改查

时间：2021-07-01 10:21:17 帮助过：22人阅读

整理的比较容易理解的PDO操作实例，注意，需要开启php的pdo支持，php5.1以上版本支持实现数据库连接单例化，有三要素 静态变量、静态实例化方法、私有构造函数 DPDO.php
  1. //PDO操作类
  2. //author http://www.lai18.com
  3. class DPDO{
  4.   private $DSN;
  5.   private $DBUser;
  6.   private $DBPwd;
  7.   private $longLink;
  8.   private $pdo;
  9.   //私有构造函数 防止被直接实例化
  10.   private function __construct($dsn, $DBUser, $DBPwd, $longLink = false) {
  11.     $this->DSN = $dsn;
  12.     $this->DBUser = $DBUser;
  13.     $this->DBPwd = $DBPwd;
  14.     $this->longLink = $longLink;
  15.     $this->connect();
  16.   }
  17.   //私有 空克隆函数 防止被克隆
  18.   private function __clone(){}
  19.   //静态 实例化函数 返回一个pdo对象
  20.   static public function instance($dsn, $DBUser, $DBPwd, $longLink = false){
  21.     static $singleton = array();//静态函数 用于存储实例化对象
  22.     $singIndex = md5($dsn . $DBUser . $DBPwd . $longLink);
  23.     if (empty($singleton[$singIndex])) {
  24.       $singleton[$singIndex] = new self($dsn, $DBUser, $DBPwd, $longLink = false);
  25.     }
  26.     return $singleton[$singIndex]->pdo;
  27.   }
  28.    
  29.   private function connect(){
  30.     try{
  31.       if($this->longLink){
  32.         $this->pdo = new PDO($this->DSN, $this->DBUser, $this->DBPwd, array(PDO::ATTR_PERSISTENT => true));
  33.       }else{
  34.         $this->pdo = new PDO($this->DSN, $this->DBUser, $this->DBPwd);
  35.       }
  36.       $this->pdo->query(‘SET NAMES UTF-8‘);
  37.     } catch(PDOException $e) {
  38.       die(‘Error:‘ . $e->getMessage() . ‘<br/>‘);
  39.     }
  40.   }
  41. }


用于处理字段映射，使用pdo的字段映射，可以有效避免sql注入
  1. //字段关联数组处理， 主要用于写入和更新数据、同and 或 or 的查询条件，产生sql语句和映射字段的数组
  2. //author http://www.lai18.com
  3.   public function FDFields($data, $link = ‘,‘, $judge = array(), $aliasTable = ‘‘){
  4.     $sql = ‘‘;
  5.     $mapData = array();
  6.     foreach($data as $key => $value) {
  7.       $mapIndex = ‘:‘ . ($link != ‘,‘ ? ‘c‘ : ‘‘) . $aliasTable . $key;
  8.       $sql .= ‘ ‘ . ($aliasTable ? $aliasTable . ‘.‘ : ‘‘) . ‘`‘ . $key . ‘` ‘ . ($judge[$key] ? $judge[$key] : ‘=‘) . ‘ ‘ . $mapIndex . ‘ ‘ . $link;
  9.       $mapData[$mapIndex] = $value;
  10.     }
  11.     $sql = trim($sql, $link);
  12.     return array($sql, $mapData);
  13.   }
  14.   //用于处理单个字段处理
  15.   public function FDField($field, $value, $judge = ‘=‘, $preMap = ‘cn‘, $aliasTable = ‘‘) {
  16.     $mapIndex = ‘:‘ . $preMap . $aliasTable . $field;
  17.     $sql = ‘ ‘ . ($aliasTable ? $aliasTable . ‘.‘ : ‘‘) . ‘`‘ . $field . ‘`‘ . $judge . $mapIndex;
  18.     $mapData[$mapIndex] = $value;
  19.     return array($sql, $mapData);
  20.   }
  21.   //使用刚方法可以便捷产生查询条件及对应数据数组
  22.   public function FDCondition($condition, $mapData) {
  23.     if(is_string($condition)) {
  24.         $where = $condition;
  25.     } else if (is_array($condition)) {
  26.       if($condition[‘str‘]) {
  27.         if (is_string($condition[‘str‘])) {
  28.           $where = $condition[‘str‘];
  29.         } else {
  30.           return false;
  31.         }
  32.       }
  33.       if(is_array($condition[‘data‘])) {
  34.         $link = $condition[‘link‘] ? $condition[‘link‘] : ‘and‘;
  35.         list($conSql, $mapConData) = $this->FDFields($condition[‘data‘], $link, $condition[‘judge‘]);
  36.         if ($conSql) {
  37.           $where .= ($where ? ‘ ‘ . $link : ‘‘) . $conSql;
  38.           $mapData = array_merge($mapData, $mapConData);
  39.         }
  40.       }
  41.     }
  42.     return array($where, $mapData);
  43.   }


增删改查的具体实现DB.php
  1. //数据库的遍历
  2. //author http://www.lai18.com
  3. public function fetch($sql, $searchData = array(), $dataMode = PDO::FETCH_ASSOC, $preType = array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)) {
  4.     if ($sql) {
  5.       $sql .= ‘ limit 1‘;
  6.       $pdoStatement = $this->pdo->prepare($sql, $preType);
  7.       $pdoStatement->execute($searchData);
  8.       return $data = $pdoStatement->fetch($dataMode);
  9.     } else {
  10.       return false;
  11.     }
  12.   }
  13.    
  14.   public function fetchAll($sql, $searchData = array(), $limit = array(0, 10), $dataMode = PDO::FETCH_ASSOC, $preType = array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)) {
  15.     if ($sql) {
  16.       $sql .= ‘ limit ‘ . (int) $limit[0] . ‘,‘ . (intval($limit[1]) > 0 ? intval($limit[1]) : 10);
  17.       $pdoStatement = $this->pdo->prepare($sql, $preType);
  18.       $pdoStatement->execute($searchData);
  19.       return $data = $pdoStatement->fetchAll($dataMode);
  20.     } else {
  21.       return false;
  22.     }
  23.   }
  24.    
  25.   public function insert($tableName, $data, $returnInsertId = false, $replace = false) {
  26.     if(!empty($tableName) && count($data) > 0){
  27.       $sql = $replace ? ‘REPLACE INTO ‘ : ‘INSERT INTO ‘;
  28.       list($setSql, $mapData) = $this->FDFields($data);
  29.       $sql .= $tableName . ‘ set ‘ . $setSql;
  30.       $pdoStatement = $this->pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
  31.       $execRet = $pdoStatement->execute($mapData);
  32.       return $execRet ? ($returnInsertId ? $this->pdo->lastInsertId() : $execRet) : false;
  33.     } else {
  34.       return false;
  35.     }
  36.   }
  37.    
  38.   public function update($tableName, $data, $condition, $mapData = array(), $returnRowCount = true) {
  39.     if(!empty($tableName) && count($data) > 0) {
  40.       $sql = ‘UPDATE ‘ . $tableName . ‘ SET ‘;
  41.       list($setSql, $mapSetData) = $this->FDFields($data);
  42.       $sql .= $setSql;
  43.       $mapData = array_merge($mapData, $mapSetData);
  44.       list($where, $mapData) = $this->FDCondition($condition, $mapData);
  45.       $sql .= $where ? ‘ WHERE ‘ . $where : ‘‘;
  46.       $pdoStatement = $this->pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
  47.       $execRet = $pdoStatement->execute($mapData);
  48.       return $execRet ? ($returnRowCount ? $pdoStatement->rowCount() : $execRet) : false;
  49.     } else {
  50.       return false;
  51.     }
  52.   }
  53.    
  54.   public function delete($tableName, $condition, $mapData = array()) {
  55.     if(!empty($tableName) && $condition){
  56.       $sql = ‘DELETE FROM ‘ . $tableName;
  57.       list($where, $mapData) = $this->FDCondition($condition, $mapData);
  58.       $sql .= $where ? ‘ WHERE ‘ . $where : ‘‘;
  59.       $pdoStatement = $this->pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
  60.       $execRet = $pdoStatement->execute($mapData);
  61.       return $execRet;
  62.     }
  63.   }


测试文件test.php
  1. //PDO操作类-测试PHP示例
  2. //author http://www.lai18.com
  3. header("Content-type: text/html; charset=utf-8");
  4. define(‘APP_DIR‘, dirname(__FILE__));
  5.  
  6. if (function_exists(‘spl_autoload_register‘)) {
  7.   spl_autoload_register(‘autoClass‘);
  8. } else {
  9.   function __auto_load($className){
  10.     autoClass($className);
  11.   }
  12. }
  13.  
  14. function autoClass($className){
  15.   try{
  16.     require_once APP_DIR.‘/class/‘.$className.‘.php‘;
  17.   } catch (Exception $e) {
  18.     die(‘Error:‘ . $e->getMessage() . ‘<br />‘);
  19.   }
  20. }
  21. $DB = new DB();
  22. //插入
  23. $inData[‘a‘] = rand(1, 100);
  24. $inData[‘b‘] = rand(1, 1000);
  25. $inData[‘c‘] = rand(1,200) . ‘.‘ . rand(1,100);
  26. $ret = $DB->insert(‘a‘, $inData);
  27. echo ‘插入‘ . ($ret ? ‘成功‘ : ‘失败‘) . ‘<br/>‘;
  28. //更新
  29. $upConData[‘a‘] = 100;
  30. $upConJudge[‘a‘] = ‘<‘;
  31. $upConData[‘b‘] = 30;
  32. $upConJudge[‘b‘] = ‘>‘;
  33. list($upConStr, $mapUpConData) = $DB->FDField(‘b‘, 200, ‘<‘, ‘gt‘);
  34. $condition = array(
  35.   ‘str‘ => $upConStr,
  36.   ‘data‘ => $upConData,
  37.   ‘judge‘ => $upConJudge,
  38.   ‘link‘ => ‘and‘
  39. );
  40. $upData[‘a‘] = rand(1, 10);
  41. $upData[‘b‘] = 1;
  42. $upData[‘c‘] = 1.00;
  43. $changeRows = $DB->update(‘a‘, $upData, $condition, $mapUpConData);
  44. echo ‘更新行数:‘ . (int) $changeRows . ‘<br/>‘;
  45. //删除
  46. $delVal = rand(1, 10);
  47. list($delCon, $mapDelCon) = $DB->FDField(‘a‘, $delVal);
  48. $delRet = $DB->delete(‘a‘, $delCon, $mapDelCon);
  49. echo ‘删除a=‘ . $delVal . ($delRet ? ‘成功‘ : ‘失败‘) . ‘<br/>‘;
  50.  
  51. //查询
  52. $data[‘a‘] = ‘10‘;
  53. $judge[‘a‘] = ‘>‘;
  54. $data[‘b‘] = ‘400‘;
  55. $judge[‘b‘] = ‘<‘;
  56. list($conSql, $mapConData) = $DB->FDFields($data, ‘and‘, $judge);
  57. $mData = $DB->fetch(‘select * from a where ‘ . $conSql . ‘ order by `a` desc‘, $mapConData);
  58.  
  59. var_dump($mData);


以上所述就是本文的全部内容了，希望大家能够喜欢。 

参考来源： 
php中PDO方式实现数据库的增删改查
http://www.lai18.com/content/422492.html

[实例]php中PDO方式实现数据库的增删改查

标签：php   pdo   数据库   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！