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

需要开启php的pdo支持,php5.1以上版本支持

实现数据库连接单例化,有三要素 静态变量、静态实例化方法、私有构造函数 DPDO.php

  class DPDO{    private $DSN;    private $DBUser;    private $DBPwd;    private $longLink;    private $pdo;    //私有构造函数 防止被直接实例化    private function __construct($dsn, $DBUser, $DBPwd, $longLink = false) {      $this->DSN = $dsn;      $this->DBUser = $DBUser;      $this->DBPwd = $DBPwd;      $this->longLink = $longLink;      $this->connect();    }    //私有 空克隆函数 防止被克隆    private function __clone(){}    //静态 实例化函数 返回一个pdo对象    static public function instance($dsn, $DBUser, $DBPwd, $longLink = false){      static $singleton = array();//静态函数 用于存储实例化对象      $singIndex = md5($dsn . $DBUser . $DBPwd . $longLink);      if (empty($singleton[$singIndex])) {        $singleton[$singIndex] = new self($dsn, $DBUser, $DBPwd, $longLink = false);      }      return $singleton[$singIndex]->pdo;    }         private function connect(){      try{        if($this->longLink){          $this->pdo = new PDO($this->DSN, $this->DBUser, $this->DBPwd, array(PDO::ATTR_PERSISTENT => true));        }else{          $this->pdo = new PDO($this->DSN, $this->DBUser, $this->DBPwd);        }        $this->pdo->query('SET NAMES UTF-8');      } catch(PDOException $e) {        die('Error:' . $e->getMessage() . '<br/>');      }    }  }  

用于处理字段映射,使用pdo的字段映射,可以有效避免sql注入

  //字段关联数组处理, 主要用于写入和更新数据、同and 或 or 的查询条件,产生sql语句和映射字段的数组    public function FDFields($data, $link = ',', $judge = array(), $aliasTable = ''){      $sql = '';      $mapData = array();      foreach($data as $key => $value) {        $mapIndex = ':' . ($link != ',' ? 'c' : '') . $aliasTable . $key;        $sql .= ' ' . ($aliasTable ? $aliasTable . '.' : '') . '`' . $key . '` ' . ($judge[$key] ? $judge[$key] : '=') . ' ' . $mapIndex . ' ' . $link;        $mapData[$mapIndex] = $value;      }      $sql = trim($sql, $link);      return array($sql, $mapData);    }    //用于处理单个字段处理    public function FDField($field, $value, $judge = '=', $preMap = 'cn', $aliasTable = '') {      $mapIndex = ':' . $preMap . $aliasTable . $field;      $sql = ' ' . ($aliasTable ? $aliasTable . '.' : '') . '`' . $field . '`' . $judge . $mapIndex;      $mapData[$mapIndex] = $value;      return array($sql, $mapData);    }    //使用刚方法可以便捷产生查询条件及对应数据数组    public function FDCondition($condition, $mapData) {      if(is_string($condition)) {          $where = $condition;      } else if (is_array($condition)) {        if($condition['str']) {          if (is_string($condition['str'])) {            $where = $condition['str'];          } else {            return false;          }        }        if(is_array($condition['data'])) {          $link = $condition['link'] ? $condition['link'] : 'and';          list($conSql, $mapConData) = $this->FDFields($condition['data'], $link, $condition['judge']);          if ($conSql) {            $where .= ($where ? ' ' . $link : '') . $conSql;            $mapData = array_merge($mapData, $mapConData);          }        }      }      return array($where, $mapData);    }  

增删改查的具体实现DB.php

  public function fetch($sql, $searchData = array(), $dataMode = PDO::FETCH_ASSOC, $preType = array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)) {      if ($sql) {        $sql .= ' limit 1';        $pdoStatement = $this->pdo->prepare($sql, $preType);        $pdoStatement->execute($searchData);        return $data = $pdoStatement->fetch($dataMode);      } else {        return false;      }    }         public function fetchAll($sql, $searchData = array(), $limit = array(0, 10), $dataMode = PDO::FETCH_ASSOC, $preType = array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY)) {      if ($sql) {        $sql .= ' limit ' . (int) $limit[0] . ',' . (intval($limit[1]) > 0 ? intval($limit[1]) : 10);        $pdoStatement = $this->pdo->prepare($sql, $preType);        $pdoStatement->execute($searchData);        return $data = $pdoStatement->fetchAll($dataMode);      } else {        return false;      }    }         public function insert($tableName, $data, $returnInsertId = false, $replace = false) {      if(!empty($tableName) && count($data) > 0){        $sql = $replace ? 'REPLACE INTO ' : 'INSERT INTO ';        list($setSql, $mapData) = $this->FDFields($data);        $sql .= $tableName . ' set ' . $setSql;        $pdoStatement = $this->pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));        $execRet = $pdoStatement->execute($mapData);        return $execRet ? ($returnInsertId ? $this->pdo->lastInsertId() : $execRet) : false;      } else {        return false;      }    }         public function update($tableName, $data, $condition, $mapData = array(), $returnRowCount = true) {      if(!empty($tableName) && count($data) > 0) {        $sql = 'UPDATE ' . $tableName . ' SET ';        list($setSql, $mapSetData) = $this->FDFields($data);        $sql .= $setSql;        $mapData = array_merge($mapData, $mapSetData);        list($where, $mapData) = $this->FDCondition($condition, $mapData);        $sql .= $where ? ' WHERE ' . $where : '';        $pdoStatement = $this->pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));        $execRet = $pdoStatement->execute($mapData);        return $execRet ? ($returnRowCount ? $pdoStatement->rowCount() : $execRet) : false;      } else {        return false;      }    }         public function delete($tableName, $condition, $mapData = array()) {      if(!empty($tableName) && $condition){        $sql = 'DELETE FROM ' . $tableName;        list($where, $mapData) = $this->FDCondition($condition, $mapData);        $sql .= $where ? ' WHERE ' . $where : '';        $pdoStatement = $this->pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));        $execRet = $pdoStatement->execute($mapData);        return $execRet;      }    }  

测试文件test.php

  header("Content-type: text/html; charset=utf-8");  define('APP_DIR', dirname(__FILE__));     if (function_exists('spl_autoload_register')) {    spl_autoload_register('autoClass');  } else {    function __auto_load($className){      autoClass($className);    }  }     function autoClass($className){    try{      require_once APP_DIR.'/class/'.$className.'.php';    } catch (Exception $e) {      die('Error:' . $e->getMessage() . '<br />');    }  }  $DB = new DB();  //插入  $inData['a'] = rand(1, 100);  $inData['b'] = rand(1, 1000);  $inData['c'] = rand(1,200) . '.' . rand(1,100);  $ret = $DB->insert('a', $inData);  echo '插入' . ($ret ? '成功' : '失败') . '<br/>';  //更新  $upConData['a'] = 100;  $upConJudge['a'] = '<';  $upConData['b'] = 30;  $upConJudge['b'] = '>';  list($upConStr, $mapUpConData) = $DB->FDField('b', 200, '<', 'gt');  $condition = array(    'str' => $upConStr,    'data' => $upConData,    'judge' => $upConJudge,    'link' => 'and'  );  $upData['a'] = rand(1, 10);  $upData['b'] = 1;  $upData['c'] = 1.00;  $changeRows = $DB->update('a', $upData, $condition, $mapUpConData);  echo '更新行数:' . (int) $changeRows . '<br/>';  //删除  $delVal = rand(1, 10);  list($delCon, $mapDelCon) = $DB->FDField('a', $delVal);  $delRet = $DB->delete('a', $delCon, $mapDelCon);  echo '删除a=' . $delVal . ($delRet ? '成功' : '失败') . '<br/>';     //查询  $data['a'] = '10';  $judge['a'] = '>';  $data['b'] = '400';  $judge['b'] = '<';  list($conSql, $mapConData) = $DB->FDFields($data, 'and', $judge);  $mData = $DB->fetch('select * from a where ' . $conSql . ' order by `a` desc', $mapConData);     var_dump($mData);

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

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

郑重声明:本网站发布的内容(图片、视频和文字)以及用户投稿、用户转载内容为主,如果涉及侵权请尽快告知,我们将会在第一时间删除。文章观点不代表本网站立场,如需处理请联系客服

发表评论

登录后才能评论