Hack 81 Capture Customer Information with IPN

figs/moderate.gif figs/hack81.gif

Use the data passed back from PayPal to keep a record of your customers and their information.

One of the key benefits of using PayPal is that customers do not have to enter information repeatedly whenever they buy something. As a merchant, you sometimes need to obtain the information on file at PayPal so that you can fulfill orders without having to contact your customers directly. You can capture the customer's information as it is stored at PayPal by using the IPN system whenever he makes a purchase from you; that way, you have it on hand in your local system for later use.

One such set of values that PayPal manages is the customer's shipping information. You can take the information passed back to you by PayPal and populate your local database so that you'll have that customer's information on file for later use. For instance, you might want to send a promotional mailing to all your customers' shipping addresses. You can also use the information to fulfill orders by printing shipping labels from your database or integrating with a shipping service such as UPS.

There are many other reasons why you would want to have a local copy of the customer's information, such as for site personalization, customer profile maintenance, and sales performance evaluations. This hack allows you to insert into a local database all the available information for a customer that PayPal passes back to you.

The following script is highly valuable when you are building an online eCommerce system. It helps with customer support issues, shipping information, and marketing and sales evaluation. It also serves as the procedural basis on which to insert other sets of data passed back to you by PayPal [Hack #83] .

7.21.1 The Database Table

Create a new database table named tblCustomers to store your customers' information. This table contains all of the available fields: first_name, last_name, payer_business_name, address_name, address_street, address_city, address_state, address_zip, address_country, address_status, payer_id, and payer_email. Each of the fields should be entered into your database defined as text values.

Next, add a field named Id and set it as the table's primary key with an auto increment of one and no duplicates allowed. This additional field enables you to work with unique records in your advanced store functionality [Hack #54] . Once the table is ready, simply save it, and you can begin creating your script that populates the table with data.

7.21.2 The IPN Page

Your IPN page is passed your customer's information as soon as the transaction completes. This hack uses VBScript for Microsoft Active Server Pages (ASP) and SQL queries to interact with the database. First, retrieve the values that are posted from PayPal and place them in temporary variables so you can work with them inside your VBScript code:

Dim first_name, last_name, payer_business_name, address_name, 


address_city, address_state, address_zip, address_country, 

                address_status, payer_id, payer_email

first_name = Request.Form("first_name")

last_name = Request.Form("last_name")

payer_business_name = Request.Form("payer_business_name")

address_name = Request.Form("address_name")

address_street = Request.Form("address_street")

address_city = Request.Form("address_city")

address_state = Request.Form("address_state")

address_zip = Request.Form("address_zip")

address_country = Request.Form("address_country")

address_status = Request.Form("address_status")

payer_id = Request.Form("payer_id")

payer_email = Request.Form("payer_email")

Once you have the variables populated with values, you are ready to insert them into your database table. The following SQL query adds the items to the database:

INSERT INTO tblCustomers (first_name, last_name, payer_business_name,

                address_name, address_street, &_address_city, address_state, 

                address_zip, address_country, address_status, &_ payer_id, 

                payer_email) VALUES ('" & first_name & "', "' & last_name & "', '" &

                payer_business_name & "', '" & address_name & "', '" & address_street

                & "', '" & address_city & "', '" & address_state & "', '" & & "', '" 

                & address_zip & "', '" & address_country & "', '" & address_status 

                & "', '" & payer_id & "', '" & payer_email & "')

See the "Database Coding and Platform Choices" section of the Preface for the additional information needed to put this SQL statement to work with this and the other hacks in this book.

Each time a new record is added to the table, a unique ID number is automatically generated for that record in the Id column. Uploading this page to your server and setting your IPN preferences to use this page as your IPN script causes the code to execute whenever a transaction is made in your account. When the page is called in the server-side post by PayPal, the transaction details are passed to this page, including the variable values. They are then recorded into your local database, creating a record on your own system.