11.1 Connecting to the Database

The first thing you have to do when working with PHP and PostgreSQL is to connect to the database. Just as when you're working with a file, you need a handle to identify a connection and to interact with it. To generate this handle, you must use pg_connect. pg_connect establishesa connection to the database and returns a database handle. The connect string sent to the database is the same string you'd use in a C program. This is an important point because PHP's PostgreSQL interface is built on C, so the behavior of PHP is easy to understand if you are familiar with the C interface. However, to work with this book, you don't have to be familiar with C.

Let's see how to establish a connection to the database:

 <?php         $connstr = "dbname=phpbook user=postgres host=localhost port=5432";         $dbh = pg_connect($connstr);         if      ($dbh)         {                 echo "the connection to the database has been established<br>";         }         else         {                 echo "no connection has been established<br>";         } ?> 

First, a connect string is defined that contains the name of the database you want to connect to, and the host the database can be found on. In addition, the port has been specified. This string is passed to pg_connect and a database handle is returned. If the database handle is returned correctly, a message is displayed.

Several things can cause PHP to return a wrong database handle:

• PHP or PostgreSQL have been installed the wrong way.

• You have defined a wrong database, name, host, user, port, or password.

• PostgreSQL has run out of connections because too many users are connected to the database.

• The database cannot be reached via the network. Maybe you have not started the postmaster using -i.

If you have configured PHP and PostgreSQL properly, and if the database exists on the system, it should not be a problem to connect to the database.

The syntax we have just discussed is the new syntax of pg_connect. The old system is still available but should not be used any more:

 int pg_connect (string host, string port, string options, string tty, string dbname) 

Now that you have seen how to open a database connection, it is time to see how to close a connection at the end of a script:

 <?php         $connstr = "dbname=phpbook user=postgres host=localhost port=5432";         $dbh = @pg_connect($connstr);         if      ($dbh)         {                 echo "the connection to the database has been established<br>";         }         $status = pg_close ($dbh);         if      ($status == TRUE)         {                 echo "Connection terminated successfully<br>";                 echo "Status: $status<br>";         } ?> 

First, a connection to the database is established and the script checks whether everything has been done correctly. If an error occurs, the error will be suppressed by the @ symbol. This can be very important because in real-world applications it can be fatal if a user can see error messages on the screen. On the one hand, it does not look professional if a site displays errors, and on the other hand, it can be a potential security threat if certain information is displayed.

Now that you have seen how to connect to the database, take a closer look at the connect string what happens if not all components of the string are defined? For every parameter that is not defined, the default value of that parameter will be used. Here's an example:

 <?php         $connstr = "dbname=phpbook user=postgres";         $dbh = pg_connect($connstr);         if ($dbh) {echo "connection to phpbook established ...<br>";}         $connstr = "user=postgres";         $dbh = pg_connect($connstr);         if ($dbh) {echo "connection to postgres established ...<br>";} ?> 

The first connect string does not contain a hostname. This will make PHP connect to PostgreSQL to the local using Unix sockets. In this case no TCP/IP will be used. In addition, the default port is not necessary because Unix sockets don't need a port. Because the database and the user exist, the connection can be established successfully. The next connect string does not contain the name of the database you want to connect to. In this case PostgreSQL will assume that the name of the database is the same as the user in this example, PostgreSQL assumes that the name of the database is postgres as well. When the script executes, an error will be displayed:

 connection to phpbook established ... Warning: Unable to connect to PostgreSQL server: FATAL 1: Database "postgres" does not exist in the system catalog. in /var/www/html/connect.php on line 7 

There is no database called postgres on the system, so an error is displayed by the PHP interpreter.

After a connection has been established, it might be necessary to find out which parameters have been used in order to establish the connection to PostgreSQL. Therefore PHP provides a set of functions for retrieving that information:

 <?php         $connstr = "dbname=phpbook user=postgres host=localhost";         $dbh = @pg_connect($connstr);         if      ($dbh)         {                 echo "Handle exists<br>";         }         echo "Host: ".pg_host($dbh)."<br>";         echo "Database: ".pg_dbname($dbh)."<br>";         echo "Port: ".pg_port($dbh)."<br>";         echo "tty: ".pg_tty($dbh)."<br>"; ?> 

pg_host returns the host you are connected to. pg_dbname returns the name of the data, pg_port returns the number of the port PostgreSQL is listening to, and pg_tty returns the current tty. If you execute the script in the preceding listing, the result could look like this:

 Handle exists Host: localhost Database: phpbook Port: 5432 tty: 

We recommend using these functions only if you are connected to PostgreSQL via TCP/IP. In the case of Unix sockets, it seems as if PHP is not that stable yet. In version 4.06, PHP core dumps when calling pg_host if the connection has been established via Unix sockets.

After establishing the connection, it is closed explicitly by using pg_close. Closing database handles is not necessary because the backend process associated with a connection is destroyed automatically at the end of a PHP script. However, in applications that are implemented properly, database handles are closed explicitly. In this scenario you have seen how to work with nonpersistent connections. How to use persistent connections will be covered in Chapter 13, "Working with Persistent Database Connections."

Let's execute the script and see what is displayed on the screen:

 the connection to the database has been established Connection terminated successfully Status: 1 

The connection has been started and terminated successfully.