Database Tables for a PayPal IPN PHP Website Payments Application

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 (http://www.devshed.com/c/a/MySQL/MySQL-in-SSH-Basic-Guide/):

codex-m@codex-m-desktop:~$ ssh -p 22 ssh_username@example.com

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.