PHP实现基于PDO扩展连接PostgreSQL对象关系数据库示例

$pdo = null;
if(version_compare(php_version, '5.3.6', '<')){
  $pdo = new pdo('pgsql:host=127.0.0.1;port=5432;dbname=postgredb1','postgres',"123456",array(pdo::mysql_attr_init_command=>'set names 'utf8'' ));
}
else{
  $pdo = new pdo('pgsql:host=127.0.0.1;port=5432;dbname=postgredb1','postgres',"123456");
}
try {
  $pdo->begintransaction();
  $tablename = 'user';
  if($fetch = true){
    $mypdostatement = $pdo->prepare("select * from " . $tablename . " where id=:id ");
    if(!$mypdostatement) {
      $errorinfo = $mypdostatement->errorinfo();
      throw new exception($errorinfo[0].'###'.$errorinfo[1].'###'.$errorinfo[2]);
    }
    $id = 1;
    $mypdostatement->bindparam(":id",$id);
    $mypdostatement->execute();
    if($mypdostatement->errorcode()>0){
      $errorinfo = $mypdostatement->errorinfo();
      throw new exception($errorinfo[0].'###'.$errorinfo[1].'###'.$errorinfo[2]);
    }
    $item = $mypdostatement->fetch();
    print_r($item);
  }
  $insertedid = 0;
  if($insert = true){
    $mypdostatement = $pdo->prepare("insert into " . $tablename . "(username,password,status)  values(:username,:password,:status)");
    if(!$mypdostatement) {
      $errorinfo = $mypdostatement->errorinfo();
      throw new exception($errorinfo[0].'###'.$errorinfo[1].'###'.$errorinfo[2]);
    }
    $timestamp = time();
    $data = array(
      'username' =>'usernamex',
      'password' =>'passwordx',
      'status' =>'1',
    );
    $mypdostatement->bindparam(":username",$data['username']);
    $mypdostatement->bindparam(":password",$data['password']);
    $mypdostatement->bindparam(":status",$data['status']);
    $mypdostatement->execute();
    if($mypdostatement->errorcode()>0){
      $errorinfo = $mypdostatement->errorinfo();
      throw new exception($errorinfo[0].'###'.$errorinfo[1].'###'.$errorinfo[2]);
    }
    $affectrowcount = $mypdostatement->rowcount();
    if($affectrowcount>0){
      $insertedid = $pdo->lastinsertid();
    }
    print_r('$insertedid = '.$insertedid);//postgresql不支持
    print_r('$affectrowcount = '.$affectrowcount);
  }
  if($update = true){
    $mypdostatement = $pdo->prepare("update " . $tablename . " set username=:username, status=:status where id=:id");
    if(!$mypdostatement) {
      $errorinfo = $mypdostatement->errorinfo();
      throw new exception($errorinfo[0].'###'.$errorinfo[1].'###'.$errorinfo[2]);
    }
    $id = 1;
    $username = 'username update';
    $status = 0;
    $mypdostatement->bindparam(":id",$id);
    $mypdostatement->bindparam(":username",$username);
    $mypdostatement->bindparam(":status",$status);
    $mypdostatement->execute();
    if($mypdostatement->errorcode()>0){
      $errorinfo = $mypdostatement->errorinfo();
      throw new exception($errorinfo[0].'###'.$errorinfo[1].'###'.$errorinfo[2]);
    }
    $affectrowcount = $mypdostatement->rowcount();
    print_r('$affectrowcount = '.$affectrowcount);
  }
  if($fetchall = true){
    $mypdostatement = $pdo->prepare("select * from " . $tablename ." where id > :id");
    if(!$mypdostatement) {
      $errorinfo = $mypdostatement->errorinfo();
      throw new exception($errorinfo[0].'###'.$errorinfo[1].'###'.$errorinfo[2]);
    }
    $id = 0;
    $mypdostatement->bindparam(":id",$id);
    $mypdostatement->execute();
    if($mypdostatement->errorcode()>0){
      $errorinfo = $mypdostatement->errorinfo();
      throw new exception($errorinfo[0].'###'.$errorinfo[1].'###'.$errorinfo[2]);
    }
    $list = $mypdostatement->fetchall();
    print_r($list);
  }
  if($update = true){
    $mypdostatement = $pdo->prepare("delete from " . $tablename . " where id=:id");
    if(!$mypdostatement) {
      $errorinfo = $mypdostatement->errorinfo();
      throw new exception($errorinfo[0].'###'.$errorinfo[1].'###'.$errorinfo[2]);
    }
    //$insertedid = 10;
    $mypdostatement->bindparam(":id",$insertedid);
    $mypdostatement->execute();
    if($mypdostatement->errorcode()>0){
      $errorinfo = $mypdostatement->errorinfo();
      throw new exception($errorinfo[0].'###'.$errorinfo[1].'###'.$errorinfo[2]);
    }
    $affectrowcount = $mypdostatement->rowcount();
    print_r('$affectrowcount = '.$affectrowcount);
  }
  $pdo->commit();
} catch (exception $e) {
  $pdo->rollback();
//     print_r($e);
}
$pdo = null;