Hack 70 Track Your eBay Products with IPN

Easily process eBay sales easily by automatically storing completed transactions in a database .

The eBay and PayPal combination is hard to beat. It gives anyone who wants to sell unique items the ability to market goods and accept payment for that item without any programming expertise or an expensive merchant account. Since eBay purchased PayPal, their efforts to integrate the two have made the process of doing business on eBay with PayPal almost seamless. This improvement includes PayPal's IPN system. When an auction is completed and it has been paid for through PayPal, an IPN call is made to your IPN script (listed in your PayPal account's Profile settings, if you have enabled IPN). This POST contains a lot of the same information as the IPN generated by a normal web purchases. However, because of the nature of an auction, the notification lacks some values we normally rely upon. Fortunately, this hack provides a workaround.

The main issue is that the item_number value supplied by IPN after payment for an auction item is actually the auction number generated by eBay, not the unique identifier you assigned to the product for internal use. This means that when an item is purchased through eBay, you have no way of determining (with your IPN script) which item that is. The workaround is to tack your internal identifier for the item to the end of the auction title, allowing our database and IPN script to process the order normally.

7.10.1 Preparing Your Database

This example pulls up product details from your database after receiving an IPN that tells you your item sold and has been paid for with PayPal. This could be useful if you like to send an automated confirmation email to your buyers with complete details about the product. To store this information, your database requires the item_number , item_name , and description fields, as shown in Table 7-1.

You might also want a count_inventory field for keeping track of how many of an item have been sold.

Table 7-1. A database table to track the stuff you sell on eBay

item_number	item_name	description
6001	Vitamins	Some children may develop a rapid...
6002	Sulfuric Acid	As seen on boxcar advertisements...
6004	Calculator	Now with a 7 and an 8!
7001	Imitation Gruel	Favored by camp counselors

7.10.2 Listing the Item Number on eBay

Have the item's unique internal identifier on hand as you list it on eBay. The length of the item numbers you use must be consistent for all the items you are selling on eBay. For instance, suppose the item we are selling is a Widget with an item number of WID-01 stored in our database. The item number is six characters long. If you list another item called Gidget on eBay, you can choose GID-02 (which is also six characters long) as its item number.

The eBay auction title field accepts 55 characters. However, since you will be using your item number in the auction title (with a space), you have only 48 characters left for your auction title. Type up to 48 characters for the auction's title, and then enter a space and the item number. For example, when you list your Widget for sale on eBay, the auction title will look like this: Widget WID-01 . It might look a bit strange at first, but it should not throw your customers off too much. When a payment for this item is made through PayPal at the auction's end, the IPN page will have the item's unique internal identifier passed back to it in the auction title.

When listing items, be sure not to add any trailing spaces after the item number as you type the auction title. You rely on the last six characters of the auction title to identify the item properly, so a trailing space will throw off your processing.

7.10.3 The Code

In your IPN script, pull out the appended item number from the auction title. The auction title is passed back in the item_name field as with normal web payment IPNs. So, for the example auction in the previous section, you would receive a value of Widget WID-01 in the item_name field. Copy that value to a variable and then assign its last six characters to a variable for the item number:

 Dim Auction_title Dim Item_number Auction_title = Request.Form("auction_title") Item_number = Right(auction_title, 6) 

Your IPN script can now query your database using that item number. For instance, here's a SQL query to get this product's information:

 SELECT * FROM tblProducts WHERE Item_number = '" & item_number & "'" 

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.

That query pulls from the database the description of the item you just sold. You could modify it to update the inventory count or perform other functions usually associated with web site payments, such as automatically delivering digital goods. As it stands, the query gives you all the information you need to email your customers your full description of the item they just purchased.