Hack 82 Insert Payment Details into a Database with IPN

Record the data from IPN into a database to facilitate simple bookkeeping .

Capturing transaction-specific information is a vital part of expanding an online store, because it provides a platform of information on which to build value-added services and upselling techniques. For example, [Hack #75] provides a list of similar products purchased by other customers.

This functionality is required for complete security against spoofing in some vending applications. It allows you to check whether a transaction has already been processed .

7.22.1 The Database Table

Create a new database table, tblOrders , in which to store your order information. This table contains information about your customers' orders, but not any information related to the products your customers actually ordered.

Your database table should consist of the fields and data types shown in Table 7-5.

Table 7-5. A table to store order information retrieved with IPN

Variable	Data type
Id	An autonumber type, set as the primary key
Payer_email	Text field
Payer_id	Text field
Payment_status	Text field
Txn_id	Text field
Mc_gross	Money, or a floating point type with 2 places of precision
Mc_fee	Money

7.22.2 The IPN Page

Once the table has been created, install your IPN script to populate it with information posted by PayPal's IPN facility. Start by creating new local variables and capturing the posted values into your IPN page:

 Dim payer_email, payer_id, payment_status, txn_id, mc_gross, mc_fee,                 payment_date payer_email = Request.Form("payer_email") payer_id = Request.Form("payer_id") payment_status = Request.Form("payment_status") txn_id = Request.Form("txn_id") mc_gross = Request.Form("mc_gross") mc_fee = Request.Form("mc_fee") payment_date = Request.Form("payment_date") 

Now that you have the values temporarily placed in your page, you can perform the database insert using the following SQL query:

 INSERT INTO tblOrders (payer_email, payer_id, payment_status, txn_id,                  mc_gross, mc_fee, payment_date) VALUES ('" & payer_email & "', "' &                 payer_id & "', '" & payment_status & "', '" & txn_id & "', " &                  mc_gross & ", " & mc_fee & ", '" & payment_date & "') 

When the values are inserted into the tblOrders database table, a unique ID number will be generated by the database for the Id field. Note that the mc_gross and mc_fee variables are not surrounded by single quotes; they are inserted into your database as numeric values.