Hack 82 Insert Payment Details into a Database with IPN

figs/moderate.gif figs/hack82.gif

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


Data type


An autonumber type, set as the primary key


Text field


Text field


Text field


Text field


Money, or a floating point type with 2 places of precision



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,


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.