Postgresql - PHP Interface

POSTGRESQL - PHP INTERFACE
Copyright tutorialspoint.com
http://www.tutorialspoint.com/postgresql/postgresql_php.htm
Installation
The PostgreSQL extension is enabled by default in the latest releases of PHP 5.3.x. It's possible to
disable it by using --without-pgsql at compile time. Still you can use yum command to install PHP
-PostgreSQL interface:
yum install php-pgsql
Before you start using PHP PostgreSQL interface, find pg_hba.conf file in your PostgreSQL
installation directory and add the following line:
# IPv4 local connections:
host
all
all
127.0.0.1/32
md5
You can start/restart postgres server in case it is not running using the following command:
[root@host]# service postgresql restart
Stopping postgresql service:
Starting postgresql service:
[
[
OK
OK
]
]
Windows users must enable php_pgsql.dll in order to use this extension. This DLL is included with
Windows distributions in the latest releases of PHP 5.3.x
For detailed installation instructions, kindly check our PHP tutorial and its official website.
PHP Interface APIs

Following are important PHP routines which can suffice your requirement to work with PostgreSQL
database from your PHP program. If you are looking for a more sophisticated application, then you
can look into PHP official documentation.
S.N.
API & Description
resource pg_connect string$connectionstring[, int$connecttype]

This opens a connection to a PostgreSQL database specified by the connection_string.
If PGSQL_CONNECT_FORCE_NEW is passed as connect_type, then a new connection is
created in case of a second call to pg_connect , even if the connection_string is identical
to an existing connection.
bool pg_connection_reset resource$connection

This routine resets the connection. It is useful for error recovery. Returns TRUE on
success or FALSE on failure.
int pg_connection_status resource$connection

This routine returns the status of the specified connection. Returns
PGSQL_CONNECTION_OK or PGSQL_CONNECTION_BAD.
string pg_dbname [resource$connection]

This routine returns the name of the database that the given PostgreSQL connection
resource.
resource pg_prepare [resource$connection], string$stmtname, string$query

This submits a request to create a prepared statement with the given parameters and
waits for completion.
resource pg_execute [resource$connection], string$stmtname, array$params

This routine sends a request to execute a prepared statement with given parameters and
waits for the result.
resource pg_query [resource$connection], string$query

This routine executes the query on the specified database connection.
array pg_fetch_row resource$result[, int$row]

This routine fetches one row of data from the result associated with the specified result
resource.
array pg_fetch_all resource$result

This routine returns an array that contains all rows records in the result resource.
10
int pg_affected_rows resource$result

This routine returns the number of rows affected by INSERT, UPDATE, and DELETE
queries.
11
int pg_num_rows resource$result

This routine returns the number of rows in a PostgreSQL result resource for example
number of rows returned by SELECT statement.
12
bool pg_close [resource$connection]

This routine closes the non-persistent connection to a PostgreSQL database associated
with the given connection resource.
13
string pg_last_error [resource$connection]

This routine returns the last error message for a given connection.
14
string pg_escape_literal [resource$connection], string$data

This routine escapes a literal for insertion into a text field.
15
string pg_escape_string [resource$connection], string$data

This routine escapes a string for querying the database.
Connecting To Database
Following PHP code shows how to connect to an existing database on a local machine and finally a
database connection object will be returned.
<?php
$host
$port
$dbname
$credentials
=
=
=
=
"host=127.0.0.1";
"port=5432";
"dbname=testdb";
"user=postgres password=pass123";
$db = pg_connect( "$host $port $dbname $credentials"

if(!$db){
echo "Error : Unable to open database\n";
} else {
echo "Opened database successfully\n";
}
);
?>
Now, let's run above program to open our database testdb, if database is successfully opened,
then it will give the following message:
Opened database successfully
Create a Table
Following PHP program will be used to create a table in previously created database:
<?php
$host
$port
$dbname
$credentials
=
=
=
=
"host=127.0.0.1";
"port=5432";
"dbname=testdb";

if(!$db){
} else {
}
);
$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 above program is executed, it will create COMPANY table in your testdb and it will display
the following messages:
Table created successfully
INSERT Operation
Following PHP program shows how we can create records in our COMPANY table created in above
example:
<?php
$host
= "host=127.0.0.1";
$port
= "port=5432";
$dbname
= "dbname=testdb";
$credentials = "user=postgres password=pass123";
if(!$db){
} else {
}
);
$sql =<<<EOF
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
EOF;
if(!$ret){
} else {
echo "Records created successfully\n";
}
pg_close($db);
?>
When above program is executed, it will create given records in COMPANY table and will display
the following two lines:
Records created successfully
SELECT Operation
Following PHP program shows how we can fetch and display records from our COMPANY table
created in above example:
<?php
$host
$port
$dbname
$credentials
=
=
=
=
"host=127.0.0.1";
"port=5432";
"dbname=testdb";

if(!$db){
} else {
}
$sql =<<<EOF
SELECT * from COMPANY;
EOF;
if(!$ret){
exit;
}
while($row = pg_fetch_row($ret)){
);
echo
echo
echo
echo
"ID = ". $row[0] . "\n";

"NAME = ". $row[1] ."\n";
"ADDRESS = ". $row[2] ."\n";
"SALARY = ".$row[4] ."\n\n";
}
echo "Operation done successfully\n";
pg_close($db);
?>
When above program is executed, it will produce the following result. Keep a note that fields are
returned in the sequence they were used while creating table.
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
Following PHP code shows how we can use UPDATE statement to update any record and then fetch
and display updated records from our COMPANY table:
<?php
$host
$port
$dbname
$credentials
=
=
=
=
"host=127.0.0.1";
"port=5432";
"dbname=testdb";

if(!$db){
} else {
}
$sql =<<<EOF
UPDATE COMPANY set SALARY = 25000.00 where ID=1;
EOF;
if(!$ret){
exit;
} else {
echo "Record updated successfully\n";
}
$sql =<<<EOF
EOF;
);
if(!$ret){
exit;
}
echo "ID = ". $row[0] . "\n";
echo "NAME = ". $row[1] ."\n";
echo "ADDRESS = ". $row[2] ."\n";
echo "SALARY = ".$row[4] ."\n\n";
}
pg_close($db);
?>
When above program is executed, it will produce the following result:

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
DELETE Operation
Following PHP code shows how we can use DELETE statement to delete any record and then fetch
and display remaining records from our COMPANY table:
<?php
$host
$port
$dbname
$credentials
=
=
=
=
"host=127.0.0.1";
"port=5432";
"dbname=testdb";

if(!$db){
} else {
}
$sql =<<<EOF
DELETE from COMPANY where ID=2;
EOF;
if(!$ret){
exit;
} else {
echo "Record deleted successfully\n";
}
);
$sql =<<<EOF
EOF;
if(!$ret){
exit;
}
echo "ID = ". $row[0] . "\n";
echo "NAME = ". $row[1] ."\n";
echo "ADDRESS = ". $row[2] ."\n";
echo "SALARY = ".$row[4] ."\n\n";
}
pg_close($db);
?>
When above program is executed, it will produce the following result:

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
Processing math: 100%

Postgresql - PHP Interface

Uploaded by

Copyright:

Available Formats

Postgresql - PHP Interface

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Postgresql - PHP Interface

Uploaded by

Copyright:

Available Formats

POSTGRESQL - PHP INTERFACE

PHP Interface APIs

API & Description

resource pg_connect string$connectionstring[, int$connecttype]

bool pg_connection_reset resource$connection

int pg_connection_status resource$connection

string pg_dbname [resource$connection]

resource pg_prepare [resource$connection], string$stmtname, string$query

resource pg_execute [resource$connection], string$stmtname, array$params

resource pg_query [resource$connection], string$query

array pg_fetch_row resource$result[, int$row]

array pg_fetch_all resource$result

int pg_affected_rows resource$result

int pg_num_rows resource$result

bool pg_close [resource$connection]

string pg_last_error [resource$connection]

string pg_escape_literal [resource$connection], string$data

string pg_escape_string [resource$connection], string$data

$db = pg_connect( "$host $port $dbname $credentials"

$db = pg_connect( "$host $port $dbname $credentials"

$db = pg_connect( "$host $port $dbname $credentials"

"ID = ". $row[0] . "\n";

$db = pg_connect( "$host $port $dbname $credentials"

When above program is executed, it will produce the following result:

$db = pg_connect( "$host $port $dbname $credentials"

When above program is executed, it will produce the following result:

Processing math: 100%

You might also like