PHP

The the past few years , PHP has become more and more popular. The number of users is constantly increasing, and the group responsible for developing PHP is gaining members . This section is dedicated to all those people out there who want to build PostgreSQL-enabled PHP applications.

A Short History of PHP

PHP shows quite well what can result when people try to build simple Perl-based "Personal Home Page Tools." I don't think that anybody back in 1994 could have imagined that a short Perl CGI program would be the start of something really big. Today, PHP is one of the most widespread languages for Web programming available and supports more databases than many of us might be able to enumerate.

Back in the early days, "Personal Home Page Tools" supported only a few macros and a parsing engine. The parser was written in 1995 and called PHP/FI version 2. The FI actually came from a second package to work with data forms that Rasmus Lerdorf wrote. PHP/FI became popular, and people started contributing code to it.

In the middle of 1997, the way PHP was developed changed significantly. PHP was not the pet project of Rasmus anymore, and a real team was formed . The parser was completely rewritten by Zeev Suraski and Andi Gutmans. The new code became the base of PHP 3. A lot of code was ported to PHP 3 or was completely rewritten.

At the time of writing, PHP 4 is the most recent version. The engine was entirely replaced by the so-called Zend scripting engine, and the development of PHP goes on even faster. A huge number of modules and libraries have been implemented for PHP, and the number of people building PHP Web sites is constantly increasing. At the moment, several millions of PHP-enabled Web sites are set up around the globe, and their number is growing and growing.

PHP has become a very reliable platform for building complex Web sites. One of the most important and most powerful components of PHP is its ability to interact with databases. The number of databases supported by PHP is large, and the flexibility, as well as the number of functions provided by the database interfaces, could not be better. No matter what kind of database you want to use, PHP will provide the right interface.

PostgreSQL is also supported by PHP, and we will try to provide you insight into building PHP- and PostgreSQL-enabled databases.

Connecting

Before we can start working with PostgreSQL and PHP, we have to establish a connection to the server. Connecting to a database with the help of PHP is an extremely easy task because everything is done by a function called pg_connect . The following is a brief syntax overview of the pg_connect command:

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

The pg_connect function returns a so-called "connection index" that you have to use to work with other functions.

We create a small PHP script called connect.php and copy it to the html or cgi-bin directory of our Web server. We strongly recommend that you use the cgi-bin directory of your Web server because this is the more secure way. Imagine yourself in a situation where something goes wrong with your PHP file, the full source code of your program will be displayed if you have the files in the html directory. People would be able to steal your source code, which is bad itself, but the most dangerous thing is that people can see the passwords you may have hardcoded in your software. This can lead to some real trouble. Before we get to a little workaround for this problem, the following is a small piece of software that shows how you can connect to your server:

 <?         $user="hs";         $dbname="performance";         echo ("trying to connect ...<br>\ n");         $db=pg_connect("user=$user dbname=$dbname");         if      ($db)         {                 echo ('connection ok<br>');         }         else         {                 echo ('no connection<br>');         } ?> 

We connect to the database using pg_connect . $db will now contain the database handle. If the connection process fails, we want no connection to be displayed. With the help of a simple if statement, we try to find out whether $db is defined.

If you are building big Web sites, connecting to the database may be necessary several times. We recommend that you place the data required to establish the connection in a separate file. Now connect.php looks as follows :

 <?         include("globals.php");         echo ("trying to connect ...<br>\ n");         $db=pg_connect("user=$user dbname=$dbname");         if      ($db)         {                 echo ('connection ok<br>');         }         else         {                 echo ('no connection<br>');         } ?> 

globals.php contains the data to establish the connection. The file looks very simple indeed:

 <?         $user="hs";         $dbname="performance"; ?> 

PHP uses the variables in the included file as if they were defined in connect.php . This is indeed very comfortable because changing passwords, users, or any other variable used in the authentification process can be changed by editing just one file. For those among you who are afraid of vi , this is a significant advantage.

Connections can be closed explicitly with the help of the pg_close command. Closing connections is not necessary because non-persistent open links are automatically closed at the end of the script's execution. We will look at persistent database connections with PHP later in Chapter 18, "Persistent Database Connections with PHP." Working with persistent connections is rather easy". Although connections are closed automatically, we recommend that you close all open connections explicitly, especially when a huge number of connections are open on the server; this can save you some troubles.

If you want to know which database you are currently connected to, the pg_dbname function can be used:

 <?         include("globals.php");         $conn=pg_connect("user=$user dbname=$dbname");         if      (!$conn)         {                 echo ('cannot establish connection<br>');                 exit;         }         $dbname=pg_dbname($conn);         echo ("name of the current database: $dbname<br>\ n"); ?> 

This example just displays the name of the database we are currently using.

PHP offers also a way to display the name of the host to which we are currently connected. pg_host can be used to resolve the name of the computer on which the database is running. If you want to find the port PostgreSQL is listening to, use pg_port .

Working with PHP

Let's get to some real SQL action now. Because we want to do a little more than simply connecting to the database, we will present some additional functions in this section.

We start with a PHP script that creates a table and inserts some data:

 <?         include("globals.php");         $conn=pg_connect("user=$user dbname=$dbname");         if      (!$conn)         {                 echo ('cannot establish connection<br>');                 exit;         }         echo ('creating table ...<br>');         $create="CREATE TABLE messageb(name text, message text)";         $result=pg_exec($conn, $create);         if      (!$result)         {                 echo ("could not execute: $create<br>");                 exit;         }         else         {                 $sql="INSERT INTO messageb VALUES('Tom', 'Hello World')";                 $result=pg_exec($conn, $sql);                 echo ("Result: $result<br>");         } ?> 

We first establish a connection to the database by using the variables defined in globals.php . Then we create the table with the help of the pg_exec command.

The syntax of pg_exec is very simple:

 int pg_exec (int connection, string query) 

Use the database handle as the first parameter and the SQL code you want to execute as the second parameter, and everything will work just fine. To determine whether everything really worked out, we check whether $result contains an error. After creating the table, we insert one record into the database using pg_exec again.

Most SQL statements you execute with pg_exec affect certain rows in the database. To determine how many rows are affected by a statement, the programmer can use a function called pg_cmdtuples that returns the desired value. The following piece of code shows you how pg_cmdtuples can be used:

 <?         include("globals.php");         $conn=pg_connect("user=$user dbname=$dbname");         if      (!$conn)         {                 echo ('cannot establish connection<br>');                 exit;         }         $sql="INSERT INTO messageb VALUES('Tom', 'Hello World')";         $result=pg_exec($conn, $sql);         $cmdtuples = pg_cmdtuples ($result);         echo ("Result: $cmdtuples<br>"); ?> 

The function returns an integer value that can easily be displayed with the help of PHP's echo command. In the previous example, 1 will be displayed because an INSERT statement affects only one row of data.

But not only the rows of data affected by a query might be interesting for the user. Sometimes, even the result of a query might be interesting:

 <?         include("globals.php");         $conn=pg_connect("user=$user dbname=$dbname");         if      (!$conn)         {                 echo ('cannot establish connection<br>');                 exit;         }         $sql="SELECT * FROM messageb";         $result=pg_exec($conn, $sql);         $rows = pg_numrows($result);         echo ("Rows returned: $rows<br>\ n");         for($i=0; $i < $rows; $i++)         {                 $myarray = pg_fetch_row($result,$i);                 echo ("Array: $myarray[0] - $myarray[1]<br>\ n");         } ?> 

We first check the number of rows returned by the query. This is done by using pg_rows . Then we process the data line-by-line and print the result onscreen. The whole process is very simple and easy to understand. Thanks to PostgreSQL's transaction code, we do not have to worry if a piece of data is still in the database while reading it. Every query done by PHP returns a consistent snapshot of data. If you want a little more information about the topic, check out Chapter 5, "Understanding Transactions".

Sometimes the name of a column is easier to use than an index created by the interpreter. You can use pg_fetch_array instead of pg_fetch_row . In addition to storing the data in the numeric indexes of the result array, pg_fetch_array also stores the data in associative indexes, using the field names as keys. This is very comfortable and helps the programmer avoid a lot of errors. Errors very often occur when the data structure changes. In case of a change, the indexes change as well, and this will lead to trouble. In the next example, we will show how pg_fetch_array can be used. We have not included the command directly in the echo command so the code is a little bit easier for you to understand:

 <?         include("globals.php");         $conn=pg_connect("user=$user dbname=$dbname");         if      (!$conn)         {                 echo ('cannot establish connection<br>');                 exit;         }         $sql="SELECT * FROM messageb";         $result=pg_exec($conn, $sql);         $rows = pg_numrows($result);         echo ("Rows returned: $rows<br>\ n");         for($i=0; $i < $rows; $i++)         {                 $myarray = pg_fetch_array($result,$i);                 $one=$myarray["name"];                 $two=$myarray["message"];                 echo ("Array: $one - $two<br>\ n");         } ?> 

According to the PHP docs, pg_fetch_array is not significantly slower than pg_fetch_row , and it adds some very powerful features. pg_fetch_row and pg_fetch_array are not the only tools you can use to retrieve data from a query. If you want the result of a query to be returned as an object, PHP supports a function called pg_fetch_object :

 <?         include("globals.php");         $conn=pg_connect("user=$user dbname=$dbname");         if      (!$conn)         {                 echo ('cannot establish connection<br>');                 exit;         }         $sql="SELECT * FROM messageb";         $result=pg_exec($conn, $sql);         $rows = pg_numrows($result);         echo ("Rows returned: $rows<br>\ n");         for($i=0; $i < $rows; $i++)         {                 $myarray = pg_fetch_object($result,$i);                 $one=$myarray->name;                 $two=$myarray->message;                 echo ("Array: $one - $two<br>\ n");         } ?> 

Now the values in $myarray can be accessed like those of any other object. Many programmers prefer working with objects rather than arrays. PHP supports both methods , objects, and arrays.

For those of you who have decided not to use PHP's object-oriented capabilities, PHP offers some other nice functions for you, such as pg_fieldname :

 <?         include("globals.php");         $conn=pg_connect("user=$user dbname=$dbname");         if      (!$conn)         {                 echo ('cannot establish connection<br>');                 exit;         }         $sql="SELECT * FROM messageb";         $result=pg_exec($conn, $sql);         echo ('Fieldname: '.pg_fieldname($result, 1).'<br>'); ?> 

In this example, we print the name of a certain field onscreen with the help of pg_fieldname . 1 means that the second field is displayed because PHP starts counting with 0, as is done by almost all other programming languages. If we want to know the field number of a certain field, we can use pg_fieldnum instead of pg_fieldname :

 echo ('Fieldnumber: '.pg_fieldnum($result, "name").'<br>'); 

Sometimes we want to find out a little bit more about the result of a query or about special fields. Imagine a situation where we need the data type and the length of a certain field. For that, PHP offers some easy-to-use functions:

 <?         include("globals.php");         $conn=pg_connect("user=$user dbname=$dbname");         if      (!$conn)         {                 echo ('cannot establish connection<br>');                 exit;         }         $sql="SELECT * FROM messageb";         $result=pg_exec($conn, $sql);         echo ("field type: ".pg_fieldtype($result, 0)."<br>\ n");         echo ("field size: ".pg_fieldsize($result, 0)."<br>\ n"); ?> 

If we execute the script with the help of our favorite Web server, we will see two lines printed onscreen:

 field type: text field size: -1 

The first column in the database is text .

Note

PHP displays the name of the data type, not the ID of the data type. Many programming languages do this differently.

pg_fieldsize returns -1 . -1 is usually displayed when the length of the field is variable. Because we are using text, the length is not defined exactly.

Like most other high-level scripting languages, PHP does all memory management itself. Usually, all memory allocated during the execution process is freed when the script terminates. This is indeed very comfortable for the programmer. He or she does not have to be concerned about memory management because everything is done by PHP in a reliable way. A little control of PHP's and PostgreSQL's memory management can be very useful, especially on high-performance and high-availability systems. If many people perform memory consuming operations simultaneously , it may be good for system performance to free the memory allocated by a query as soon as the result is not used any more. To do this, PHP provides a function called pg_freeresult . The following example shows how the function can be used:

 <?         include("globals.php");         $conn=pg_connect("user=$user dbname=$dbname");         if      (!$conn)         {                 echo ('cannot establish connection<br>');                 exit;         }         $sql="SELECT name FROM messageb";         $result=pg_exec($conn, $sql);         $rows=pg_numrows($result);         $array=pg_fetch_object($result, 0);         echo ("The first field: ".$array->name."<br>\ n");         $status=pg_freeresult($result);         echo ("Status: $status<br>\ n"); ?> 

We have seen that huge amounts of data can easily be inserted into the database with the help of a COPY command. Usually, COPY commands are not sent to the server as one string; it is much handier to send the data line-by-line. Consequently, three commands are necessary. Let's look at the following script we use to insert two values into a table:

 <?         include("globals.php");         $conn=pg_connect("user=$user dbname=$dbname");         if      (!$conn)         {                 echo ('cannot establish connection<br>');                 exit;         }         pg_exec($conn, "COPY messageb FROM STDIN");         pg_put_line($conn, "Etschi\ tSomebody to love\ n");         pg_put_line($conn, "Hans\ tGive me something to believe in\ n");         pg_put_line($conn, "\ \ .\ n");         pg_end_copy($conn); ?> 

After connecting to the database server, we need pg_exec to send the COPY command to the database. PostgreSQL is now waiting for some data to come. The data is passed to the server line-by-line using pg_put_line . \t (tab) tells the database that the next column is about to come. After sending two lines of data to the server, we send \. and a linefeed to the backend. All components used by the COPY command have now been sent to the server, and we can finish the operation with pg_end_copy .

Note

The whole COPY command is processed as one transaction.

Large amounts of data can be transferred that way.

Errors and Exception Handling

Up to now, we have not dealt with errors and exceptions. Because exception handling is a very important issue, we will show some ways of dealing with errors. In many cases, errors and exceptions are caused by the programmer, but sometimes an error simply occurs if something has gone wrong. When working with databases, PHP usually displays an error if something unexpected happens during the communication process. If you want to get rid of those awful warnings and errors displayed when something goes wrong with your SQL statements, you can use PHPs wonderful abilities of handling exceptions.

The following script tries to execute a simple SQL statement, but somehow, something goes wrong:

 <?         include("globals.php");         $conn=pg_connect("user=$user dbname=$dbname");         if      (!$conn)         {                 echo ('cannot establish connection<br>');                 exit;         }         $sql="SELECT * FROM notthere";         $result=pg_exec($sql); ?> 

The query fails and PHP displays an error. The table called notthere does not seem to be in the database. The following error will be displayed:

 Warning: PostgreSQL query failed: ERROR: Relation 'notthere' does not exist in /var/www/html/action.php on line 12 

The error is very useful for debugging purposes, but it may not look too good on a business Web site.

PHP offers a nice onboard tool to suppress errors, simply use a @ before the command you expect will produce the error:

 <?         include("globals.php");         $conn=pg_connect("user=$user dbname=$dbname");         if      (!$conn)         {                 echo ('cannot establish connection<br>');                 exit;         }         $sql="SELECT * FROM notthere";         $result=@pg_exec($sql);         if      (!$result)         {                 echo ("This is a much more beautiful error ...<br>\ n");         }         else         {                 echo ("Everything works just fine ...<br>\ n");         } ?> 

@ does a good job, so only the error we want to be displayed can be seen onscreen:

 This is a much more beautiful error ... 

But PHP also offers a function to display errors for PostgreSQL:

 <?         include("globals.php");         $conn=pg_connect("user=$user dbname=$dbname");         if      (!$conn)         {                 echo ('cannot establish connection<br>');                 exit;         }         echo ("Trying SELECT ... <br>\ n");         $sql="SELECT * FROM notthere";         $result=@pg_exec($sql);         if      (!$result)         {                 echo (pg_errormessage($conn)."<br>\ n");         } ?> 

Simply use pg_errormessage to display the previous error message associated with a connection. The error displayed is actually generated by the backend process and can easily be compared with the errors displayed by other programming languages. If we execute the previous script, we will receive two lines of output:

 Trying SELECT ... ERROR: Relation 'notthere' does not exist 

But errors are not only displayed onscreen. If you want PHP to send error messages somewhere else, a function called error_log can be used. In general, error_log offers four possibilities of logging:

• Send the message to a file or the operating system's logging mechanism, depending on what the error_log configuration directive is set.

• 1 Message is sent by email to the address in the third parameter. This is the only message type where the fourth parameter extra_headers is used.

• 2 The message is sent through the PHP debugging connection. If remote debugging has been enabled, the message will be sent to the host defined in the third parameter.

• 3 PHP appends the message to the file defined by the third parameter.

Before we get to a simple example, the following is an overview of error_log 's syntax:

 int error_log (string message, int message_type [, string destination         [, string extra_headers]]) 

In the next example, you can see how an error message can be written to a file:

 <?         include("globals.php");         $conn=pg_connect("user=$user dbname=$dbname");         if      (!$conn)         {                 echo ('cannot establish connection<br>');                 exit;         }         echo ("Trying SELECT ... <br>\ n");         $sql="SELECT * FROM notthere";         $result=@pg_exec($sql);         if      (!$result)         {                 echo ("Error ...<br>\ n");                 error_log ("Cannot execute $sql\ n", 3, "/tmp/my-errors.log");         } ?> 

Note

Don't forget the \n at the end of the error message because PHP does not use a linefeed by itself.

Working with BLOB s and File Uploads

In Web applications, BLOB s are very often used in combination with file uploads. Today, file uploads are used for many purposes. Imagine a simple Web-based mailing system where people add their attachments to email using a file upload.

Before we start working with BLOB s, we will present a simple example of how a file can be uploaded using a simple HTML form.

The following is a short example of a HTML form:

 <FORM ENCTYPE="multipart/form-data" ACTION="action.php" METHOD=POST>         <INPUT TYPE="hidden" name="MAX_FILE_SIZE" value="1000000">         Send this file: <INPUT NAME="userfile" TYPE="file">         <INPUT TYPE="submit" VALUE="Send File"> </FORM> 

Figure 9.1 shows how this simple form looks.

Figure 9.1. A simple form for file uploads.

Simply select a file and click the Send File button. As soon as you click the button, action.php will be called:

 <?         include("globals.php");         echo ("Userfile: $userfile<br>\ n"); ?> 

PHP accepts file uploads from all RFC-1867 “compliant browsers. There are no restrictions on what type of file is being uploaded. It doesn't matter if the file contains ASCII or binary data, the file upload will be processed the same way. The most important thing that has to be defined in the HTML code is the maximum size of the file being uploaded. The maximum size has to be defined in bytes.

Click the Send File button and see what happens:

 Userfile: /tmp/phpDFtNrW 

PHP now creates a temporary file we can use for further operations. Nothing has been uploaded yet; this is just a temporary filename.

Since PHP 4.02, it is a very easy task, because everything is done by a function called move_uploaded_file .

In the next example, we will try to insert the uploaded file into the database. First we create a table called upload :

 CREATE TABLE upload(id serial, file oid); 

The following script copies the uploaded file to a temporary directory, imports it into the database, displays the contents of the database, and removes the garbage produced by the upload process:

 <?         include("globals.php");         # Connecting to the database         $conn=pg_connect("user=$user dbname=$dbname");         if      (!$conn)         {                 echo ('cannot establish connection<br>');                 exit;         }         # Creating a unique temporary filename         $tmpfname = tempnam ("/tmp", "upl_");         # Copy uploaded file         if        (is_uploaded_file($userfile))         {                 move_uploaded_file($userfile, "$tmpfname");         }         else         {                 echo ('no file uploaded<br>');                 exit;         }         # Starting transaction         pg_exec ($conn,  "BEGIN");         # Writing BLOB and OID         $oid=pg_loimport ($tmpfname,  $conn);         pg_exec ($conn, "INSERT INTO upload (file) VALUES ('$oid') ");         # Stopping transaction         pg_exec ($conn,  "COMMIT");         echo ("File $tmpfname has been inserted into the database<br><br>\ n");         echo ("Data already stored in table:<br>\ n");         # Selecting data         $result = pg_exec ($conn, "SELECT * FROM upload");         if (!$result)         {                 echo "An error occurred.\ n";                 exit;         }         $num = pg_numrows($result);         for ($i=0; $i<$num; $i++)         {                 $r = pg_fetch_row($result, $i);                 for ($j=0; $j<count($r); $j++)                 {                         echo "$r[$j]&nbsp; ";                 }                 echo "<br>";         }         # Removing temporary file and disconnecting         unlink($tmpfname);         pg_close ($conn); ?> 

After connecting to the database, we create a unique temporary filename. We will use this filename to store the uploaded file. I would consider using a temporary filename instead of the real filename because in multi-user environments or on systems with many people accessing the system simultaneously, two files may have the same name, and people would start hampering each other. That is not what we want.

With the help of move_uploaded_file , we copy the uploaded file to the temporary location.

File uploads have to be processed in one transaction, so we start a transaction explicitly. After importing the file into the database, we insert the object ID of the file into the table we use to store uploaded files. This is necessary because the files would be "lost" in the database otherwise . The data has now successfully been inserted into the database, and we can now commit the transaction now.

Finally, we display all records stored in the table and remove the temporary file so that not too much trash has to be stored in the directory for temporary files on the system.

Figure 9.2 shows how the output of the PHP file looks when uploading a file.

Figure 9.2. Some files have already been uploaded successfully.

We have not stored the original name of the file on the user's system in the database to keep the example simple. If you need additional information, check out Table 9.8.

Table 9.8. Variables Containing Additional Information

Variable	Content
$userfile	Contains the temporary filename of the uploaded file.
$userfile_name	Contains the name of the file on the user's system
$userfile_size	Contains the size of the uploaded file in bytes
$userfile_type	Contains the mime type of the file if the browser provided this information

Exporting a file from the database is as easy as importing a file into the database. Simply generate a temporary filename and export the file from the database using pg_loexport , as in the following:

 <?         include("globals.php");         # Connecting to the database         $conn=pg_connect("user=$user dbname=$dbname");         if      (!$conn)         {                 echo ('cannot establish connection<br>');                 exit;         }         $tmpfname = tempnam ("/tmp", "exp_");         $var=pg_loexport('', '$tmpfname', $conn);         if      ($var == 't')         {                 echo ("Export of file '$tmpfname' successful <br>\ n");         }         else         {                 echo ("Export of file '$tmpfname' failed <br>\ n");         } ?> 

We connect to the database and create a temporary filename, just as we did in the previous example. In the next step, we export the file with the object ID 11687766 to the temporary file (see Figure 9.3).

Figure 9.3. The file has been successfully exported.

Debugging

So far we have discussed a lot of PHP commands, but we haven't covered debugging sufficiently yet. Debugging a database-enabled PHP application is not that different from debugging any other application, but PHP, in combination with PostgreSQL, offers some very powerful and easy-to-use methods for making your applications work better.

trace (also available in Perl) allows the programmer to trace the communication between the backend process and the frontend. The information provided by pg_trace is redirected to a file and can be used very easily for debugging purposes.

We have included a very simple example to show you how tracing can be enabled and how a logfile created by pg_trace looks:

 <?         include("globals.php");         # Connecting to the database         $conn=pg_connect("user=$user dbname=$dbname");         if      (!$conn)         {                 echo ('cannot establish connection<br>');                 exit;         }         $res=pg_trace("/tmp/trace_log.log", w, $conn);         $res=pg_exec($conn, "SELECT 1+1");         echo ('nothing ...');         pg_close($conn); ?> 

The script does nothing other than connect to the database and compute the result of 1+1 . Although the operation is very simple, the output looks quite complex:

 [hs@duron php]#  cat /tmp/trace_log.log  To backend> Q To backend> SELECT 1+1 From backend> P From backend> "blank" From backend> T From backend (#2)> 1 From backend> "?column?" From backend (#4)> 23 From backend (#2)> 4 From backend (#4)> -1 From backend> D From backend (1)> _ From backend (#4)> 5 From backend (1)> 2 From backend> C From backend> "SELECT" From backend> Z From backend> Z To backend> X 

Tracing can be turned off again by using pg_untrace .

In most cases, analyzing the output of pg_trace is only useful when you understand the basics of PostgreSQL's internal protocol. A more efficient way to debug your applications is to use a simple function, such as the following:

 <?         debug("Starting script ...");         include("globals.php");         # Connecting to the database         $conn=pg_connect("user=$user dbname=$dbname");         if      (!$conn)         {                 debug("cannot establish connection");                 echo ('cannot establish connection<br>');                 exit;         }         debug("connection successfully established");         echo ('connection successfully established<br>');         debug("terminating script"); function debug($var) {         setlocale("LC_TIME","de_DE");         $datum=strftime("[%d/%b/%Y:%T %Z]");         $log_str="$datum -- $var\ n";         $fp=fopen("/tmp/debug.log","a+");         if ($fp)         {                 fwrite($fp,$log_str);                 fclose($fp);         } } ?> 

The logfile produced by the function may look like the following:

 [hs@duron php]$  cat /tmp/debug.log  [29/Mai/2001:22:38:32 CEST] -- Starting script ... [29/Mai/2001:22:38:32 CEST] -- connection successfully established [29/Mai/2001:22:38:32 CEST] -- terminating script 

At first glace, the output of the function might not seem very useful. So why should someone use that function?

The code of the debug function is taken from a real-world application implemented by us (see http://postgres.cybertec.at). If your applications use logging extensively, it is very easy to locate errors inside an application. It is necessary to know exactly what is going on inside your application, especially when dealing with complex and very business-critical software. In many situations, it may happen that the user of a system does something wrong and blames the software for the problems occurring. With the help of good logging information, it is an easy task to protect yourself against unjustifiable impeachment.

Another comfortable side effect of logging is that the logging information can be used for monitoring the demands and the behavior of your system under heavy load. Monitoring is an extremely important task, specially in database environments, because "slow" queries can easily impair the way your system works. Logfiles will help you to find the real problems of your system very quickly.

Of course, PHP also has some onboard debugging tools. Because PHP is permanently being improved, we have not included a full overview of PostgreSQL's debugging tools.

A Final Example

After all the theory about PostgreSQL and PHP, we have decided to include a small example to show you how easy it is to build applications using PostgreSQL.

Figure 9.4 shows a small prototype for a messageboard.

Figure 9.4. A simple messageboard.

Messages can be put on the board, and all messages are automatically displayed when a user enters the forum. The data structure we use for the demo application is rather trivial:

 performance=#  \   d   messageb  Table "messageb"  Attribute  Type  Modifier -----------+------+----------  name       text   message    text 

We only use two columns to store the data. The first column is used to store the name of the person who made the posting, and the second column is used to store the message.

Let's get to the source code of the application:

 <?         include("globals.php");                 // Connecting to the database         $conn=pg_connect("user=$user dbname=$dbname");         if        (!$conn)         {                 echo ("Cannot connect to the database<br>\ n");                 exit;         }         // Displaying welcome message and some HTML                echo ("<b>Welcome to the messageboard</b><br>\ n");         // Checking whether data has to inserted into the database         if        (($username) && ($message))         {                 // both messages provided                 $insert="INSERT INTO messageb VALUES('$username', '$message')";                 $result=pg_exec($conn, $insert);                 if        ($result)                 {                         echo ('insert successful ...<br>');                 }                 else                 {                         echo ('insert failed ...<br>');                 }                 }         else         {                 echo ('<font size=-1>now new messages to be inserted into                         the database<br><font size=+0>');         }                 // Executing SQL statement         $sql="SELECT * FROM messageb";         $result=pg_exec($conn, $sql);         $rows=pg_numrows($result);         // Displaying messages that are already in the database         echo ("<hr><br>Messages: <br>\ n");         echo ("<table BORDER COLS=2 WIDTH=\ "100%\ " NOSAVE > \ n");         for($i=0; $i<$rows; $i++)         {                 $array=pg_fetch_object($result, $i);                 echo ("<tr><td>$array->name</td><td>$array->message</td></tr>\ n");         }         echo ("</table>\ n");         echo ("<br><hr>Insert new messages:<br>\ n");         // Displaying input form         echo ('<FORM ENCTYPE="multipart/form-data" ACTION="board.php"                 METHOD=POST> ');         echo ('name: <INPUT name="username"> <br>                message: <INPUT name="message"> <br>                 <INPUT TYPE="submit" VALUE="Submit Message"> ');         echo ('</FORM> ');         // Closing connection         pg_close($conn); ?> 

As you can see, the code is brief. First, we connect to the database and display a message. If the database cannot be accessed, we quit the program. Then we check whether someone has got data. If username and message are defined, the script tries to insert the data into the database. Depending on whether the operation works, a message or a failure is displayed.

In the next step, we select all data from the database and display it in a table. After that, we display a form to insert the data again.

In the messageboard example, you can see how easy programming PHP is and that you can build simple applications with little effort. If applications become more complex than this one, we recommend that you keep the source code object-orientated. PHP offers wonderful object-oriented capabilities, and this will make your applications more extensible and easier to understand.