PostgreSQL連接PHP
安裝
PostgreSQL的擴展是默認啟用的最新版本的PHP5.3.x.但是它可能通過在編譯時加上--without-pgsql 選項禁用。但仍然可以使用yum命令安裝PHP的PostgreSQL的接口:
yum install php-pgsql
在開始使用PHP PostgreSQL接口,打開pg_hba.conf文件(在PostgreSQL安裝目錄),並添加下麵一行:
# IPv4 local connections: host all all 127.0.0.1/32 md5
使用start/restart啟動Postgres服務器,它冇有運行使用下麵的命令:
[root@host]# service postgresql restart Stopping postgresql service: [ OK ] Starting postgresql service: [ OK ]
Windows用戶必須啟用php_pgsql.dll為了使用這個擴展。這個DLL包含帶Windows分布在最新版本PHP5.3.x
有關詳細的安裝說明,請查看我們的PHP教學和其官方網站上。
PHP 接口APIs
以下是重要的PHP程序,可以連接使用PostgreSQL數據庫PHP程序。如果正在尋找一個更複雜的應用程序,那麼可以看看到PHP官方文件。
S.N. | API & 描述 |
---|---|
1 |
resource pg_connect ( string $connection_string [, int $connect_type ] ) 這將打開一個連接到PostgreSQL數據庫由connection_string指定。 如果PGSQL_CONNECT_FORCE_NEW會傳遞為connect_type,然後在第二個調用的情況下,對pg_connect()創建一個新的連接,即使connection_string一個現有的連接是相同的。 |
2 |
bool pg_connection_reset ( resource $connection ) 此例程重置連接。它是有用的錯誤恢複。成功返回TRUE或失敗則返回FALSE。 |
3 |
int pg_connection_status ( resource $connection ) 這個例程返回指定的連接狀態。返回PGSQL_CONNECTION_OK或PGSQL_CONNECTION_BAD。 |
4 |
string pg_dbname ([ resource $connection ] ) 這個例程返回給定的PostgreSQL數據庫連接資源的名稱。 |
5 |
resource pg_prepare ([ resource $connection ], string $stmtname , string $query ) 提交請求以創建具有給定參數的準備好的語句,並等待完成。 |
6 |
resource pg_execute ([ resource $connection ], string $stmtname , array $params ) 這個程序發送一個請求給定的參數執行準備好的語句,並等待結果。 |
7 |
resource pg_query ([ resource $connection ], string $query ) 此程序執行查詢指定的數據庫連接。 |
8 |
array pg_fetch_row ( resource $result [, int $row ] ) 該例程將指定的結果的資源相關聯的,數據從結果中獲取一個行。 |
9 |
array pg_fetch_all ( resource $result ) 這個例程返回一個數組,包含所有行(記錄),結果資源。 |
10 |
int pg_affected_rows ( resource $result ) 這個例程返回INSERT,UPDATE和DELETE查詢受到影響的行數。 |
11 |
int pg_num_rows ( resource $result ) 這個例程返回的行數,例如SELECT語句返回的行數在一個PostgreSQL結果資源。 |
12 |
bool pg_close ([ resource $connection ] ) 該例程關閉非持久連接到一個給定的連接資源相關的PostgreSQL數據庫。 |
13 |
string pg_last_error ([ resource $connection ] ) 這個例程返回的最後一個錯誤消息,對於一個給定的連接。 |
14 |
string pg_escape_literal ([ resource $connection ], string $data ) 此例程轉義文字插入一個文本字段。 |
15 |
string pg_escape_string ([ resource $connection ], string $data ) 這例程轉義一個字符串查詢數據庫。 |
連接到數據庫
下麵的PHP代碼顯示了如何連接到一個現有的在本地機器上的數據庫,最後將返回一個數據庫連接對象。
<?php $host = "host=127.0.0.1"; $port = "port=5432"; $dbname = "dbname=testdb"; $credentials = "user=postgres password=pass123"; $db = pg_connect( "$host $port $dbname $credentials" ); if(!$db){ echo "Error : Unable to open database "; } else { echo "Opened database successfully "; } ?>
現在讓我們運行上麵的程序來打開數據庫testdb,如果成功打開數據庫,然後它會給下麵的消息:
Opened database successfully
創建表
下麵的PHP程序將使用以前創建的數據庫中創建一個表:
<?php $host = "host=127.0.0.1"; $port = "port=5432"; $dbname = "dbname=testdb"; $credentials = "user=postgres password=pass123"; $db = pg_connect( "$host $port $dbname $credentials" ); if(!$db){ echo "Error : Unable to open database "; } else { echo "Opened database successfully "; } $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 "; } pg_close($db); ?>
上述程序執行時,它會創建表COMPANY在數據庫testdb 中 ,它會顯示以下消息:
Opened database successfully Table created successfully
INSERT操作
PHP程序,顯示我們如何創建表COMPANY 在上麵的例子中,創建表中的記錄:
<?php $host = "host=127.0.0.1"; $port = "port=5432"; $dbname = "dbname=testdb"; $credentials = "user=postgres password=pass123"; $db = pg_connect( "$host $port $dbname $credentials" ); if(!$db){ echo "Error : Unable to open database "; } else { echo "Opened database successfully "; } $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 "; } pg_close($db); ?>
上述程序執行時,它會創建COMPANY表中的記錄,並會顯示以下兩行:
Opened database successfully Records created successfully
SELECT 操作
PHP程序表明我們如何獲取並顯示COMPANY 在上麵的例子中創建表的記錄:
<?php $host = "host=127.0.0.1"; $port = "port=5432"; $dbname = "dbname=testdb"; $credentials = "user=postgres password=pass123"; $db = pg_connect( "$host $port $dbname $credentials" ); if(!$db){ echo "Error : Unable to open database "; } else { echo "Opened database successfully "; } $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] . " "; echo "NAME = ". $row[1] ." "; echo "ADDRESS = ". $row[2] ." "; echo "SALARY = ".$row[4] ." "; } echo "Operation done successfully "; pg_close($db); ?>
當上述程序執行時,它會產生以下結果。字段返回序列中,它們被用來創建表的同時保持注意。
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
UPDATE 操作
下麵的PHP代碼顯示了如何我們可以使用UPDATE語句來更新任何記錄,然後獲取並顯示更新的記錄,從COMPANY 表:
<?php $host = "host=127.0.0.1"; $port = "port=5432"; $dbname = "dbname=testdb"; $credentials = "user=postgres password=pass123"; $db = pg_connect( "$host $port $dbname $credentials" ); if(!$db){ echo "Error : Unable to open database "; } else { echo "Opened database successfully "; } $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 "; } $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] . " "; echo "NAME = ". $row[1] ." "; echo "ADDRESS = ". $row[2] ." "; echo "SALARY = ".$row[4] ." "; } echo "Operation done successfully "; pg_close($db); ?>
當上述程序執行時,它會產生以下結果:
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
DELETE 操作
下麵的PHP代碼,表明我們如何能夠使用DELETE語句刪除任何記錄,然後獲取並顯示COMPANY 表剩餘的記錄:
<?php $host = "host=127.0.0.1"; $port = "port=5432"; $dbname = "dbname=testdb"; $credentials = "user=postgres password=pass123"; $db = pg_connect( "$host $port $dbname $credentials" ); if(!$db){ echo "Error : Unable to open database "; } else { echo "Opened database successfully "; } $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 "; } $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] . " "; echo "NAME = ". $row[1] ." "; echo "ADDRESS = ". $row[2] ." "; echo "SALARY = ".$row[4] ." "; } echo "Operation done successfully "; pg_close($db); ?>
當上述程序執行時,它會產生以下結果:
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