Beginning Databases with PostgreSQL - Chapter 15: Accessing PostgreSQL from PHP

Using the PHP API for PostgreSQL

All of the interaction with the PostgreSQL database is performed through the PostgreSQL extension, which is a comprehensive set of PHP functions. For a complete list of functions and further information about the same, refer to http://www.php.net/manual/ref.pgsql.php.

A simple PHP script that opens a connection to a PostgreSQL database, selects some rows, prints the number of rows in the resultset, and closes the connection would look something like this:

<?php  
$db_handle = pg_connect("dbname=bpsimple");  
$query = "SELECT * FROM item";  
$result = pg_exec($db_handle, $query);  
echo "Number of rows: " . pg_numrows($result);  
pg_freeresult($result);  
pg_close($db_handle);  
?>

As you can see, interacting with the database from within PHP is fairly straightforward. We will now cover the various aspects of the PHP PostgreSQL extension in more depth.

Database Connections

Before you can interact with the database, you must first open a connection to it. Each connection is represented by a single variable (we'll refer to this variable as the connection handle). PHP allows you to have multiple connections open at once, each with its own connection handle.

pg_connect()

Database connections are opened using the pg_connect() function. This function takes a connect string as its only argument and returns a database connection handle. Here's an example:

$db_handle = pg_connect("dbname=bpsimple user=jon");

You can create your own user name and use it to connect to the database as user=<username>.

If you want to use PHP variables, remember to surround the connection string in double quotes instead of single quotes:

$db_handle = pg_connect("dbname=$dbname user=$dbuser");

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)

If the connection attempt fails, the pg_connect() function will return false. Failed connection attempts can, thus, be detected by testing the return value:

<?php  
$db_handle = pg_connect("dbname=bpsimple");  
if ($db_handle) {  
   echo 'Connection attempt succeeded.';  
} else {  
   echo 'Connection attempt failed.';  
}  
pg_close($db_handle);  
?>

As mentioned above, PHP supports multiple concurrent database connections:

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

Persistent Connections

PHP also supports persistent database connections. Persistent connections are held open beyond the lifetime of the page request, whereas normal connections are closed at the end of the page request. PHP maintains a list of currently open connections and, if a request is made for a new persistence database connection with the same connection parameters as one of the open connections in this list, a handle to the already opened connection is returned instead. This has the advantage of saving the script the additional overhead of creating a new database connection when a suitable one already exists in the connection pool.

pg_pconnect()

To open a persistent connection to PostgreSQL, use the pg_pconnect() function. This function behaves exactly like the pg_connect() function described above, except that it requests a persistent connection, if one is available.

It is suggested however that you use persistent connections with care. Overusing persistent connections could lead to a large number of idle database connections to your database. The ideal use of a persistent connection is in those instances where multiple pages will also request the same kind of database connection (meaning one containing same connection parameters). In such cases, persistent connections offer a substantial performance boost.

Closing Connections

pg_close()

Database connections can be explicitly closed using the pg_close() function:

pg_close($db_handle);

There are a few things however that need to be pointed out here. Firstly, in the case of persistent connections, this function will not actually close the connection. Instead, the connection will just be returned to the database connection pool. Secondly, PHP will automatically close any open non-persistent database connections at the end of the script's execution. Both of these points make calling pg_close() largely unnecessary, but the function is included for completeness and for those instances where there is truly a need to close the connection immediately.

If the provided connection handle is invalid, pg_close() will return false. Otherwise, pg_close() will return true upon success.

Connection Information

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

All of these functions require a connection handle as their sole argument and will return either a string or a number upon success. Otherwise, they will return false:

<?php  
$db_handle = pg_connect("dbname=bpsimple");  
echo "<h1>Connection Information</h1>";  
echo "Database name: ' . pg_dbname($db_handle) . "<br>\n";  
echo "Hostname: " . pg_host($db_handle) . "<br>\n";  
echo "Options: " . pg_options($db_handle) . "<br>\n";  
echo "Port: " . pg_port($db_handle) . "<br>\n";  
echo "TTY name: " . pg_tty($db_handle) . "<br>\n";  
pg_close($db_handle);  
?>