stellarwebsolutions.com: PayPal Database Reference for MySQL

StellarWebSolutions.com - The Web Application Experts - Main Page

You are here: Home > Articles > PayPal DB Reference

PayPal Integration: MySQL Table Reference.

Here's some table designs to help get going on PayPal integration without having to spend hours reading PayPal's own documentation to figure out the variable names and data types.

Shopping Cart

CREATE TABLE `stellar`.`stellar_cart` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `invoice` VARCHAR(127) NOT NULL,
  `custom` VARCHAR(255),
  PRIMARY KEY(`id`)
)

Cart Items

TABLE `stellar`.`stellar_cart_item` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent_id` INTEGER UNSIGNED,
  `item_number` VARCHAR(127),
  `quantity` INTEGER,
  `item_name` VARCHAR(127) NOT NULL,
  `amount` DECIMAL(10,2),
  `on0` VARCHAR(64),
  `os0` VARCHAR(200),
  `on1` VARCHAR(64),
  `os1` VARCHAR(200),
  `shipping` DECIMAL(10,2),
  `shipping2` DECIMAL(10,2),
  `handling` DECIMAL(10,2),
  `tax` DECIMAL(6,4),
  `type` VARCHAR(64),
  PRIMARY KEY(`id`)
)
TYPE = InnoDB;

Processed Transaction IPN

CREATE TABLE `stellar`.`stellar_sold_trans` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `invoice` VARCHAR(127),
  `custom` VARCHAR(255),
  `test_ipn` INTEGER UNSIGNED DEFAULT 0,
  `memo` VARCHAR(255),
  `business` VARCHAR(127),
  `receiver_id` VARCHAR(13),
  `receiver_email` VARCHAR(127),
  `first_name` VARCHAR(64),
  `last_name` VARCHAR(64),
  `contact_phone` VARCHAR(24),
  `address_city` VARCHAR(40),
  `address_country` VARCHAR(64),
  `address_country_code` VARCHAR(2),
  `address_name` VARCHAR(128),
  `address_state` VARCHAR(40),
  `address_status` VARCHAR(12),
  `address_street` VARCHAR(200),
  `address_zip` VARCHAR(20),
  `payer_business_name` VARCHAR(127),
  `payer_email` VARCHAR(127),
  `payer_id` VARCHAR(13),
  `payer_status` VARCHAR(12),
  `residence_country` VARCHAR(2),
  `txn_id` VARCHAR(17),
  `txn_type` VARCHAR(24),
  `transaction_entity` VARCHAR(10),
  `tax` DECIMAL(6,4),
  `auth_id` VARCHAR(64),
  `auth_exp` VARCHAR(64),
  `auth_status` VARCHAR(12),
  `auth_amount` DECIMAL(10,2),
  `num_cart_items` INTEGER,
  `mc_currency` VARCHAR(3),
  `exchange_rate` DECIMAL(10,2),
  `mc_fee` DECIMAL(10,2),
  `mc_gross` DECIMAL(10,2),
  `parent_txn_id` VARCHAR(17),
  `payment_date` VARCHAR(64),
  `payment_status` VARCHAR(24),
  `payment_type` VARCHAR(12),
  `pending_reason` VARCHAR(16),
  `reason_code` VARCHAR(16),
  `remaining_settle` DECIMAL(10,2),
  `mc_handling` DECIMAL(10,2),
  `mc_shipping` DECIMAL(10,2),
  `settle_currency` VARCHAR(3),
  `case_id` VARCHAR(64),
  `case_type` VARCHAR(12),
  `case_creation_date` VARCHAR(64),
  `handling` DECIMAL(10,2),
  `shipping` DECIMAL(10,2),
  `settle_amount` DECIMAL(10,2),
  `auction_buyer_id` VARCHAR(64),
  `auction_closing_date` VARCHAR(64),
  `auction_multi_item` INTEGER,
  `for_auction` VARCHAR(10),
  `subscr_date` VARCHAR(64),
  `subscr_effective` VARCHAR(64),
  `period1` VARCHAR(12),
  `period2` VARCHAR(12),
  `period3` VARCHAR(12),
  `amount1` DECIMAL(10,2),
  `amount2` DECIMAL(10,2),
  `amount3` DECIMAL(10,2),
  `mc_amount1` DECIMAL(10,2),
  `mc_amount2` DECIMAL(10,2),
  `mc_amount3` DECIMAL(10,2),
  `recurring` VARCHAR(1),
  `reattempt` VARCHAR(1),
  `retry_at` VARCHAR(64),
  `recur_times` INTEGER,
  `username` VARCHAR(64),
  `password` VARCHAR(64),
  `subscr_id` VARCHAR(19),
  `receipt_id` VARCHAR(24),
  PRIMARY KEY(`id`)
)
TYPE = InnoDB;

Processed Item

CREATE TABLE `stellar`.`stellar_sold_item` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent_id` INTEGER UNSIGNED,
  `custom` VARCHAR(255),
  `test_ipn` INTEGER UNSIGNED DEFAULT 0,
  `item_name` VARCHAR(127) NOT NULL,
  `item_number` VARCHAR(127),
  `mc_gross` DECIMAL(10,2),
  `option_name1` VARCHAR(64),
  `option_selection1` VARCHAR(200),
  `option_name2` VARCHAR(64),
  `option_selection2` VARCHAR(200),
  `quantity` INTEGER,
  `mc_shipping` DECIMAL(10,2),
  `mc_handling` DECIMAL(10,2),
  `tax` DECIMAL(6,4),
  PRIMARY KEY(`id`)
)
TYPE = InnoDB;

PayPal Official References

PayPal Standard Variable Reference (Shopping Cart/Buy Now/Donate Forms/Buttons)

PayPal Instant Payment Notification (IPN) and Payment Data Transfer (PDT) Variable Reference