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

发布:smiling　来源: PHP粉丝网　添加日期：2021-09-06 10:12:46　浏览: 评论：0　

这篇文章主要介绍了PHP实现基于PDO扩展连接PostgreSQL对象关系数据库,结合实例形式分析了php使用pdo连接PostgreSQL并执行简单sql语句的相关操作技巧,需要的朋友可以参考下。

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