Hack 83 Insert Cart Details into a Database

Record the contents of customers' Shopping Carts into a database to build a complete order-tracking subsystem .

This hack records a list of products a customer has purchased, in addition to the corresponding payment and customer information. There are two situations in which you'll record purchase information: purchases of a single item (with the Buy Now button) and Shopping Cart transactions.

The first is fairly straightforward and serves as a primer for the more complex Shopping Cart insertion into your database. This hack is necessary for many merchants , because the PayPal history does not keep track of the individual items purchased in Shopping Cart transactions. For Shopping Cart purchases, the history provides only transaction information without any product detail. However, the PayPal IPN system does POST the individual cart values back to us in real time, so we can use that information to create our own payment history with full details.

7.23.1 The Database Table

Create a new database table to hold only the product detail information, as shown in Table 7-6.

Table 7-6. A database table that stores a customer's purchases

item_number	item_name	txn_id
6001	Vitamins	349857340958734958
6002	Sulfuric Acid	459384579348754343
6004	Calculator	345312023123246896
7001	Imitation Gruel	234982134201309323

This table will be used later with the transaction table to give a complete view of any specific transaction.

You will not record any of the payment information in this table, because you have already captured it in [Hack #82] .

You can join the tables using the transaction ID as the key; it will be the same in both tables for any one transaction. The minimal information you'll capture for each product purchased will be the product's name and item number, so create two fields named item_name and item_number with text data types.

The PayPal system does not provide individual product price information via IPN. To overcome this limitation, you must query an item's price from another table [Hack #73] and calculate the price for the item based on the item_number passed by the IPN system.

Name the new table tblOrderDetails and save the database. It is now ready to have information inserted into it by your IPN script.

7.23.2 Single-Item Purchases IPN Page

Because you're looking for the item_name and item_number variables, you need to create two new temporary variables to hold these values. Also, you need to capture the transaction ID so that you can query your database later for the information regarding a specific transaction. Create and populate the variables with the following code:

 Dim item_name, item_number, txn_id Item_name = Request.Form("item_name") Item_Number = Request.Form("item_number") Txn_id = Request.Form("txn_id") 

Next, execute this SQL statement to insert these values into the database:

 INSERT INTO tblOrderDetails (item_name, item_number, txn_id) VALUES                  ('" & item_name & "', '" & item_number & "', '" & txn_id & "') 

Once the script is activated, the values passed back for any transaction are inserted into your tblOrderDetails database table.

7.23.3 A Shopping Cart IPN

Since Shopping Carts pass one or more products for any single transaction, you need to check the IPN data for the item name and number of each product. First, use the num_cart_items variable to find out how many items the customer purchased. Create a local variable to hold the number of cart items and populate it with the following code:

 'Get number of cart items purchased Dim num_cart_items Num_car_items = Request.Form("num_cart_items") 

For Shopping Cart transactions, the item_name and item_number variables are appended with their corresponding cart item count. To get the value of the first item in the cart, examine item_name1 ; the name of the third item in the cart (if it exists) is stored in item_name3 . Using the item_name i or item_number i format, where the i is the cart item count, you can get the values for all the items in the cart.

Use a For loop in your IPN script to iterate through all the products your customer purchased, inserting the information about each into your database as you go.

 'Get number of cart items purchased Dim num_cart_items Num_car_items = Request.Form("num_cart_items") 'Create new count variable Dim i For i=1 to num_cart_items   set cInsDetails = Server.CreateObject("ADODB.Command")   cInsDetails.ActiveConnection = "DRIVER={Microsoft Access Driver                  (*.mdb)};DBQ="C:/InetPub/wwwroot/database/dbPayPal.mdb")   cInsDetails.CommandText = "INSERT INTO tblOrderDetails (item_name,                  item_number, txn_id) VALUES ('" & Request.Form(item_name & i) &                  "', '" & Request.Form(item_number & i) & "', '" & txn_id & "')"   cInsDetails.CommandType = 1   cInsDetails.CommandTimeout = 0   cInsDetails.Prepared = true   cInsDetails.Execute( ) Next 

Note that the transaction ID variable remains the same, regardless of what cart item you are on, because all the items were purchased as part of the same transaction.