eCommerce Site

We need a shopping cart through which customers can select products for purchase. It is activated with "Buy Now" button appearing on the detail.php page. When the button is clicked, a record is added to the shopping cart identifying the selected product. Selected items are accumulated until the customer finalizes the purchase, at which time the shopping cart items are used to prepare a sales order.

A convenient method of maintaining a shopping cart is with a database table. Records are added to the table when a customer chooses items for purchase, records are deleted from the table when a customer chooses not to purchase a previously selected item, and records are changed when a customer chooses a different quantity to purchase. The table is dynamic, with records added, changed, and deleted during shopping activities.

The shopping cart record only needs to contain a minimum amount of information. It needs to keep track of the customer, the product, and the quantity selected. Additional fields can be added to support other activities. For example, you can include a date field for time-stamping the purchases (a good idea for use in cleaning up the table of abandoned purchases) and a price field if there is a risk that the price of the item might change prior to final checkout. For present purposes we'll establish the following structure for our ShopCart table that is added to the eCommerce.mdb database:

Selecting a product and adding it to the shopping cart are activities that take place on the detail.php page. When the customer clicks the "Buy Now" button, information about the related item is written as a new record to the ShopCart table. Items accumulate in the shopping cart as additional purchases are made. After final checkout and recording of the final order, the shopping cart can be cleared of these items.

First, we need to revisit the "Buy Now" button on the detail.php page and code it to trigger a purchase.

Field Name	Field Type	Field Size	Field Contents
OrderNo	Text	10	Order number
OrderItem	Text	6	Product number
OrderDate	Date/Time		Date item was ordered
OrderQuantity	Number	Long Integer	Quantity of product selected

<form action="detail.php?OrderItem=<?php echo $ItemNumber ?>" method="post">
<input type="submit" class="buttonL" name="BuyButton" value="Buy Now"
onMouseOver="OverMouse(this)"; onMouseOut="OutMouse(this)">
</form>

The ACTION attribute, of course, is the current detail.php page; plus, we need to inform this page, when it is reloaded, of the ItemNumber of the product to be added to the shopping cart. This information is passed through the query string ?OrderItem=<?php echo $ItemNumber ?> (recall, the ItemNumber variable is available on this current page). The POST method is used for submission.

Here is something interesting to think about. Normally, <FORM> tags enclose data fields that are transmitted when the submit button is clicked. In this case, however, there are no form fields--only the button. Therefore, when the form is submitted, only the name and value of the button is transmitted. We need, though, to send the ItemNumber of the product detailed on this page so that it can be added to the shopping cart. So, we simply append it as a query string to the ACTION URL. Now, both a $_POST[BuyButton] variable (containing the BuyButton) and a $_GET[OrderItem] variable (containing the OrderItem) become available to the receiving page. Note that we've used the query string name OrderItem for the product to be purchased to avoid any confusion with the ItemNumber for the product being viewed.

When this form is submitted, a script at the top of the detail.php page writes the item to the shopping cart.

 <?php
session_start();
$ItemNumber = $_GET[ItemNumber];
$Category = $_GET[Category];
$Criterion =$_GET[Criterion];
$OrderNo = $_SESSION[OrderNo];
$OrderDate = date('n/d/y');

 if ($_POST[BuyButton] == "Buy Now")
  {
  
$OrderItem = $_GET[OrderItem];
$conn2 = odbc_connect('Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\inetpub\wwwroot\PHPTutorial\Ecommerce\databases\ecommerce.mdb','','');
$sqlCart = "SELECT OrderNo,OrderItem FROM ShopCart WHERE OrderNo ='$OrderNo'";
$rsCart = odbc_exec($conn2,$sqlCart);
	
	while ($row = odbc_fetch_array($rsCart))
		{
			if ($row[OrderNo] == $OrderNo && $row[OrderItem] == $OrderItem)
		{
				$update = true;
		
		}
	
		}
	
	if (!$update)
	  
		{
	  
	$sqlInsert = "INSERT INTO ShopCart (OrderNo,OrderItem,OrderDate,OrderQuantity) Values ('$OrderNo', '$OrderItem','$OrderDate,1)";
	$rsInsert = odbc_exec($conn2,$sqlInsert);
	    
	    }
	  
	  else
	  
	  {
	  
	  $sqlUpdate = "Update ShopCart SET OrderQuantity = OrderQuantity + 1 WHERE OrderNo = '$OrderNo' AND 			
	  OrderItem = '$OrderItem'";
	  $rsUpdate = odbc_exec($conn2,$sqlUpdate);
	  
	  }
  
   header("Location:shopcart.php");
  }

?>

The new ShopCart record is populated with four values. The OrderNo is taken from the Session Object - $_SESSION[OrderNo]. The OrderItem is passed to this page through the query string. The OrderDate is valued with the current system date obtained by using the date() function. The OrderQuantity is set to 1. When an item is initially selected for purchase we'll automatically set the quantity ordered to 1. The customer can change this quantity on the shopping cart page.

We are also dealing here with the situation in which the customer might attempt to purchase the same product multiple times, either intentionally or inadventently. Our "business rule" is that only one item can be ordered one time (although customers will have the ability to change the quantity ordered on the shopping cart page). We don't want the same item to appear multiple times in the shopping cart.

This situation is easily handled by first checking to see if a record for this product already exists in the ShopCart table. An SQL SELECT statement and a loop through the returned recordset makes this check:

If matching OrderNo and OrderItem values are found, an SQL UPDATE statement is needed to update the existing record. If no matches are found an SQL INSERT statement is needed to insert the new record. To handle this, we create a variable ($update) and assign it a true value if a matching OrderNo and OrderItem values are found. The following code block contains the INSERT and UPDATE statements. The value of our $update variable depends on which SQL statement is executed:

After the new record is added, the recordset and connection are closed and the customer is sent directly to the shopping cart page through the header("Location:url") method. It is assumed that the customer will wish to review shopping cart items after making a selection. We'll be coding the shopcart.php page next.