12.5 An Example of a Simple Image Management Tool | PHP and PostgreSQL. Advanced Web Programming2002

In this section you will see a simplified example of a file management tool that works with images. The basic features have been implemented. The idea is to show you how data can be extracted to the database and how it can be sent to the browser directly. Keep in mind that this is a prototype application.

First, a table for storing information about the pics must be generated:

 CREATE TABLE pic_db (         id serial,              -- id of pic         name text,              -- name of the pic         picoid oid,             -- object id of pic         added timestamp DEFAULT now() );

The next listing contains a piece of HTML code you can use for uploading files:

 <html> <head> <title>Title</title> </head> <body> <?php         echo "<b>List of pictures in the table</b><br>\n";         # connecting to the database         $dbh = pg_connect("dbname=phpbook user=postgres");         if      (!$dbh)         {                 echo "cannot open connection to the database<br>";                 exit;         }         $result = pg_exec($dbh, "SELECT id, name, picoid, added FROM pic_db");         $rows = pg_numrows($result);         for     ($i = 0; $i < $rows; $i++)         {                 $data = pg_fetch_row($result, $i);                 echo $data[0].' - <a href="detail.php?id='.$data[0].                         '" target="_blank"'.">$data[1]</a><br>";         } ?> <br><br><hr> <b>File Upload:</b> <form  method="post" action="input_file.php"         enctype="multipart/form-data"> <p> Choose a file: <br> <input name="testfile" type="file" size="50" maxlength="100000"><br> <input name="submit" type="submit"> </p> </form> </body> </html>

After the header is displayed, a PHP script starts. A connection to the database is established and the information about the pics that are already in the database is displayed. Every filename is displayed and a link to detail.php is generated. detail.php will contain the code for extracting the picture. After generating the links, a form is displayed. This form can be used for uploading additional files.

Figure 12.3 shows what the entire screen looks like.

Figure 12.3. File uploads after a file has been added.

graphics/12fig03.jpg

input_file.php is called as soon as somebody clicks the Submit Query button. A connection to the database is opened and the file that has been uploaded by the user is imported into the database. If the file has been imported successfully, a message is displayed. Let's have a look at input_file.php:

 <?php         # connecting to the database         $dbh = pg_connect("dbname=phpbook user=postgres");         if      (!$dbh)         {                 echo "cannot open connection to the database<br>";                 exit;         }         # checking for uploaded file         if      ($testfile)         {                 if      (is_uploaded_file($testfile))                 {                         chmod($testfile, 0777);                         $sql = "INSERT INTO pic_db (name, picoid) VALUES ";                         $sql .= "('$testfile_name', lo_import('$testfile'))";                         $stat = pg_exec($dbh, $sql);                         if      (!$stat)                         {                                 echo "the picture cannot be added<br>";                                 exit;                         }                         else                         {                                 echo "the picture has been added                                         successfully<br>";                         }                 }                 else                 {                         echo "no file uploaded";                 }         }         pg_close($dbh); ?>

The script called detail.php is used to display a picture on the screen:

 <?php         # connecting to the database         $dbh = pg_connect("dbname=phpbook user=postgres");         if      (!$dbh)         {                 echo "cannot open connection to the database<br>";                 exit;         }         $sql = "SELECT id, name, picoid, added FROM pic_db WHERE id=$id";         $result = pg_exec($dbh, $sql);         $data = pg_fetch_row($result, 0);         if      (!$data)         {                 echo "undefined picture<br>";         }         else         {                 Header ("Content-type: image/jpg");                 pg_exec($dbh, "BEGIN");                 $ofp = pg_loopen($data[2], "r");                 if      (!$ofp)                 {                         echo "cannot open BLOB<br>\n";                 }                 $img = pg_loreadall($ofp);                 print $img;                 pg_loclose($ofp);                 pg_exec($dbh, "END");         } ?>

A connection to the database is established. Then the object id of the picture you want to see is retrieved. To open the BLOB, a transaction must be started. This is an important part because without starting a transaction, it is not possible to interact with the BLOB. pg_loopen returns a handle that can be used for further operations such as reading the data from the BLOB. Finally, all handles as well as transactions are closed. In addition, the data is sent to the browser. Figure 12.4 shows what the result looks like.

Figure 12.4. Watching the pics.

graphics/12fig04.jpg