PHP

PostgreSQL

PostgreSQL DBMSs are accessed in much the same way as MySQL and Microsoft SQL Server DBMSs. Again, there are many-often functionally overlapping-functions for connecting to, querying, and extracting results from a PostgreSQL DBMS.

The five key functions are listed here, and Example 4-15 shows these implemented in a modified version of Example 4-1.

resource pg_connect(string connection_details)

Establishes a connection to a PostgreSQL DBMS. On success, the function returns a connection resource handle that can access databases through subsequent commands. It returns false on failure.

The parameters are similar to those of the mysql_connect( ) function, but the parameters are concatenated into a single string that usually includes the keywords host, dbname, user, and password. For example, to connect to localhost, use the winestore database, and log in as fred with password shhh, the format is:

$connection = pg_connect("host=localhost dbname=winestore
              user=fred password=shhh");
resource pg_exec(resource connection, string SQL_command)

Runs an SQL command through the connection created with pg_connect( ) (the database is selected with pg_connect( )). Returns a resource-a result handle used to fetch the result set-on success, and false on failure.

array pg_fetch_row(resource result_set)

Fetches the result-set data, row by row, following a pg_exec( ) command using the result_set resource returned by the query. The results are returned as an array, and the use is identical to mysql_fetch_row( ). false is returned when no more rows are available.

int pg_num_fields(resource result_set)

Returns the number of attributes in a result_set resource handle, where the result_set handle is returned from pg_exec( ).

int pg_close(resource connection)

Closes a PostgreSQL connection opened with pg_connect( ).

Example 4-15. Connecting to a PostgreSQL server database with PHP
<!DOCTYPE HTML PUBLIC
               "-//W3C//DTD HTML 4.0 Transitional//EN"
               "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
  <title>Wines</title>
</head>
<body><pre>
<?php
   // (1) Open the database connections
   $connection = pg_connect("host=localhost
               user=fred password=shhh dbname=winestore");
   // (2) Run the query on the winestore through the
   // connection
   $result = pg_exec($connection,"SELECT * FROM wine");
   // (3) While there are still rows in the result set
   while ($row = pg_fetch_row($result))
   {
      // (4) Print out each attribute in the row
      for ($i=0; $i<pg_num_fields($result); $i++)
         echo $row[$i] . " ";
      // Print a carriage return to neaten the output
      echo "\n";
   }
   // (5) Close the database connection
   pg_close($connection);
?>
</pre>
</body>
</html>