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 email@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 (
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 (
Below you will see the table details, with the field name and their types:
Finally, to create the productstable:
mysql> create table productstable (
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.
blog comments powered by Disqus