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

本文实例讲述了PHP实现基于PDO扩展连接PostgreSQL对象关系数据库的方法。分享给大家供大家

本文实例讲述了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;