{"id":1337,"date":"2012-10-28T09:20:55","date_gmt":"2012-10-28T03:20:55","guid":{"rendered":"http:\/\/techsatwork.com\/blog\/?p=1337"},"modified":"2016-01-07T11:31:04","modified_gmt":"2016-01-07T05:31:04","slug":"php-postgres","status":"publish","type":"post","link":"https:\/\/techsatwork.com\/?p=1337","title":{"rendered":"PHP & Postgres"},"content":{"rendered":"

I have been using PHP for a while now. \u00c2\u00a0When people say PHP, its usually associated with \u00c2\u00a0 \u00c2\u00a0 MySQL as its backend database. But PHP will work with pretty much any database. \u00c2\u00a0Few years ago I wrote about how to get PHP to work with DB2<\/a>. \u00c2\u00a0 Here I am going to attempt to explain how to configure PHP to work with Postgres. \u00c2\u00a0For those of you not heard of Postgres<\/a>, its another famous open source database. Its highly scalable and stable. Postgres can run pretty much on any operating systems. \u00c2\u00a0I have been trying to get more familiar with it and potentially write an application that uses Postgres as its backend database.<\/p>\n

Here is what you would need to get PHP to work with Postgres:
\na. Apache
\nb. PHP with pgsql option
\nc. Postgres <\/a><\/p>\n

If you don’t want to install each of the component by yourself then you can optionally use the stack from bitnami.org<\/a>\u00c2\u00a0Install the appropriate stack for your operating system. If you are using Ubuntu (debian), you can do\u00c2\u00a0apt-get install php5-pgsql.<\/p>\n

Open up the php.ini file and make sure you have the PostgresSQL extensions turned on. \u00c2\u00a0If you are on Linux the install\/compile PHP5 with pgsql . With windows you may have to install the pgsql extensions.<\/p>\n

Lets get into postgres and create a database , table and insert some data that we can query using php.<\/p>\n

Assuming the userid you are on has \u00c2\u00a0postgres libraries in its environments, open a command prompt.
\nCreate a Database :<\/strong>
\npostgres@ubuntu:\/home\/public_html$ createdb rajudb
\n<\/span>
\nGet to a psql prompt :<\/strong>
\npostgres@ubuntu:\/home\/public_html$ psql<\/span><\/p>\n

psql (8.4.11)<\/span>
\n Type “help” for help.<\/p>\n

<\/span>Connect to database rajudb:<\/strong>
\npostgres=# \\c rajudb<\/span><\/p>\n

psql (8.4.11)<\/span>
\n You are now connected to database “rajudb”.
\n<\/span>
\nCreate a table called emailaddress:<\/strong>
\nrajudb=# create table emailaddress(userid char(4) not null,emailid char(25), primary key(userid));<\/span>
\nNOTICE:\u00c2\u00a0 CREATE TABLE \/ PRIMARY KEY will create implicit index “emailaddress_pkey” for table “emailaddress”<\/span>
\n CREATE TABLE<\/span><\/p>\n

Insert data into the table emailaddress that you created earlier:<\/strong>
\nrajudb=# insert into emailaddress values(‘A001′,’a001@techsatwork.com’);<\/span>
\n INSERT 0 1<\/span>
\n rajudb=# insert into emailaddress values(‘A002′,’a002@techsatwork.com’);<\/span>
\n INSERT 0 1<\/span>
\n rajudb=# insert into emailaddress values(‘A003′,’a003@techsatwork.com’);<\/span>
\n INSERT 0 1<\/span><\/p>\n

Select data from the table :<\/strong>
\nrajudb=# select * from emailaddress;<\/span>
\nuserid |\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 emailid<\/span>
\n ——–+—————————<\/span>
\n A001\u00c2\u00a0\u00c2\u00a0 | a001@techsatwork.com<\/span>
\n A002\u00c2\u00a0\u00c2\u00a0 | a002@techsatwork.com<\/span>
\n A003\u00c2\u00a0\u00c2\u00a0 | a003@techsatwork.com<\/span>
\n (3 rows)<\/span><\/p>\n

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 .<\/p>\n

<!DOCTYPE html PUBLIC “-\/\/W3C\/\/DTD HTML 4.01\/\/EN”><\/span>
\n <html><\/span>
\n <head><\/head><\/span>
\n <body><\/span><\/p>\n

<?php<\/span>
\n \/\/ attempt a connection<\/span>
\n $dbh = pg_connect(“host=localhost dbname=rajudb user=postgres password=secret!”);<\/span>
\n if (!$dbh) {<\/span>
\n die(“Error in connection: ” . pg_last_error());<\/span>
\n }<\/span><\/p>\n

\/\/ execute query<\/span>
\n $sql = “SELECT * FROM emailaddress”;<\/span>
\n $result = pg_query($dbh, $sql);<\/span>
\n if (!$result) {<\/span>
\n die(“Error in SQL query: ” . pg_last_error());<\/span>
\n }<\/span><\/p>\n

\/\/ iterate over result set<\/span>
\n \/\/ print each row<\/span>
\n while ($row = pg_fetch_array($result)) {<\/span>
\n echo “User Id: ” . $row[0] . “<br \/>”;<\/span>
\n echo “Email address: ” . $row[1] . “<p \/>”;<\/span>
\n }<\/span><\/p>\n

\/\/ free memory<\/span>
\n pg_free_result($result);<\/span><\/p>\n

\/\/ close connection<\/span>
\n pg_close($dbh);<\/span>
\n ?><\/span><\/p>\n

<\/body><\/span>
\n <\/html><\/span><\/p>\n

\u00c2\u00a0This 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.<\/a>\u00c2\u00a0<\/span><\/span><\/p>\n


\n<\/span><\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

I have been using PHP for a while now. \u00c2\u00a0When people say PHP, its usually associated with \u00c2\u00a0 \u00c2\u00a0 MySQL as its backend database. But PHP will work with pretty much any database. \u00c2\u00a0Few years ago I wrote about how to get PHP to work with DB2. \u00c2\u00a0 Here I am going to attempt to […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-container-style":"default","site-container-layout":"default","site-sidebar-layout":"default","site-transparent-header":"default","disable-article-header":"default","disable-site-header":"default","disable-site-footer":"default","disable-content-area-spacing":"default","footnotes":""},"categories":[53,303,3,358],"tags":[830,828,943,833,834,56,829,831,827,832],"class_list":["post-1337","post","type-post","status-publish","format-standard","hentry","category-applications","category-database","category-db2","category-how-to","tag-bitmani","tag-connect-php-to-postgres","tag-db2","tag-pg_connect","tag-pg_query","tag-php","tag-php-and-postgres","tag-php5-pgsql","tag-postgres","tag-psql"],"_links":{"self":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1337","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1337"}],"version-history":[{"count":5,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1337\/revisions"}],"predecessor-version":[{"id":1343,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1337\/revisions\/1343"}],"wp:attachment":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1337"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1337"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1337"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}