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);

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