PHP基于pdo的数据库操作类

本文实例讲述了PHP基于pdo的数据库操作类。分享给大家供大家参考，具体如下：

工作中需要操作sqlserver、oracle都是使用的这个类，当时是在别人的基础上改进了，现在分享下

Config = $config;
    $this->connect();
  }
  
  public function connect(){
    try {
$this->pdo= new PDO($this->Config['dsn'], $this->Config['username'], $this->Config['password']);//$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
$this->pdo->query("set names utf8");
    }catch(Exception $e){
      echo '数据库连接失败,详情: ' . $e->getMessage () . ' 请在配置文件中数据库连接信息';
      exit ();
    }
    
    //把结果序列化成stdClass
    //$this->pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
    //自己写代码捕获Exception
    //$this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $this->pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);//属性名 属性值 数组以关联数组返回
  }
  
  public function close(){
    $this->pdo = null;
  }
  //用于有记录结果返回的操作，特别是SELECt操作
  public function query($sql,$return=false){
    $res = $this->pdo->query($sql);
    if($res){
      $this->res = $res; // 未返回 return $this->res;
    }
    if($return){
      return $res;
    }
  }
  //主要是针对没有结果集合返回的操作，比如INSERT、UPDATE、DELETE等操作
  public function exec($sql,$return=false){
    $res = $this->pdo->exec($sql);
    if($res){
      $this->res = $res;
    }
    if($return){//返回操作是否成功 成功返回1 失败0
      return $res;
    }
  }
  //将$this->res以数组返回(全部返回)
  public function fetchAll(){
    return $this->res->fetchAll();
  }
  //将$this->res以数组返回(一条记录)
  public function fetch(){
    return $this->res->fetch();
  }
  //返回所有字段
  public function fetchColumn(){
    return $this->res->fetchColumn();
  }
  //返回最后插入的id
  public function lastInsertId(){
    return $this->res->lastInsertId();
  }
  //返回最后插入的id
  public function lastInsertId2(){
    return $this->pdo->lastInsertId();
  }
  
  public function select($table, $fields="*", $sqlwhere="", $orderby="", $debug=0, $mode=0){
    //参数处理
    if(is_array($table)){
      $table = implode(', ', $table);
    }
    if(is_array($fields)){
      $fields = implode(',',$fields);
      
    }
    if(is_array($sqlwhere)){
      $sqlwhere = ' and '.implode(' and ', $sqlwhere);
    }
    //数据库操作
    if($debug === 0){
      if($mode === 2){ //统计
 $this->query("select count(*) from $table where 1=1 $sqlwhere");
 $return = $this->fetchColumn();
      }else if($mode === 1){ //返回一条
 $this->query("select $fields from $table where 1=1 $sqlwhere $orderby");
 $return = $this->fetch();
      }else{
 $this->query("select $fields from $table where 1=1 $sqlwhere $orderby");
 $return = $this->fetchAll();//如果 $this->res为空即sql语句错误 会提示Call to a member function fetchAll() on a non-object
      }
      return $return;
    }else{
 if($mode === 2){
   echo "select count(*) from $table where 1=1 $sqlwhere";
 }else if($mode === 1){
   echo "select $fields from $table where 1=1 $sqlwhere $orderby";
 }else{
   echo "select $fields from $table where 1=1 $sqlwhere $orderby";
 }
 if($debug === 2){
   exit;
 }
    }
  }
  
  public function oic_insert($table, $set, $debug=0, $mode=0){
    //参数处理
    if(is_array($table)){
      $table = implode(', ', $table);
    }
    if(is_array($set)){
      $s='';$i=0;
      foreach($set as $k=>$v){
 $i++;
 $s[$i]=$k;//,连接
 $val[$i]=$v;
      }
      $sarr=implode(",",$s);//去掉最后一个,
      //array_pop($sarr);
      $set=implode("','",$val);////15221579236','张三','','2001','8','4','女','是
      //$set = implode(', ', $set);
    }
    //数据库操作
    if($debug === 0){
      if($mode === 2){
 $this->query("insert into $table ($sarr) values('".$set."')");
 //$return = $this->lastInsertId();
      }else if($mode === 1){
 $this->exec("insert into $table ($sarr) values('".$set."')");
 $return = $this->res;
      }else{
 $this->query("insert into $table ($sarr) values('".$set."')");
 $return = NULL;
      }
      return $return;
    }else{
      echo "insert into $table ($sarr) values('".$set."')";
      if($debug === 2){
 exit;
      }
    }
  }
  public function insert($table, $set, $debug=0, $mode=0){
    //参数处理
    if(is_array($table)){
      $table = implode(', ', $table);
    }
    if(is_array($set)){
      $s='';
      foreach($set as $k=>$v){
 $s.=$k."='".$v."',";//,连接
      }
      $sarr=explode(',',$s);//去掉最后一个,
      array_pop($sarr);
      $set=implode(',',$sarr);
      //$set = implode(', ', $set);
    }
    //数据库操作
    if($debug === 0){
      if($mode === 2){
 $this->query("insert into $table set $set");
 $return = $this->pdo->lastInsertId();
      }else if($mode === 1){
 $this->exec("insert into $table set $set");
 $return = $this->res;
      }else{
 $this->query("insert into $table set $set");
 $return = NULL;
      }
      return $return;
    }else{
      echo "insert into $table set $set";
      if($debug === 2){
 exit;
      }
    }
  }
  
  public function update($table, $set, $sqlwhere="", $debug=0, $mode=0){
    //参数处理
    if(is_array($table)){
      $table = implode(', ', $table);
    }
    if(is_array($set)){
      $s='';
      foreach($set as $k=>$v){
 $s.=$k."='".$v."',";
      }
      $sarr=explode(',',$s);//去掉最后一个,
      array_pop($sarr);
      $set=implode(',',$sarr);
      //$set = implode(', ', $set);
    }
    if(is_array($sqlwhere)){
      $sqlwhere = ' and '.implode(' and ', $sqlwhere);
    }
    //数据库操作
    if($debug === 0){
      if($mode === 1){
 $this->exec("update $table set $set where 1=1 $sqlwhere");
 $return = $this->res;
      }else{
 $this->query("update $table set $set where 1=1 $sqlwhere");
 $return = true;
      }
      return $return;
    }else{
      echo "update $table set $set where 1=1 $sqlwhere";
      if($debug === 2){
 exit;
      }
    }
  }
  
  public function delete($table, $sqlwhere="", $debug=0, $mode=0){
    //参数处理
    if(is_array($sqlwhere)){
      $sqlwhere = ' and '.implode(' and ', $sqlwhere); //是字符串需自己加上and
    }
    //数据库操作
    if($debug === 0){
      if($mode === 1){
 $this->exec("delete from $table where 1=1 $sqlwhere");
 $return = $this->res;
      }else{
 $this->query("delete from $table where 1=1 $sqlwhere");
 $return = NULL;
      }
      return $return;
    }else{
      echo "delete from $table where 1=1 $sqlwhere";
      if($debug === 2){
 exit;
      }
    }
  }
}

$mssql2008_config=array(
  'dsn'=>'odbc:Driver={SQL Server};Server=192.168.1.60;Database=his',//数据库服务器地址
  'username'=>'sa',
  'password'=>'xxxxx',
);
$mssql=new Pdodb($mssql2008_config);
$sql="select * from
(
  select row_number()over(order by tempcolumn)temprownumber,*
    from (
      select top 10 tempcolumn=0,a.*
      from DA_GR_HBFS a
      where 1=1
    ) t
) tt
where temprownumber>0";
$mssql->query($sql);
while($res=$mssql->fetch()){
  $data[]=$res;
}
print_r($data);exit;
//mysql 操作
$msyql_config=array(
  'dsn'=>'mysql:host=localhost;dbname=talk',
  'username'=>'root',
  'password'=>'123456'
);
$mysql=new PDO_DB($msyql_config);
$sql = 'SELECt user_id, user_name, nickname FROM et_users ';
$mysql->query($sql);
$data=$mysql->fetchAll();
print_r($data);exit;
//oracle 操作
$oci_config=array(
  'dsn'=>'oci:dbname=orcl',
  'username'=>'BAOCRM',
  'password'=>'BAOCRM'
);
$oracle=new PDO_DB($oci_config);
//print_r($oracle);exit;//PDO_DB Object ( [pdo:protected] => PDO Object ( ) [res:protected] => [config:protected] => [Config] => Array ( [dsn] => oci:dbname=orcl [name] => PWACRM [password] => PWACRM ) )
$sql="select * from CUSTOMER_LEVEL t";
$oracle->query($sql);
$data=$oracle->fetchAll();
print_r($data);exit;

?>

更多关于PHP相关内容感兴趣的读者可查看本站专题：《PHP基于pdo操作数据库技巧总结》、《php+Oracle数据库程序设计技巧总结》、《PHP+MongoDB数据库操作技巧大全》、《php面向对象程序设计入门教程》、《php字符串(string)用法总结》、《php+mysql数据库操作入门教程》及《php常见数据库操作技巧汇总》

希望本文所述对大家PHP程序设计有所帮助。