Database Tables for a PayPal IPN PHP Website Payments Application

This is the third part of the PayPal IPN PHP Script tutorial. This part will discuss the database table required by the application as well as the details of uploading and securing the digital products on the test server.

Creating the required MySQL database table

The application requires three MySQL database table to be created:

customerrecords – This is the actual table that holds all of the customer records (the product they purchased, transaction number, amount paid, download status, etc). The data input to this table comes from a successful and verified PayPal IPN transaction.

Before the data enters the database, the PHP script will do a series of validation tests to make sure the data is clean and valid according to the application’s objective.

ipnlogs – This is the table that logs the errors and success of the PayPal IPN transaction. This is particularly useful for troubleshooting purposes.

productstable – This is the table that holds the digital product information in detail. For example, the product name, product price and the filename of the digital product are important things to include in this table.

Create the customerrecords MySQL table

Okay, let’s use the MySQL command line method in SSH to create this table. If you want, you can also use phpMyAdmin, but SSH is much better in terms of security. First log in to your SSH server from the Linux terminal, or Putty if you are using Windows ( ):

codex-m@codex-m-desktop:~$ ssh -p 22

Then once you see the bash prompt, connect to the MySQL database in which you would like database tables to be created:

-bash-2.05b$ mysql -uUSERNAME -pPASSWORD -hHOSTNAME

You will then see the MySQL prompt. Next, use the database (replace “databasename” below with the actual MySQL database name):

mysql> use databasename

If you see the message “Database changed,” then you are ready to create tables under that database.

Let’s create the “customerrecords” MySQL table. This is what it looks like when you enter the commands in the MySQL prompt to create the table:

mysql> create table customerrecords (
-> id INT Not Null Auto_Increment,
-> Primary key(id),
-> PaymentStatus VARCHAR(20),
-> PaymentAmount Decimal(6,2),
-> PaymentCurrency VARCHAR(20),
-> PayerEmail VARCHAR(50),
-> ReceiverEmail VARCHAR(50),
-> TransactionID VARCHAR(30),
-> InvoiceNumber VARCHAR(30),
-> ProductPurchased VARCHAR(60),
-> IPAddress VARCHAR(15),
-> DownloadStatus VARCHAR(10),
-> Cur_TimeStamp TIMESTAMP(8));

After you press enter on the last command with the semicolon (;), the table will be created. When you describe the table:

mysql> describe customerrecords;

Below you will see the table details with the field names and their types:

Create the ipnlogs and productstable MySQL table

Below is the MySQL command for creating the ipnlogs table:

mysql> create table ipnlogs (
-> id INT Not Null Auto_Increment,
-> Primary key(id),
-> eventlog VARCHAR(60),
-> Cur_TimeStamp TIMESTAMP(8));

Below you will see the table details, with the field name and their types:

Finally, to create the productstable:

mysql> create table productstable (
-> id INT Not Null Auto_Increment,
-> Primary key(id),
-> ProductName VARCHAR(60),
-> ProductPrice DECIMAL(6,2),
-> Filename VARCHAR(100),
-> Cur_TimeStamp TIMESTAMP(8));

Here is a screen shot of the table’s fields and data types:

Note: The “Filename” field is the actual filename of your digital product. For example, since the demo application used in this tutorial sells ebooks using the PayPal IPN, an example filename would be "codexebook.pdf."

The filename will include the file extension when it is stored in the database.

{mospagebreak title=Insert test products in productstable}

Now that the products table has been created, you will need to insert test products into it. In this tutorial series, I’m using two sample ebooks. These products are  the ones that appear on the shopping page created in the second part of this tutorial:

It is important that the product name and the product price exactly match what you are using in your PayPal buttons created in the second part of this tutorial. Below are the products that need to be inserted:

First test product

ProductName: Codex Ebook
ProductPrice: $30
Filename: codexebook.pdf

Second test product

ProductName: How to behave like a child
ProductPrice: $15
Filename: howtobehavelikeachild.pdf

Below is the MySQL insert command for the first product (you can also use phpMyAdmin to insert these products):

mysql> INSERT INTO productstable (ProductName,ProductPrice,Filename) VALUES (‘Codex Ebook’,’30′,’codexebook.pdf’);

And for the second product:

mysql> INSERT INTO productstable (ProductName,ProductPrice,Filename) VALUES (‘How to behave like a child’,’15′,’howtobehavelikeachild.pdf’);

This is what the products look like after being inserted:  

Upload Your Products into Your Test Server

Now that all of the database tables have been created, you are ready to upload your digital products (ebooks for example) to your hosting server. Follow the detailed steps below.

1. Suppose you will place your digital products inside a folder named ”ebookdownloads.” Also, suppose we will place the ”ebookdownloads” folder inside the ”paypal_ipn_demo” folder, which was created in the second part of this tutorial series. We will be doing this for testing purposes.

You will need to create the foldername “ebookdownloads” inside the “paypal_ipn_demo” folder using an SSH client, and then assign a folder permission of 755.

2. Now that the folder has been created, upload the two test products, codexebook.pdf and howtobehavelikeachild.pdf, to the “ebookdownloads” folder.

You can download the sample ebook used in this tutorial (unzip and extract the ebooks first):

The recommended file permission for the ebook is 644.

3. You also need to protect the ebook from direct downloading (using a browser and any other methods). You will be using the .htaccess method to deny public access to these ebooks.

If you are not going to protect this, then malicious users might be able to figure out the URL path of your ebook and download it without paying in PayPal.

To create an .htaccess file:

First, open a text editor (notepad or gedit in Linux), and add the following lines:

order deny,allow
deny from all

Save this file as .htaccess (do not forget to include a dot before the filename), and then upload the .htaccess file inside the ebookdownloads folder.

This is what it looks like inside an SSH client (Filezilla):

4. Try to directly download the ebook using a browser. For example, this is the actual path to download the codex ebook:

Downloading should be denied, and you should see the 403 forbidden error shown in the browser.

5. Reconfirm the server header status using a checker:

The result should be a 403 forbidden status:

Results of the GSiteCrawler Server-Test
Tested at 1/12/2011 6:20:47 AM / from

Result code: 403 (Forbidden / Forbidden)

Now that direct downloading permission is denied, how would the user be able to download the ebook? The secure strategy is to let PHP read the file from the ebookdownloads folder (using the readfile PHP function) and let it force downloading the purchased ebook to the user.

Using this method, no one will be able to download the ebook, except for authenticated and valid customers in your website. The details of this PHP script and other IPN related scripts will be discussed in upcoming PayPal IPN PHP script tutorials.

Google+ Comments

Google+ Comments