Web Database Applications with PHP \& MySQLWeb Database Applications with PHP \& MySQLSearch this book

12.2. HTML and Email Receipts

Once an order has been processed, the winestore application confirms the shipping of the wines through both an email and an HTML receipt. The order.3 script redirects to the shipping.1 script shown in Example 12-2, which sends the user an email. In turn, the shipping.1 script redirects to the shipping.2 script shown in Example 12-3, which produces the HTML receipt. The HTML receipt can be visited again at a later time by bookmarking the URL and, as it carries out no database updates, it doesn't suffer from the reload problem described in Chapter 6. The receipt functionality is separated into two scripts so that returning to the HTML receipt doesn't cause an additional email receipt to be sent to the customer.

The function send_confirmation_email( ) in Example 12-2 creates the destination address, the subject, the body, and additional headers of an email message, and then sends that email message. The destination to address is created using the firstname, the surname, and the email address of the customer so that, for example, it has the following format:

Michael Smith <mike@webdatabasebook.com>

The additional email headers are static and always have the following format:

From: "Hugh and Dave's Online Wines" <help@webdatabasebook.com>                                                                               
X-Sender: <help@webdatabasebook.com>                                                                                                          
X-Mailer: PHP                                                                                                                                 
X-Priority: 1
Return-Path: <help@webdatabasebook.com>

The subject of the email is always:

$subject = "Hugh and Dave's Online Wines: Order 
            Confirmation";

The body of the message is created by querying and retrieving the details of the customer, order, and items and appending data to the string $out. The following is an example of the body of a confirmation email:

Dear Dr Smith,
  
Thank you for placing an order at Hugh and Dave's 
Online Wines.
 
Your order (reference #653-71) has been fictionally
dispatched, and should be arriving in your imagination 
soon now. Please quote the reference number in any
correspondence.
 
If it existed, the order would be shipped to:
 
Dr Michael Smith
12 Hotham St.
Collingwood Victoria 3066
Australia
 
We have billed your fictional credit card
 
The order consists of:

Quantity  Wine                           Unit Price  Total
12        1999 Smith's Chardonnay        $22.25      $267.00

Total: $267.00

Thank you for shopping at Hugh and Dave's Online Wines!

The email itself is sent with the following fragment:

// Send the email!
mail($to, $subject, $out, $headers);

The mail( ) function is a PHP library function; it's discussed later in this section.

The body of the script also checks that the user is logged in, that the correct parameters of a cust_id and an order_id have been provided, and that the user viewing the receipt is the owner of the receipt. If any of the checks fail, the user is redirected so that an error message can be displayed.

Example 12-2. shipping.1 sends the user an order confirmation as an email

<?php
  // This script sends the user a confirmation email 
  // for their order and then redirects to an HTML receipt
  // version

  include 'include.inc';

  set_error_handler("errorHandler");

  // Send the user an email that summarizes their purchase
  function send_confirmation_email($custID, 
                                   $orderID, 
                                   $connection)
  {
    // Find customer information
    $query = "SELECT *
              FROM customer
              WHERE cust_id = $custID";
  
    if (!($result = @ mysql_query ($query,
                                   $connection))) 
       showerror( );
    
    // There is only one matching row
    $row = @ mysql_fetch_array($result);
  
    // Start by setting up the "to" address
    $to = $row["firstname"] . " " . 
          $row["surname"] . " <" . 
          $row["email"] . ">";
  
    // Now, set up the "subject" line
    $subject = "Hugh and Dave's Online Wines: " .
               "Order Confirmation";
  
    // And, last (before we build the email), set up 
    // some mail headers
    $headers  = "From: Hugh and Dave's Online Wines" .
                "<help@webdatabasebook.com>\r\n";
    $headers .= "X-Sender: <help@webdatabasebook.com>\r\n"; 
    $headers .= "X-Mailer: PHP\r\n"; 
    $headers .= "X-Priority: 1\r\n"; 
    $headers .= "Return-Path: " .
               "<help@webdatabasebook.com>\r\n";
  
    // Now, put together the body of the email
    $out = "Dear " . $row["title"] . 
           " " . $row["surname"] . ",\n" .
            "\nThank you for placing an order at " .
            "Hugh and Dave's Online Wines.\n";
  
    $out .= "\nYour order (reference #" . $custID . 
            "-" . $orderID . 
            ") has been fictionally dispatched,\n" .
           "and should be arriving in your imagination " .
           "soon now.\n" . 
           "Please quote the reference number in any " .
           "correspondence.\n";
  
    $out .=  "\nIf it existed, the order would be " .
             "shipped to: \n\n" . 
             $row["title"] . " " . 
             $row["firstname"] . " " . 
             $row["initial"] . " " .
             $row["surname"] . "\n" .
             $row["addressline1"];
  
    if ($row["addressline2"] != "")
       $out .= "\n" . $row["addressline2"];
  
    if ($row["addressline3"] != "")
       $out .= "\n" . $row["addressline3"]; 
  
    $out .=  "\n" . $row["city"] . " " . 
                    $row["state"] . " " . 
                    $row["zipcode"] . "\n" . 
                    $row["country"] . "\n\n";
  
    $out .= "We have billed your fictional " .
            "credit card \n\n";
  
    $out .=   "The order consists of:\n\n";
  
    // This is a heading for the order summary
    $out .=   str_pad("Quantity", 10) . 
              str_pad("Wine", 55) .  
              str_pad("Unit Price", 12) . 
              str_pad("Total", 12) . "\n";
  
    $orderTotalPrice = 0;
  
    // list the particulars of each item in the order
    $query = "SELECT  i.qty, w.wine_name, i.price, 
                      w.wine_id, w.year, wi.winery_name
              FROM    items i, wine w, winery wi
              WHERE   i.cust_id = $custID
              AND     i.order_id = $orderID
              AND     i.wine_id = w.wine_id
              AND     w.winery_id = wi.winery_id
              ORDER BY item_id";
  
    if (!($result = @ mysql_query ($query, $connection))) 
       showerror( );

    // Add each item to the email
    while ($row = @ mysql_fetch_array($result)) 
    { 
      // Work out the cost of this line item
      $itemsPrice = $row["qty"] * $row["price"];

      $orderTotalPrice += $itemsPrice;
   
      $wineDetail = showWine($row["wine_id"], $connection);
  
      $out .= str_pad($row["qty"],10) . 
              str_pad(substr($wineDetail, 0, 53), 55);
  
      $out .= str_pad(sprintf("$%-.2f" , 
                      $row["price"]), 10);
 .    $out .= "  ";
      $out .= str_pad(sprintf("$%-.2f", $itemsPrice), 12);
      $out .= "\n";
    }
  
    $out .= "\n\nTotal: ";
  
    $out .= sprintf("$%-.2f\n", $orderTotalPrice);
  
    $out .= "\n\nThank you for shopping at Hugh and " .
            "Dave's Online Wines!";
  
    // Send the email!
    mail($to, $subject, $out, $headers);
  }
  
  // Main ----------
  
   // Re-establish the existing session
   session_start( );    
  
   // Check if the user is logged in - this should 
   // never fail if unless the script is run incorrectly
   if (!session_is_registered("loginUsername"))
   {
      session_register("message");
      $message = "You must login to finalise " .
                 "your purchase.";
      header("Location: example.cart.2.php");
      exit;
   }      

   // Check the correct parameters have been passed
   // unless the script is run correctly
   if (!isset($custID) || !isset($orderID))
   {
      session_register("message");
      $message = "Incorrect parameters to " .
                 "example.shipping.1.php";
      header("Location: example.cart.2.php");
      exit;
   }      
   // Check this customer matches the custID
   if ($custID != getCustomerID($loginUsername, NULL))
   {
      session_register("message");

      $message = "You can only view your own receipts!";
      header("Location: example.order.1.php");
      exit;
   }

   // Open a connection to the DBMS
   if (!($connection = @ mysql_pconnect($hostName, 
                                       $username, 
                                       $password)))
      showerror( );

   if (!mysql_select_db($databaseName, $connection))
      showerror( );
    
   // Send the user a confirmation email
   send_confirmation_email($custID, $orderID, $connection);

   // Redirect to a receipt page (this can't be the 
   // receipt page, since the reload problem would cause
   // extra emails).
   header("Location: example.shipping.2.php?" . 
          "custID=$custID&orderID=$orderID");
?>

The mail( ) function is used to send the order confirmation receipt. The function is also useful for sending passwords to users and sending strings to users that confirm actions such as creating accounts and other tasks. It has the following function prototype:

Boolean mail (string to, string subject, string body [, string additional_headers, string [additional_parameters]])
Sends an email. The function requires a destination to address, a subject string, and the body of an email message. Multiple destination addresses can be specified in the to address by separating each with a comma. Optional headers can also be specified and usually include the From: header specifying the address of the sender of the email. Headers must be separated with both a carriage return and a linefeed, usually specified with \r\n. The Cc: header that is used to carbon-copy an email to a recipient is case-sensitive; the header CC: is invalid. The additional_parameters parameter allows options to be supplied to the program that actually sends the email, which in most Linux installations, is sendmail or procmail. Additional parameters can usually be omitted.

The function returns true if the mail is successfully sent and false otherwise.

Example 12-3 shows the shipping.2 script that confirms the shipping of an order using HTML. The script has an identical structure to shipping.1 and executes the same queries. The only difference is that the script outputs HTML rather than creating strings to be emailed to the customer.

Example 12-3. shipping.2 confirms an order as an HTML receipt

<?php
  // This script shows the user an HTMl receipt of their
  // purchase. It is bookmarkable and carries out no
  // database writes.
  // The user must be logged in to review a receipt.

  include 'include.inc';

  set_error_handler("errorHandler");

  function show_HTML_receipt($custID, 
                             $orderID, 
                             $connection)
  {
    // Find customer information
    $query = "SELECT *
              FROM customer
              WHERE cust_id = $custID";
  
    if (!($result = @ mysql_query ($query, $connection)))
       showerror( );
    
    // There is only one matching row
    $row = @ mysql_fetch_array($result);

    echo "\n<h1>" .
      "Your order (reference # $custID - $orderID) " .
      "has been dispatched</h1>\n";
  
    echo "Thank you " . 
       $row["title"] . " " . 
       $row["surname"] . ", " .
       "your order has been completed and dispatched. " .
       "Your order reference number is " . 
       $custID . "-" . 
       $orderID . 
       ". Please quote this number in any" .
       " correspondence.<br>\n";
  
    echo "<p>If it existed, the order would have ".
       "been shipped to: \n<br><b>" . 
       $row["title"] . " " . 
       $row["firstname"] . " " . 
       $row["initial"] . " " . 
       $row["surname"] . "\n<br>" . 
       $row["addressline1"] . "\n";
  
    if ($row["addressline2"] != "")
       echo "\n<br>" . 
            $row["addressline2"];
  
    if ($row["addressline3"] != "")
       echo "\n<br>" . 
            $row["addressline3"]; 
  
    echo "\n<br>" . 
         $row["city"] . " " . 
         $row["state"] . " " . 
         $row["zipcode"] . "\n<br>" . 
         $row["country"] . "</b>\n<br>\n<br>";
  
    echo "\n<p>" .
         "We have billed your fictional credit card.";
  
    echo "\n<table border=0 width=50% " .
     "cellpadding=0 cellspacing=5>\n" .
     "\n<tr>" .
     "\n\t<td><b>Quantity</b></td>\n" .
     "\n\t<td><b>Wine</b></td>\n" .
     "\n\t<td align=\"right\"><b>Unit Price</b></td>\n" .
     "\n\t<td align=\"right\"><b>Total</b></td>\n" .
     "\n</tr>";
  
    $orderTotalPrice = 0;
  
    // list the particulars of each item in the order
    $query = "SELECT  i.qty, w.wine_name, i.price, 
                      w.wine_id, w.year, wi.winery_name
              FROM    items i, wine w, winery wi
              WHERE   i.cust_id = $custID
              AND     i.order_id = $orderID
              AND     i.wine_id = w.wine_id
              AND     w.winery_id = wi.winery_id
              ORDER BY item_id";
  
    if (!($result = @ mysql_query ($query, $connection)))
       showerror( );

    // Add each item to the receipt
    while ($row = @ mysql_fetch_array($result)) 
    { 
      // Work out the cost of this line item
      $itemsPrice = $row["qty"] * $row["price"];

      $orderTotalPrice += $itemsPrice;
   
      $wineDetail = showWine($row["wine_id"], 
                             $connection);
  
      echo "\n<tr>" .
           "\n\t<td>" . $row["qty"] . "</td>" .
           "\n\t<td>" . showWine($row["wine_id"],
                          $connection) . "</td>";

      printf("\n\t<td align=\"right\">$%-.2f</td>",
             $row["price"]);
      printf("\n\t<td align=\"right\">$%-.2f</td>",
             $itemsPrice);
      echo "\n</tr>\n";
    }

    echo "\n<tr></tr>" .
         "\n<tr>\n\t<td colspan=2 " .
         "align=\"left\"><i><b>Total of this order" .
         "</b></td>\n\t<td></td>";
  
    printf("\n\t<td align=\"right\">" .
           "$<b><i>%-.2f</b></td>\n", $orderTotalPrice);

    echo "\n</tr>\n</table>";
  
    echo "\n<p><i>An email confirmation has been sent " .
         "to you." .
         " Thank you for shopping at Hugh and Dave's " .
         "Online Wines.</i>";
  }
  
  // Main ----------
  
   // Re-establish the existing session
   session_start( );    
  
   // Check if the user is logged in - this should never
   // fail if unless the script is run incorrectly
   if (!session_is_registered("loginUsername"))
   {
      session_register("message");
      $message = "You must login to view your receipt.";

      // Redirect the browser back to the calling page
      header("Location: example.order.1.php");
      exit;
   }      

   // Check the correct parameters have been passed
   // unless the script is run correctly
   if (!isset($custID) || !isset($orderID))
   {
      session_register("message");

      $message = "Incorrect parameters to " .
                 "example.shipping.2.php";

      header("Location: $HTTP_REFERER");
      exit;
   }      

   // Check this customer matches the custID
   if ($custID != getCustomerID($loginUsername, NULL))
   {
      session_register("message");

      $message = "You can only view your own receipts!";
      header("Location: example.order.1.php");
      exit;
   }

   // Open a connection to the DBMS
   if (!($connection = @ mysql_pconnect($hostName, 
                                      $username, 
                                      $password)))
      showerror( );

   if (!mysql_select_db($databaseName, $connection))
      showerror( );

?>
<!DOCTYPE HTML PUBLIC 
   "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html401/loose.dtd">
<html>
<head>
  <title>Hugh and Dave's Online Wines</title>
</head>
<body bgcolor="white">
<?php
   // Show the user login status
   showLogin( );

   // Show the user any messages
   showMessage( );

   // Show the confirmation HTML page
   show_HTML_receipt($custID, $orderID, $connection);
?>
<form action="example.cart.5.php" method="GET">   
<table>  
<tr>
   <td><input type="submit" name="home" value="Home"></td>
</tr>
</table>
</form>
<br><a href="http://validator.w3.org/check/referer">
    <img src="http://www.w3.org/Icons/valid-html401"
     height="31" width="88" align="right" border="0"
     alt="Valid HTML 4.01!"></a>
</body>
</html>



Library Navigation Links

Copyright © 2003 O'Reilly & Associates. All rights reserved.