Recording the Orders


After displaying all the products as a catalog, and after the user has filled up their shopping cart, there are three final steps:

  • Checking the user out

  • Recording the order in the database

  • Fulfilling the order

Ironically, the most important partchecking out (i.e., taking the customer's money)could not be adequately demonstrated in a book, as it's so particular to each individual site. So what I've done instead is given an overview of that process in the sidebar.

Similarly, the act of fulfilling the order is beyond the scope of the book. For physical products, this means that the order will need to be packaged and shipped. Then the order in the database would be marked as shipped by noting the shipping date. This concept shouldn't be too hard for you to grasp.

What I can adequately show in this chapter is how the order information would be stored in the database. To ensure that the order is completely and correctly entered into both the orders and order_contents tables, I'll use transactions (introduced in Chapter 11).

This script, submit_order.php, represents the final step the customer would see in the e-commerce process. Because the steps preceding this script have been skipped in this book, a little doctoring of the process is required.

To create submit_order.php

1.

Create a new PHP document in your text editor or IDE (Script 14.11).

 <?php # Script 14.11 - submit_order.  php $page_title = 'Order Confirmation'; include ('./includes/header.html'); 

Script 14.11. The final script in the e-commerce application records the order information in the database. It uses transactions to ensure that the whole order gets submitted properly.


2.

Create two temporary variables.

 $customer = 1; $total = 178.93; 

To enter the orders into the database, this page needs two additional pieces of information: the customer's identification number (which is the customer_id from the customers table) and the total of the order. The first would presumably be determined when the customer logged in (it would probably be stored in the session). The second value may also be stored in a session (after tax and shipping are factored in) or may be received by this page from the billing process. But as I don't have immediate access to either value (having skipped those steps), I'll create these two variables to fake it.

3.

Include the database connection and turn off MySQL's autocommit mode.

 require_once ('../mysql_connect.  php); mysqli_autocommit($dbc, FALSE); 

The mysqli_autocommit() function can turn MySQL's autocommit feature on or off. Since I'll want to use a transaction to ensure that the entire order is entered properly, I'll turn off autocommit first. If you have any questions about transactions, see Chapter 11 or the MySQL manual.

4.

Add the order to the orders table.

 $query = "INSERT INTO orders  (customer_id, total) VALUES  ($customer, $total)"; $result = mysqli_query($dbc, $query); if (mysqli_affected_rows($dbc)  == 1) { 

This query is very simple, entering only the customer's ID number and the total amount of the order into the orders table. The order_date field in the table will automatically be set to the current date and time, as it's a TIMESTAMP column.

5.

Retrieve the order ID and insert the order contents into the database.

 $oid = mysqli_insert_id($dbc); $query = "INSERT INTO order_  contents (order_id, print_id,  quantity, price) VALUES "; foreach ($_SESSION['cart'] as $pid  => $value) {   $query .= "($oid, $pid, {$value    ['quantity]}, {$value['price']}    ), "; } $query = substr($query, 0, -2); $result = mysqli_query($dbc, $query); 

The order_id value from the orders table is needed in the order_contents table to relate the two. This value, the print_id, the quantity ordered, and the price are all entered as individual records in the order_contents table. By looping through the shopping cart, as I did in view_cart.php, I can dynamically build up a query.

If you have any problems with this or the other queries in this script, use your standard MySQL debugging techniques: print out the query using PHP, print out the MySQL error, and run the query using another interface, like the mysql client.

6.

Report on the success of the transaction.

 if (mysqli_affected_rows($dbc)  == count($_SESSION['cart])) {   mysqli_commit($dbc);   mysqli_close($dbc);   unset($_SESSION['cart']);   echo '<p>Thank you for your order.    You will be notified when the    items ship.</p>'; 

The conditional checks to see if as many records were entered into the database as exist in the shopping cart. In short: did each product get inserted into the order_contents table? If so, then the transaction is complete and can be committed. Then the shopping cart is emptied and the user is thanked. Logically you'd want to send a confirmation email to the customer here as well.

7.

Handle any MySQL problems.

   } else {     mysqli_rollback($dbc);     mysqli_close($dbc);     echo '<p>Your order could not be      processed due to a system      error. You will be contacted      in order to have the problem      fixed. We apologize for the      inconvenience.</p>';   } } else {   mysqli_rollback($dbc);   mysqli_close($dbc);   echo '<p>Your order could not be    processed due to a system error.    You will be contacted in order    to have the problem fixed. We    apologize for the inconvenience.    </p>'; } 

The first else clause applies if the correct number of records were not inserted into the order_contents table. The second else clause applies if the original orders table query fails. In either case, the entire transaction should be undone, so the mysqli_rollback() function is called.

If a problem occurs at this point of the process, it's rather serious because the customer has been charged but no record of their order has made it into the database. This shouldn't happen, but just in case, you should write all the data to a text file and/or email all of it to the site's administrator or do something that will create a record of this order. If you don't, you'll have some very irate customers on your hands.

8.

Complete the page.

 include ('./includes/footer.html'); ?> 

9.

Save the file as submit_order.php, upload to your Web server, and test in your Web browser (Figure 14.37).

Figure 14.37. The customer's order is now complete, after entering all of the data into the database.


Because there's no direct link to this script, you'll need to fill up your shopping cart and then manually change the URL in the Web browser to http://your.domain.here/submit_order.php.

Tips

  • On a live, working site, you should assign the $customer and $total variables real values for this script to work.

  • For testing purposes, you could also change the Checkout link on view_cart.php so that it points to submit_order.php. Then you could more easily go from the one step to the other.

  • PHP has the ability to work directly with some common credit card processing systems (e.g., Cybercash or Verisign). See the PHP manual for more information.

  • If you'd like to learn more about e-commerce or see variations on this process, a quick search on the Web will turn up various examples and tutorials for making e-commerce applications with PHP.


The Checkout Process

The checkout process (which I will not discuss in detail) involves three steps:

1.

Confirm the order.

2.

Confirm/submit the billing and shipping information.

3.

Process the billing information.

Steps 1 and 2 should be easy enough for intermediate programmers to complete on their own by now. In all likelihood, most of the data in Step 2 would come from the customers table, after the user has registered and logged in.

Step 3 is the trickiest one and could not be adequately addressed in any book. The particulars of this step vary greatly depending upon how the billing is being handled and by whom. To make it more complex, the laws are different depending upon whether the product being sold is to be shipped later or is immediately delivered (like access to a Web site or a downloadable file).

Most small to medium-sized e-commerce sites use a third party to handle the financial transactions. Normally this involves sending the billing information, the order total, and a store number (a reference to the e-commerce site itself) to another Web site. This site will handle the actual billing process, debiting the customer and crediting the store. Then a result code will be sent back to the e-commerce site, which would be programmed to react accordingly. In such cases, the third-party handling the billing will provide the developer with the appropriate code and instructions to interface with their system.




    PHP and MySQL for Dynamic Web Sites. Visual QuickPro Guide
    PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide (2nd Edition)
    ISBN: 0321336577
    EAN: 2147483647
    Year: 2005
    Pages: 166
    Authors: Larry Ullman

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net