Storing Ecommerce Order Information Correctly

Yesterday I was doing a tiny bit of web realted freelancing for a friend – simply involving doing a bulk, conditional update of his entire stock for his ecommerce website. The site itself was simple in design and used PayPal with a sprinkle of IPN. So, after a bit of MySQL magic, I updated all of his prices, for all ~700 products in one fell swoop – all good so far, easy money.

However, after an hour or so of emailing the store owner I got a very worried phone call. In short, the bulk price update to had caused all archived order invoices to become incorrect. Why – due to bad database design by the original developer, let me explain.

Continue reading Storing Ecommerce Order Information Correctly

Process Custom eCommerce data using Paypal IPN

More often than not it’s hard to visualise how you can send custom information to Paypal during checkout. The list of available hidden field variables initially seems very specific and restrictive at best. Granted, you can easily send over simple things such as your shipping amountm and tax rate. However, during order processing (done in you IPN script that Paypal sends the transaction’s IPN post data to) you often want to record more information when creating and storingn order information.

For example, let’s say the user can enter a specific coupon code during checkout. You would want to make sure the Paypal transaction has been successful before making the voucher as used, to do this you would need to know what code was entered during checkout. In the following simple example we’ll use the ‘custom’ field variable¬† – this is an optional field, whereby the the data is never presented to the shopper and can be 256 character long. Whatever is placed in this field before clicking your checkout button will be invisibly sent ot Paypal and posted back to your IPN script (assuming you have youre ‘rm’ or return method set to 2, or ‘POST’). The HTML for the hidden field is very simple:

<input type="hidden" name="custom" value="YOUR CUSTOM INFORMATION HERE">

Now, let’s say when you’re recording all the order information you weant to record the exact coupon code, delivery method id, the method by which the customer found your site (E.g. another id) and referrer id number. For convenience and for the sake of the example, I’ll assume you’ve done all the necessary processing to get your four pieces of information. The code to create our hidden field data is as follows:

/* ...logic to get below variables here! */

$shipping_method_id = '33';
$coupon_code = '45895';
$found_out_method = '9';
$referrer_id = '200';

$custom_info =
			'shipping_method_id' => 33,
			'coupon_code'=> 45895 ,
			'found_out_method' => 20 ,
			'referrer_id' => 9

/* Initiase field data and looping variable */
$field_data = NULL;
$i = 0;

/* Loop through info array to build data string */
foreach ($custom_info as $key => $field) {

	$field_data .= $field;

	if ( $i !== count($custom_info)) {
	      $field_data .= '-';


/* create the hidden field
generated HTML is <input type="hidden" name="custom" value="33-45895-20-9"> */
echo '<input type="hidden" name="custom" value="'.$field_data.'">' . "\n";

Printing the value of $field data will give you the string ’33-45895-20-9′. This translates to shipping method id 33, coupon code 45895, found out id 20 and referrer id 9. The dash symbol has been used to delimit values for convenience, as we need to split this string up later on.

To process these variables in your IPN script (see the Paypal PHP sample script) you simply use PHP’s explode method to split the data into an array:

/* $_POST['custom'] contains the custom information we initially sent to Paypal */
$data = explode('-',$_POST['custom']);

/* Convert $data array into variables for further processing */
$shipping_method_id = $data[0];
$coupon_code = $data[1];
$found_out_method = $data[2];
$referrer_id = $data[3];

So you’ve now posted a string of custom information to Paypal and got this custom data back via IPN during the transaction processing. Now you have assigned each piece iof the array to a variable, you can easily continue to create your order header and save it to a database.