PHP & Postgres
I have been using PHP for a while now.  When people say PHP, its usually associated with   MySQL as its backend database. But PHP will work with pretty much any database.  Few years ago I wrote about how to get PHP to work with DB2.  Here I am going to attempt to explain how to configure PHP to work with Postgres.  For those of you not heard of Postgres, its another famous open source database. Its highly scalable and stable. Postgres can run pretty much on any operating systems.  I have been trying to get more familiar with it and potentially write an application that uses Postgres as its backend database.
Here is what you would need to get PHP to work with Postgres:
a. Apache
b. PHP with pgsql option
c. Postgres
If you don’t want to install each of the component by yourself then you can optionally use the stack from bitnami.org Install the appropriate stack for your operating system. If you are using Ubuntu (debian), you can do apt-get install php5-pgsql.
Open up the php.ini file and make sure you have the PostgresSQL extensions turned on. Â If you are on Linux the install/compile PHP5 with pgsql . With windows you may have to install the pgsql extensions.
Lets get into postgres and create a database , table and insert some data that we can query using php.
Assuming the userid you are on has  postgres libraries in its environments, open a command prompt.
Create a Database :
postgres@ubuntu:/home/public_html$ createdb rajudb
Get to a psql prompt :
postgres@ubuntu:/home/public_html$ psql
psql (8.4.11)
Type “help” for help.
Connect to database rajudb:
postgres=# \c rajudb
psql (8.4.11)
You are now connected to database “rajudb”.
Create a table called emailaddress:
rajudb=# create table emailaddress(userid char(4) not null,emailid char(25), primary key(userid));
NOTICE:Â CREATE TABLE / PRIMARY KEY will create implicit index “emailaddress_pkey” for table “emailaddress”
CREATE TABLE
Insert data into the table emailaddress that you created earlier:
rajudb=# insert into emailaddress values(‘A001′,’a001@techsatwork.com’);
INSERT 0 1
rajudb=# insert into emailaddress values(‘A002′,’a002@techsatwork.com’);
INSERT 0 1
rajudb=# insert into emailaddress values(‘A003′,’a003@techsatwork.com’);
INSERT 0 1
Select data from the table :
rajudb=# select * from emailaddress;
userid |Â Â Â Â Â Â Â Â Â emailid
——–+—————————
A001Â Â | a001@techsatwork.com
A002Â Â | a002@techsatwork.com
A003Â Â | a003@techsatwork.com
(3 rows)
Now lets try to retrieve the same rows using a web browser through a php script. Make sure your apache webserver is running and php is enabled to connect to postgres. Open a text editor and type the following and save it on the web server directory .
<!DOCTYPE html PUBLIC “-//W3C//DTD HTML 4.01//EN”>
<html>
<head></head>
<body>
<?php
// attempt a connection
$dbh = pg_connect(“host=localhost dbname=rajudb user=postgres password=secret!”);
if (!$dbh) {
die(“Error in connection: ” . pg_last_error());
}
// execute query
$sql = “SELECT * FROM emailaddress”;
$result = pg_query($dbh, $sql);
if (!$result) {
die(“Error in SQL query: ” . pg_last_error());
}
// iterate over result set
// print each row
while ($row = pg_fetch_array($result)) {
echo “User Id: ” . $row[0] . “<br />”;
echo “Email address: ” . $row[1] . “<p />”;
}
// free memory
pg_free_result($result);
// close connection
pg_close($dbh);
?>
</body>
</html>
 This is one set of pg functions that you can use to interact with postgres using php. To find out about others, refer to the php manuals.Â