[執行個體]php中PDO方式實現資料庫的增刪改查

標籤：php   pdo   資料庫   

整理的比較容易理解的PDO操作執行個體，注意，需要開啟php的pdo支援，php5.1以上版本支援實現資料庫連接單例化，有三要素 靜態變數、靜態執行個體化方法、私人建構函式 DPDO.php

//PDO操作類//author http://www.lai18.comclass 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語句和映射欄位的數組//author http://www.lai18.com  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

//資料庫的遍曆//author http://www.lai18.compublic 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

//PDO操作類-測試PHP樣本//author http://www.lai18.comheader("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方式實現資料庫的增刪改查
http://www.lai18.com/content/422492.html

[執行個體]php中PDO方式實現資料庫的增刪改查