php_pgsql 接口

1.簡介

php_pgsql 是 PostgreSQL 的 PHP 擴展，允許 PHP 腳本與 PostgreSQL 數(shù)據(jù)庫交互。

HighGo 數(shù)據(jù)庫提供了對 php_pgsql 特性的支持，并且支持連接認證方式為國密 SM3。

2.下載配置PHP 與 瀚高驅(qū)動

2.1.下載地址

鏈接：https://pan.baidu.com/s/1xuz6uJz0utRgKWecXhpOiA?pwd=o0tj

2.2.Windows 環(huán)境

  1. 前提條件，確保已安裝php對應(yīng)版本的 php_pgsql.dll 擴展。
  2. 下載對應(yīng)版本的libpq.dll,上傳位置為 PHP 安裝根目錄。
  3. 修改php.ini,去掉“extension=pgsql”前的分號。
  4. 終端執(zhí)行 php -m ,查看輸出列表是否有“pgsql”,如果有表示加載成功，沒有表示加載失敗

2.3.Linux 環(huán)境

  1. 下載對應(yīng)版本驅(qū)動
  2. 上傳服務(wù)器 /opt/highgolib 并解壓
Text
tar -zxvf v458_driver_for_php-7.4.33_x86_64.tar.gz
  1. 修改php.ini,引入擴展
Text
extension= /opt/highgolib/pgsql.so
  1. 終端執(zhí)行 php -m ,查看輸出列表是否有“pgsql”,如果有表示加載成功，沒有表示加載失敗

3.php_pgsql接口API

以下是重要的 PHP 例程，它們可以滿足您在 PHP 程序中使用 HighGo 數(shù)據(jù)庫的要求。 如果您正在尋找更復(fù)雜的應(yīng)用程序，那么您可以查看 PHP 官方文檔。

API和描述
resource pg_connect ( string $connection_string [, int $connect_type ] )
這將打開到由 connection_string 指定的 HighGo 數(shù)據(jù)庫的連接。
如果 PGSQL_CONNECT_FORCE_NEW 作為 connect_type 傳遞，那么在第二次調(diào)用 pg_connect() 時會創(chuàng)建一個新連接，即使 connection_string 與現(xiàn)有連接相同。
bool pg_connection_reset ( resource $connection )
此例程重置連接。 它對于錯誤恢復(fù)很有用。 成功時返回 TRUE，失敗時返回 FALSE。
int pg_connection_status ( resource $connection )
此例程返回指定連接的狀態(tài)。 返回 PGSQL_CONNECTION_OK 或 PGSQL_CONNECTION_BAD。
string pg_dbname ([ resource $connection ] )
此例程返回給定 HighGo 連接資源的數(shù)據(jù)庫的名稱。
resource pg_prepare ([ resource $connection ], string $stmtname, string $query )
這會提交一個請求以使用給定的參數(shù)創(chuàng)建一個準備好的語句并等待完成。
resource pg_execute ([ resource $connection ], string $stmtname, array $params )
該例程發(fā)送請求以執(zhí)行具有給定參數(shù)的準備好的語句并等待結(jié)果。
resource pg_query ([ resource $connection ], string $query )
該例程在指定的數(shù)據(jù)庫連接上執(zhí)行查詢。
array pg_fetch_row ( resource $result [, int $row ] )
此例程從與指定結(jié)果資源關(guān)聯(lián)的結(jié)果中獲取一行數(shù)據(jù)。
array pg_fetch_all ( resource $result )
此例程返回一個數(shù)組，其中包含結(jié)果資源中的所有行（記錄）。
int pg_affected_rows ( resource $result )
此例程返回受 INSERT、UPDATE 和 DELETE 查詢影響的行數(shù)。
int pg_num_rows ( resource $result )
此例程返回 HighGo 結(jié)果資源中的行數(shù)，例如 SELECT 語句返回的行數(shù)。
bool pg_close ([ resource $connection ] )
此例程關(guān)閉與給定連接資源關(guān)聯(lián)的 HighGo 數(shù)據(jù)庫的非持久連接。
string pg_last_error ([ resource $connection ] )
此例程返回給定連接的最后一條錯誤消息。
string pg_escape_literal ([ resource $connection ], string $data )
此例程轉(zhuǎn)義文字以插入文本字段。
string pg_escape_string ([ resource $connection ], string $data )
此例程轉(zhuǎn)義字符串以查詢數(shù)據(jù)庫。

4.數(shù)據(jù)庫操作

4.1.連接數(shù)據(jù)庫

以下 PHP 代碼顯示了如何連接到現(xiàn)有數(shù)據(jù)庫。 如果數(shù)據(jù)庫不存在，則創(chuàng)建它。最后將返回一個數(shù)據(jù)庫連接對象。

<?php
   $host        = "host = 127.0.0.1";
   $port        = "port = 5866";
   $dbname      = "dbname = testdb";
   $credentials = "user = sysdba password=pass123";

   $db = pg_connect( "$host $port $dbname $credentials"  );
   if(!$db) {
      echo "Error : Unable to open database\n";
   } else {
      echo "Opened database successfully\n";
   }
?>

現(xiàn)在，讓我們運行上面給出的程序來打開我們的數(shù)據(jù)庫 **testdb **：如果數(shù)據(jù)庫成功打開，那么它將給出以下消息 ?

Opened database successfully

4.2.建表操作

以下 PHP 程序?qū)⒂糜谠谙惹皠?chuàng)建的數(shù)據(jù)庫中創(chuàng)建表 ?

<?php
   $host        = "host = 127.0.0.1";
   $port        = "port = 5866";
   $dbname      = "dbname = testdb";
   $credentials = "user = sysdba password=pass123";

   $db = pg_connect( "$host $port $dbname $credentials"  );
   if(!$db) {
      echo "Error : Unable to open database\n";
   } else {
      echo "Opened database successfully\n";
   }
   
   $sql =<<<EOF
      CREATE TABLE COMPANY
      (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL);
EOF;

   $ret = pg_query($db, $sql);
   if(!$ret) {
      echo pg_last_error($db);
   } else {
      echo "Table created successfully\n";
   }
   pg_close($db);
?>

執(zhí)行上述程序時，它將在您的?testdb?中創(chuàng)建 COMPANY 表，并顯示以下消息 ?

Opened database successfully
Table created successfully

4.3.插入操作

以下 PHP 程序顯示了我們?nèi)绾卧谏厦媸纠袆?chuàng)建的 COMPANY 表中創(chuàng)建記錄 ?

<?php
   $host        = "host=127.0.0.1";
   $port        = "port=5866";
   $dbname      = "dbname = testdb";
   $credentials = "user = sysdba password=pass123";

   $db = pg_connect( "$host $port $dbname $credentials"  );
   if(!$db) {
      echo "Error : Unable to open database\n";
   } else {
      echo "Opened database successfully\n";
   }

   $sql =<<<EOF
      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (1, 'Paul', 32, 'California', 20000.00 );

      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 );

      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );

      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
EOF;

   $ret = pg_query($db, $sql);
   if(!$ret) {
      echo pg_last_error($db);
   } else {
      echo "Records created successfully\n";
   }
   pg_close($db);
?>

執(zhí)行上述給定程序時，它將在 COMPANY 表中創(chuàng)建給定記錄，并顯示以下兩行 ?

Opened database successfully
Records created successfully

4.4.查詢操作

以下 PHP 程序展示了我們?nèi)绾螐纳厦媸纠袆?chuàng)建的 COMPANY 表中獲取和顯示記錄 ?

<?php
   $host        = "host = 127.0.0.1";
   $port        = "port = 5866";
   $dbname      = "dbname = testdb";
   $credentials = "user = sysdba password=pass123";

   $db = pg_connect( "$host $port $dbname $credentials"  );
   if(!$db) {
      echo "Error : Unable to open database\n";
   } else {
      echo "Opened database successfully\n";
   }

   $sql =<<<EOF
      SELECT * from COMPANY;
EOF;

   $ret = pg_query($db, $sql);
   if(!$ret) {
      echo pg_last_error($db);
      exit;
   } 
   while($row = pg_fetch_row($ret)) {
      echo "ID = ". $row[0] . "\n";
      echo "NAME = ". $row[1] ."\n";
      echo "ADDRESS = ". $row[2] ."\n";
      echo "SALARY =  ".$row[4] ."\n\n";
   }
   echo "Operation done successfully\n";
   pg_close($db);
?>

執(zhí)行上述給定程序時，將產(chǎn)生以下結(jié)果。 請注意，字段按創(chuàng)建表時使用的順序返回。

Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  20000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

4.5.更新操作

以下 PHP 代碼顯示了我們?nèi)绾问褂?UPDATE 語句更新任何記錄，然后從我們的 COMPANY 表中獲取并顯示更新的記錄 ?

<?php
   $host        = "host=127.0.0.1";
   $port        = "port=5866";
   $dbname      = "dbname = testdb";
   $credentials = "user = sysdba password=pass123";

   $db = pg_connect( "$host $port $dbname $credentials"  );
   if(!$db) {
      echo "Error : Unable to open database\n";
   } else {
      echo "Opened database successfully\n";
   }
   $sql =<<<EOF
      UPDATE COMPANY set SALARY = 25000.00 where ID=1;
EOF;
   $ret = pg_query($db, $sql);
   if(!$ret) {
      echo pg_last_error($db);
      exit;
   } else {
      echo "Record updated successfully\n";
   }
   
   $sql =<<<EOF
      SELECT * from COMPANY;
EOF;

   $ret = pg_query($db, $sql);
   if(!$ret) {
      echo pg_last_error($db);
      exit;
   } 
   while($row = pg_fetch_row($ret)) {
      echo "ID = ". $row[0] . "\n";
      echo "NAME = ". $row[1] ."\n";
      echo "ADDRESS = ". $row[2] ."\n";
      echo "SALARY =  ".$row[4] ."\n\n";
   }
   echo "Operation done successfully\n";
   pg_close($db);
?>

執(zhí)行上述給定程序時，將產(chǎn)生以下結(jié)果 ?

Opened database successfully
Record updated successfully
ID = 2
NAME = Allen
ADDRESS = 25
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = 23
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = 25
SALARY =  65000

ID = 1
NAME = Paul
ADDRESS = 32
SALARY =  25000

Operation done successfully

4.6.刪除操作

以下 PHP 代碼顯示了我們?nèi)绾问褂?DELETE 語句刪除任何記錄，然后從我們的 COMPANY 表中獲取并顯示剩余的記錄 ?

<?php
   $host        = "host = 127.0.0.1";
   $port        = "port = 5866";
   $dbname      = "dbname = testdb";
   $credentials = "user = sysdba password=pass123";

   $db = pg_connect( "$host $port $dbname $credentials"  );
   if(!$db) {
      echo "Error : Unable to open database\n";
   } else {
      echo "Opened database successfully\n";
   }
   $sql =<<<EOF
      DELETE from COMPANY where ID=2;
EOF;
   $ret = pg_query($db, $sql);
   if(!$ret) {
      echo pg_last_error($db);
      exit;
   } else {
      echo "Record deleted successfully\n";
   }
   
   $sql =<<<EOF
      SELECT * from COMPANY;
EOF;

   $ret = pg_query($db, $sql);
   if(!$ret) {
      echo pg_last_error($db);
      exit;
   } 
   while($row = pg_fetch_row($ret)) {
      echo "ID = ". $row[0] . "\n";
      echo "NAME = ". $row[1] ."\n";
      echo "ADDRESS = ". $row[2] ."\n";
      echo "SALARY =  ".$row[4] ."\n\n";
   }
   echo "Operation done successfully\n";
   pg_close($db);
?>

執(zhí)行上述給定程序時，將產(chǎn)生以下結(jié)果 ?

Opened database successfully
Record deleted successfully
ID = 3
NAME = Teddy
ADDRESS = 23
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = 25
SALARY =  65000

ID = 1
NAME = Paul
ADDRESS = 32
SALARY =  25000

Operation done successfully