PostgreSQL Connection with PHP

How to connect PostgreSQL with PHP:

• All of the standard PostgreSQL connection parameters are available in the connection string. The most commonly used options and their meanings are listed below:

    * Dbname: Database to connect to (Default: $PGDATABASE)

    * User: User name to use when connecting (Default: $PGUSER)

    * password: Password for the specified user (Default: $PGPASSWORD or none)

    * Host: Name of the server to connect to (Default: $PGHOST or localhost)

    * hostaddr: IP address of the server to connect to (Default: $PGHOSTADDR)

    * Port: TCP/IP port to connect to on the server (Default: $PGPORT or 5432)

$db_handle1 = pg_connect("dbname=database1");

$db_handle2 = pg_connect("dbname=database2");

• PHP provides a number of simple functions for retrieving information on the current database connection based on the connection handle provided. Such functions include:

    * pg_dbname() -- Returns the name of the current database

    * pg_host() -- Returns the hostname associated with the current connection

    * pg_options() -- Returns the options associated with the current connection

    * pg_port() -- Returns the port number of the current connection

    * pg_tty() -- Returns the TTY name associated with the current connection

PostgreSQL Functions to retrieve information from Database:

1. pg_fetch_all:

            $result = pg_exec("SELECT * from expense_master");

            $print0=pg_fetch_all($result);

            echo "<br><br>";print_r($print0);

output:

            Array ( [0] => Array ( [exp_id] => 4 [exp_desc] => Rent [exp_code] => re04 ) [1] => Array             ( [exp_id] => 5 [exp_desc] => Electricity [exp_code] => eb05 ) )

2. pg_fetch_array:

            $result = pg_exec("SELECT * from expense_master");

            $print1=pg_fetch_array($result,0);    // OR  $print1=pg_fetch_array($result);

            echo "<br><br>";print_r($print1);

output:

            Array ( [0] => 4 [exp_id] => 4 [1] => Rent [exp_desc] => Rent [2] => re04 [exp_code]=> re04 ) 

3. pg_fetch_assoc:

            $result = pg_exec("SELECT * from expense_master");

            $print2=pg_fetch_assoc($result,0);

            echo "<br><br>";print_r($print2);

output:

            Array ( [exp_id] => 4 [exp_desc] => Rent [exp_code] => re04 )

4. pg_fetch_object:

            $result = pg_exec("SELECT * from expense_master");

            $print3=pg_fetch_object($result,1);

            echo "<br><br>";print_r($print3);

output:

            stdClass Object ( [exp_id] => 5 [exp_desc] => Electricity [exp_code] => eb05 )

5. pg_fetch_result:

            $result = pg_exec("SELECT * from expense_master");  

            $print4=pg_fetch_result($result,0,1);

            echo "<br><br>";print_r($print4);

output:

            Rent

6. pg_fetch_row:

            $result = pg_exec("SELECT * from expense_master");

            $print5=pg_fetch_row($result,1);

            echo "<br><br>";print_r($print5);

output:

            Array ( [0] => 5 [1] => Electricity [2] => eb05 )

7. pg_fetch_all_columns:

            $result = pg_exec("SELECT * from expense_master");

            $print6=pg_fetch_all_columns($result,1);

            echo "<br><br>";print_r($print6);

output:

            Array ( [0] => Rent [1] => Electricity )

PHP Program to connect PostgreSQL:

Note : Red Color fonts denotes the Function Name

 

 <?php

$username = "madumeena";

$password = "meena123";

$hostname = "localhost";

$dbname = "testingdb";

// Connecting to database

$link = pg_connect("dbname=$dbname user=$username password=$password");

if ($link)

            {

            echo 'Connection attempt succeeded.';

            }

else

            {

            echo 'Connection attempt failed.';

            }

// Performing SQL query

pg_exec("SET SEARCH_PATH TO _100123");

$result = pg_exec("SELECT * from expense_master");

$print0=pg_fetch_all($result);

$print1=pg_fetch_array($result,0);

$print2=pg_fetch_assoc($result,0);

$print3=pg_fetch_object($result,1);

$print4=pg_fetch_result($result,0,1);

$print5=pg_fetch_row($result,0);

$print6=pg_fetch_all_columns($result,1);

// Print the output

echo "<br><br>";print_r($print0);

echo "<br><br>";print_r($print1);

echo "<br><br>";print_r($print2);

echo "<br><br>";print_r($print3);

echo "<br><br>";print_r($print4);

echo "<br><br>";print_r($print5);

echo "<br><br>";print_r($print6);

// Print whole table of expense_master in tabular format 

echo "<table border=1 align=center>";

echo "<thead>";

echo "<th> Expense ID </th>";

echo "<th> Expense Description </th>";

echo "<th> Expense Code </th>";

if (pg_num_rows($result) != 0)

            {

            while ($row = pg_fetch_array($result))

                        {

                        echo "<tr>";

                        echo "<td>".$row['exp_id']."</td>";

                        echo "<td>".$row['exp_desc']."</td>";

                        echo "<td>".$row['exp_code']."</td>";

                        echo "</tr>";

                        }

            }

echo "</table>";

// Print the Connection Information

echo "<h2>Connection Information</h2>";

echo "Database name:" . pg_dbname($link) . "<br>\n";

echo "Hostname: " . pg_host($link) . "<br>\n";

echo "Options: " . pg_options($link) . "<br>\n";

echo "Port: " . pg_port($link) . "<br>\n";

echo "TTY name: " . pg_tty($link) . "<br>\n";

// Closing connection

pg_close($link);

?>