E-Commerce Exercise 5

E-commerce Confirm Order

The second page of the checkout actually updates the database and completes the order. The file confirm_order.php is very simple. Notice that when we are done updating the database, we destroy the session so the user cannot hit the refresh button and make another entry into the database. The pseudocode is described below:

Start the session;

Register the session variables;

Update_database();

Session_destory() to empty the shopping cart;

The php code that corresponds to the pseudocode in step 1 is as follows:

<?php

include 'confirm_order_functions.inc';

session_start();

session_register(array(cart_items));

session_register(array(cart_quantities));

// The update_database() function is in confirm_order.inc

update_database();

// Destroy the session to prevent another order being added to the database

// if the user hits the refresh button

session_destroy();

html_headers("Bobby's CD Store - Thanks for your order");

echo "Your order has been received.<br>";

echo "Please <a href=\"search.php\">continue</a> to shop here.";

html_footer();

The confirm_order_functions.inc file contains the update_cart() function which is where the meat of actual work is done. There are 3 functions defined in this file - update_database(), html_header(), & html_footer(). The pseudocode for the update_database() function is described below:

Fix the Credit Card date entered by the user

Update the customers table with information supplied by user;

Find out the customer_id # that the mySQL database assigned the new customer;

Create unique order id # by combining (concatenating) last name,date, and time into one string;

For (every item in our shopping cart)

{

Insert this item in the orders table;

Find out how many CD’s we currently have in our database;

Calculate new amount based on # current - #purchased;

Update cd_store database with new quantity for this item;

}

The actual code that corresponds to the first three steps of the pseudocode in step 3 is as follows:

function update_database()

{

// Before we insert the data into the tables, we need to fix the CC Expiration date

// information the user gave us. The periods will concatenate (or put one string

// and attach it to the end of another) strings. The "00" is because the CC Expiration

// date is usually just a month and a year... there is no day value on credit cards

$date = $_POST[year] . $_POST[month] . "00";

// The localtime() function returns the current time in 24 hour time

// format.... hourminsec (with no spaces, colons, etc)

// Again this will be used to create our unique order id

// The TRUE parameter that I'm passing it will return an associative array

// meaning that $time[hour] (instead of $time[0]) will contain the hour

$time = localtime(time(),TRUE);

$dbcnx = mysql_connect("localhost", "bobby","bobby_passwd") or die("Could not connect to the database.");

mysql_select_db("bobby",$dbcnx) or die("Can not select database");

// Update the customer table with our new information that the user has given us

// If they have ordered from us before and enter the exact same information, then we

// will have duplicate copies in our database (this is because this e-commerce site

// doesn't have login capabilities).

$sql = "INSERT INTO customers (cust_id,first_name,last_name,address_1,address_2,city,state,zip,telephone,cc_num,cc_type,cc_expire)";

$sql .= "VALUES (\"\",\"$_POST[first_name]\",\"$_POST[last_name]\",\"$_POST[address1]\",\"$_POST[address2]\",\"$_POST[city]\",\"$_POST[state]\",\"$_POST[zip]\",\"$_POST[telephone]\",\"$_POST[cc_num]\",\"$_POST[cc_type]\",\"$date\")";

$sql = stripslashes($sql);

$result = mysql_query($sql) or die(mysql_error());

// This will get the customer Id # from the database so we can insert in our

// orders table. This way we know where to send the items

$cust_id = mysql_insert_id();

The remaining steps of the pseudocode are completed by the following code:

// This variable is used to create the "unique" order Id number

// the date() function will return 4 digit year (Y)followed by

// the to digit representation of month and day (md)

$today_date = date(Ymd);

// The $order_id variable is a concatenation of the last name, today's date, and the hour,min, and sec

$order_id = "$_POST[last_name]" . "$today_date" . "$time[tm_hour]" . "$time[tm_min]" . "$time[tm_sec]";

// for every item in our shopping cart, we will insert a new record into the database

// The will have the same order_id, customer_id, and date_ordered, but different item_ordered and quantity values

for ($i=0; $i < count($_SESSION[cart_items]); $i++)

{

// The $order_id variable is a concatenation of the last name, today's date, and the hour,min, and sec

$order_id = "$_POST[last_name]" . "$today_date" . "$time[tm_hour]" . "$time[tm_min]" . "$time[tm_sec]";

// Insert the item in the orders table

$query2 = "INSERT INTO orders (order_id,cust_id,item_ordered,quantity,date_ordered) VALUES(\"$order_id\",$cust_id,\"{$_SESSION[cart_items][$i]}\",\"{$_SESSION[cart_quantities][$i]}\",$today_date)";

$query2 = stripslashes($query2);

$result2 = mysql_query($query2) or die(mysql_error());

// This section of code finds out how many Cd's we have left in our shopping cart

$query3 = "SELECT quantity FROM cd_store WHERE item_number=\"{$_SESSION[cart_items][$i]}\"";

$query3 = stripslashes($query3);

$result3 = mysql_query($query3);

$db_qty = mysql_fetch_row($result3) or die(mysql_error());

// The new quantity that we will put in the database is how many we used to have minus

// how many this user ordered.

$new_db_qty = $db_qty[0] - $_SESSION[cart_quantities][$i];

// This section of code will actually update the quantity we have left (we just computed in the live above)

$query4 = "UPDATE cd_store SET quantity=\"$new_db_qty\" WHERE item_number=\"{$_SESSION[cart_items][$i]}\"";

$query4 = stripslashes($query4);

$result4 = mysql_query($query4) or die(mysql_error());

}

The source code for the confirm_order.php and confirm_order_functions.inc:

Source for confirm_order.php

Source for confirm_order_functions.inc