Basic PostgreSQL Tutorial

Advanced PostgreSQL Tutorial

PostgreSQL Interface

PostgreSQL PHP Interface

install

latest version of PHP 5.3. x The PostgreSQL extension is enabled by default. It can be used at compile time to -- without-To disable it, you can still use the yum command to install PHP-PostgreSQL Interface

yum install php-pgsql

Before using the PHP PostgreSQL interface, find the pg_hba.conf file in the PostgreSQL installation directory and add the following line

# IPv4　local connections:
host all all all　　　　　　　　　127.0.0.1/32　　　　　　　　　　md5

If the postgres server is not running, you can use the following command to start it/Restarting postgres server-

[root@host]# service postgresql restart
Stopping postgresql service: [OK]
Starting postgresql service:　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　[　　OK　　]

Windows users must enable php_pgsql.dll to use this extension. This DLL is included in PHP5.3version of Windows after .x

For detailed installation instructions, please refer to ourPHP Tutorialand its official website.

PHP Interface APIs

Below are some important PHP routines that can meet your needs for using PostgreSQL databases in PHP programs. If you are looking for a more complex application, you can refer to the PHP official documentation.

S. No.	API & Description
1	resource pg_connect ( string $connection_string [, int $connect_type ] ) This will open a connection to the PostgreSQL database specified by connection_string. If PGSQL_CONNECT_FORCE_NEW is passed as connect_type, a new connection will be created on the second call to pg_connect(), even if the connection_string is the same as an existing connection.
2	bool pg_connection_reset ( resource $connection ) This routine resets the connection. It is very useful for error recovery. Returns TRUE on success, FALSE on failure.
3	int pg_connection_status ( resource $connection ) Returns the status of the specified connection. Returns PGSQL_CONNECTION_OK or PGSQL_CONNECTION_BAD.
4	string pg_dbname ([ resource $connection ] ) Returns the name of the database of the given PostgreSQL connection resource.
5	resource pg_prepare ([ resource $connection ], string $stmtname, string $query ) Submit a request in this way, using the given something to create a prepared statement and wait for completion.
6	resource pg_execute ([ resource $connection ], string $stmtname, array $params ) This routine sends a request to execute a previously prepared statement with the given parameters, waiting for the result.
7	resource pg_query ([ resource $connection ], string $query ) Execute a query on the specified database connection.
8	array pg_fetch_row ( resource $result [, int $row ] ) Get a row of data from the result associated with the specified result resource.
9	array pg_fetch_all ( resource $result ) Return an array containing all rows (records) in the result resource.
10	int pg_affected_rows ( resource $result ) Return the number of rows affected by an INSERT, UPDATE, and DELETE query.
11	int pg_num_rows ( resource $result ) Return the number of rows in the PostgreSQL result resource, for example, the number of rows returned by a SELECT statement.
12	bool pg_close ([ resource $connection ] ) Close the non-persistent connection associated with the PostgreSQL database for the given connection resource.
13	string pg_last_error ([ resource $connection ] ) Return the last error message for the given connection.
14	string pg_escape_literal ([ resource $connection ], string $data ) Escape a text to be inserted into a text field.
15	string pg_escape_string ([ resource $connection ], string $data ) Escape the string used in the query of the database.

Connect to database

The following PHP code demonstrates how to connect to an existing database on the local computer and ultimately return a database connection object.

<?php
　　　$host = "host ="　127.0.0.1";
　　　$port = "port ="　5432";
　　　$dbname = "dbname = testdb";
　　　$credentials = "user = postgres password=pass"123";
　　　$db = pg_connect("$host $port $dbname $credentials" );
　　　if(!$db) {
　　　　　　echo "Error : Unable to open database\n";
　　　} else {
　　　　　　echo "Database opened successfully\n";
　　　}
?>

Now, let's run the above given program to open our database testdb: If the database is successfully opened, it will give the following message-

Database opened successfully

Create Table

The following PHP program will be used to create a table in the previously created database

<?php
　　　$host = "host ="　127.0.0.1";
　　　$port = "port ="　5432";
　　　$dbname = "dbname = testdb";
　　　$credentials = "user = postgres password=pass"123";
　　　$db = pg_connect("$host $port $dbname $credentials" );
　　　if(!$db) {
　　　　　　echo "Error : Unable to open database\n";
　　　} else {
　　　　　　echo "Database opened 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);
?>

When the above given program is executed, it will create the COMPANY table in testdb and display the following message-

Database opened successfully
Table created successfully

INSERT operation

The following PHP program demonstrates how to use the above example-Create records in the COMPANY table created in the database

<?php
　　　$host = "host=127.0.0.1";
　　　$port = "port=5432";
　　　$dbname = "dbname = testdb";
　　　$credentials = "user = postgres password=pass"123";
　　　$db = pg_connect("$host $port $dbname $credentials" );
　　　if(!$db) {
　　　　　　echo "Error : Unable to open database\n";
　　　} else {
　　　　　　echo "Database opened 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);
?>

When the above given program is executed, it will create the given records in the COMPANY table and display the following two lines

Database opened successfully
Records created successfully

SELECT operation

The following PHP program demonstrates how to use the above example-Retrieve and display records from the COMPANY table created in the database

<?php
　　　$host = "host ="　127.0.0.1";
　　　$port = "port ="　5432";
　　　$dbname = "dbname = testdb";
　　　$credentials = "user = postgres password=pass"123";
　　　$db = pg_connect("$host $port $dbname $credentials" );
　　　if(!$db) {
　　　　　　echo "Error : Unable to open database\n";
　　　} else {
　　　　　　echo "Database opened 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);
?>

When the above program is executed, it will produce the following results. Please note that the fields will be returned in the order they were created when the table was created.

Database opened 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 operation

The following PHP code demonstrates how to use the UPDATE statement to update any record and then retrieve and display the updated record from our company table

<?php
　　　$host = "host=127.0.0.1";
　　　$port = "port=5432";
　　　$dbname = "dbname = testdb";
　　　$credentials = "user = postgres password=pass"123";
　　　$db = pg_connect("$host $port $dbname $credentials" );
　　　if(!$db) {
　　　　　　echo "Error : Unable to open database\n";
　　　} else {
　　　　　　echo "Database opened 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);
?>

When the above program is executed, the following results will be produced

Database opened 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 operation

The following PHP code demonstrates how to use the DELETE statement to delete any record and then retrieve and display the record from the COMPANY table-Retrieve and display the remaining records

<?php
　　　$host = "host ="　127.0.0.1";
　　　$port = "port ="　5432";
　　　$dbname = "dbname = testdb";
　　　$credentials = "user = postgres password=pass"123";
　　　$db = pg_connect("$host $port $dbname $credentials" );
　　　if(!$db) {
　　　　　　echo "Error : Unable to open database\n";
　　　} else {
　　　　　　echo "Database opened 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);
?>

When the above program is executed, the following results will be produced

Database opened 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

PostgreSQL Perl PostgreSQL JAVA