Red Hat Database: Programmer's Guide
PrevChapter 9. Setting Up a Web Site that Uses PHPNext

Sample Application

This section contains a simple example that illustrates how PHP and Red Hat Database work together. This application will add the user's name, address, email address, and a member identification number (automatically generated) into a table.

Note

A user ID "nobody" has to be created before running the following psql commands. Please refer to the Red Hat Database Administrator and User's Guide for instructions on how to create a user.

Creating the Database Schema

Create the sample database using the following SQL commands through the psql interface:

-- create a database named sample
CREATE DATABASE sample;

-- connect to the sample database
\c sample

--create a sequence for member id
CREATE SEQUENCE UpByOne;

-- create a table 'members' with email as the primary key 
CREATE TABLE members(
  name          TEXT,
  address       TEXT,
  email         TEXT,
  mem_id        INTEGER,
  PRIMARY KEY   (email)
);

-- to allow user 'nobody' to update the table 'members' 
-- and use the sequence 'UpByOne'
grant all on members to nobody;
grant all on UpByOne to nobody;

Creating the PHP Files

To create the PHP files:

  1. Change to the /var/www/html directory:

    $ cd /var/www/html

  2. Create the HTML form for user input, reg.php, ensuring that it is world readable. This form enables users to input some values to be processed. It is assumed that PHP is installed as an Apache module; if PHP is installed as a CGI interpreter, you have to update the .php files accordingly.

    <?
   print("<BODY BGCOLOR=\"#FFFFCC\">\n");
   print("<HTML>\n");
   print("<HEAD>\n");
   print("Register As a Member</TITLE>\n");
   print("</HEAD>\n");
   print("<BODY>\n");
   print("<B>Enter the following to register as a member\n</B>");

   /* use reguser.php to handle the data */
   print("<FORM ACTION=\"reguser.php\">\n");
   print("<TABLE>\n");

   /* put the text fields in the form for user input */
   print("<TR>\n");
   print("<TD><B>Name</B></TD>");
   print("<TD><INPUT TYPE=\"text\" SIZE=30 NAME=\"Name\"></TD>\n");
   print("</TR>\n");
   print("<TR>\n");
   print("<TD><B>Address</B></TD>");
   print("<TD><INPUT TYPE=\"text\" SIZE=60 NAME=\"Address\"></TD>\n");
   print("</TR>\n");
   print("<TR>\n");
   print("<TD><B>Email Address</B></TD>");
   print("<TD><INPUT TYPE=\"text\" SIZE=30 NAME=\"Email\"></TD>\n");
   print("</TR>\n");
   print("</TABLE>\n");
   print("<INPUT TYPE=\"Submit\" NAME=\"Search\">\n");
   print("</FORM>\n");
   print("</BODY>\n");
   print("</HTML>\n");
?>

  3. Create reguser.php, which also needs to be readable by all, containing the following code. This script performs some checking, tries to create a connection with the database, inserts the row, gets the user's member id, returns the info to the user, and closes the connection.

    <?
   /*
    * This function prints a line in blue with font size 5
    */
   function PrintTitle($title)
   {
      print("<CENTER>");
      print("<FONT COLOR=\"#0000FF\" SIZE=\"5\">");
      print("<B>");
      print(strtoupper($title));
      print("</B>");
      print("</FONT>");
      print("</CENTER>\n");
   }

   /*
    * This function prints a line in blue with font size 5
    */
   function PrintError($title)
   {
      print("<CENTER>");
      print("<FONT COLOR=\"#AAAA00\" SIZE=\"4\">");
      print("<B>");
      print(strtoupper($title));
      print("</B>");
      print("</FONT>");
      print("</CENTER>\n");
   }

   print("<BODY BGCOLOR=\"#FFFFCC\">\n");
   print("<HTML>\n");
   print("<HEAD>\n");
   print("Register As a Member</TITLE>\n");
   print("</HEAD>\n");
   print("<BODY>\n");
   // to check for empty fields in the form when user
   // clicks on the submit button
   if ($Name == "")
   {
      printError("Please fill in your name.<BR>\n");
      exit;
   }
   if ($Address == "")
   {
      printError("Please fill in your address.<BR>\n");
      exit;
   }
   if ($Email == "")
   {
      printError("Please fill in your email
      address.<BR>\n");
      exit;
   }

   // Connect to database sample with default host, port,
   // options, TTY, and database named "sample"
   if (!($Connection = pg_connect("", "", "", "", "sample")))
   {
      // exit if connection cannot be established
      print("Could not establish connection.<BR>\n");
      exit;
   }

   //create SQL statement for inserting the new row
   $SQLstat = "INSERT INTO members VALUES";
   $SQLstat .= "('$Name', '$Address', '$Email',
   NEXTVAL('UpByOne'))";

   //execute the SQL statement created
   if(!($Result = pg_exec($Connection, $SQLstat)))
   {
      // exit if there are errors when executing the SQL
      // statement
      print("Could not execute query: ");
      print(pg_errormessage($Connection));
      print("<BR>\n");
      exit;
   }
   // select the row that was just inserted
   $SQLstat = "SELECT * FROM members WHERE email='$Email'";

   // execute the select statement
   if(!($Result = pg_exec($Connection, $SQLstat)))
   {
      print("Could not execute query: ");
      print(pg_errormessage($Connection));
      print("<BR>\n");
      exit;
   }

   PrintTitle("Thank you for your registration!");

   // display the member id number by select the value
   // in the mem_id field of the newly inserted row
   $result_ID = pg_result($Result, 0, "mem_id");
   PrintTitle("Your member id is $result_ID.");

   // free the result and close the connection
   pg_freeresult($Result);
   pg_close($Connection);

   print("</BR>\n");
   print("</BODY>\n");
   print("</HTML>\n");
?>

  4. Create another file listmembers.php, which is readable by all, with the following statements. This script will try to connect to the database, select all rows in the table, loop through each one of them and print out each field for each row.

    <?
   function PrintTitle($title)
   {
      print("<CENTER>");
      print("<FONT COLOR=\"#0000FF\" SIZE=\"5\">");
      print("<B>");
      print(strtoupper($title));
      print("</B>");
      print("</FONT>");
      print("</CENTER>\n");
   }

   print("<BODY BGCOLOR=\"#FFFFCC\">\n");
   print("<HTML>\n");
   print("<HEAD>\n");
   print("All Members</TITLE>\n");
   print("</HEAD>\n");
   print("<BODY>\n");
   // Connect to database sample
   if (!($Connection = pg_connect("", "", "", "", "sample")))
   {
      print("Could not establish connection.<BR>\n");
      exit;
   }

   //create SQL statement to list all members
   $SQLstat = "SELECT * FROM members";

   //execute the select statement
   if(!($Result = pg_exec($Connection, $SQLstat)))
   {
      print("Could not execute query: ");
      print(pg_errormessage($Connection));
      print("<BR>\n");
      exit;
   }

   //display the members
   print("<TABLE BORDER=1>\n");
   
   // print header row
   print("<TR>\n");

   for($Field=0; $Field < pg_numfields($Result); $Field++)
   {
      print("<TD>");
      print("<B>");
      print(strtoupper(pg_fieldname($Result, $Field) . " "));
      print("</B>");
      print("</TD>\n");
   }
   print("</TR>\n");

   //loop through rows
   for($Row=0; $Row < pg_numrows($Result); $Row++)
   {
      print("<TR>\n");
      // print out each field
      for($Field=0; $Field < pg_numfields($Result);
      $Field++)
      {
      print("<TD>");
      print(pg_result($Result, $Row, $Field));
      print("</TD>\n");
      }
      print("</TR>\n");
   }

   print("</TABLE>\n");

   // free the result and close the connection
   pg_freeresult($Result);
   pg_close($Connection);

   print("</BR>\n");
   print("</BODY>\n");
   print("</HTML>\n");
?>

Updating the Table

Update the table using the PHP form:

  1. After creating the above files, point to the following URL in a web browser: http://hostname/reg.php

    You can now type in the information and click on the Submit button. PHP is called from the HTML form and reguser.php receives the three form fields. PHP changes the form fields into variables that will be processed.

  2. After you have input one or more entries, point to the following URL in a web browser: http://hostname/listmembers.php

    The browser displays a list of all the members in the table members.

PrevHomeNext
Setting Up a Web Site that Uses PHPUpServer Programming