How To: Getting your orders into MySQL in realtime

[J Graham]

Thought I'd share how I insert our orders into our MySql database as they
are processed by ShopSite. This technique uses the Order API feature of
ShopSite v6 as well as PHP and of course MySql.

Below is a perl cgi script I wrote that can be submitted as and ShopSite
Order API cgi script. It's basically the same as the sample custom-dump.pl
script, except I write the order data to a file. It's the first step in
storing your orders to a MySQL database as they are processed by Shopsite in
real-time.

At the end of this script I execute a PHP script that then opens the file to
which I just wrote the order data. The PHP script then parses the data file
and creates an insert statement into my MySQL database.

There's probably a variety of ways to do this, but this seemed the most
flexible and easiest to implement.

-Jon
Homeworkout.com

====== Start order.cgi

#!/usr/bin/perl

open (OUT, ">[PATH_TO_MY_ROOTDIR]/orders/data.txt") || die "cant open
outputfile";

my $buffer;
my $method;
if ($ENV{'REQUEST_METHOD'} =~ /get/i) {
$buffer = $ENV{'QUERY_STRING'};
$method = "Get";
}
else {
read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
$method = "Post";
}

my $pair;
my @nvpairs = split(/&/, $buffer);
foreach $pair (@nvpairs)
{
($name, $value) = split(/=/, $pair);

$name =~ tr/+/ /;
$name =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
$value =~ tr/+/ /;
$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;

# Use whatever output format you like in the line below
print OUT "$name ::==:: $value\n";
}

close(OUT);

# The line below may have wrapped - should be one line
my $output = `[PATH_TO_PHP_EXECUTABLE]/php
[PATH_TO_MY_ROOTDIR]/orders/orderprocess.php`;

====== End order.cgi

[loren_d_c]

You could also write directly to the MySQL database from within the
Order API cgi. If you have to do the same amount/kind of coding from
inside the PHP file, then why not cut down on the amount of code and
just do it all in the same file. Or if you prefer writing the SQL stuff
in PHP, you could just do the whole thing in PHP, there is no rule that
says the Order API program has to be Perl, it can be anything that can
accept the CGI-type name=value pairs.

Also, rather than loop through the whole query string sent to the cgi,
if you use the perl CGI module (PHP probably has a similar module) you
can just call a param by name and get it's value. The sample
custom-dump.pl just loops through the values like it does because it is
generic and is just printing out all of the variables passed to it,
reagardless of their name. But since in writing an application you will
know what specific variables you need, it is much simpler to use the CGI
module and just get it by it's name (shown below).

Here is a bit of code that I have posted before that inserts certain
order info into an SQL database using the perl DBI module (in this case
the database is PostgreSQL, but the DBI stuff for MySQL is very
similar):

===================================
#!/usr/bin/perl

use DBI;
use CGI qw(:standard); # need the standard to do some special stuff not
included in this sample

# get all name value pairs into $formIn
my $formIn = new CGI;

# now start getting variables that we need
$orderid = $formIn->param('O-OrderNum');

....
<snip>
In this section of code I got all of the universal cart values I needed
just like I got the ShopSite order number above (which is the nice thing
about the CGI module, makes getting the variable values very easy if you
know their names which you should because they are in the documentation
or you can see them via the custom-dump.pl). I then did some looping
stuff to get each product's unique info (from the Bxx- variables) into
arrays, which just happened to be what the particular project called
for.
</snip>
....

# connect to the postgres database called mydatabase authenticating as
the database user myuser
$dbh = DBI->connect('dbi:Pg:dbname=mydatabase', 'myuser', undef, {
RaiseError => 1, AutoCommit => 1 });

# print loop to print each unique line item
for ($i = 0; $i < $uniqueitems; $i++){

# create the SQL statement to insert to the lstrans table called
transactions
$sql = "INSERT INTO transactions VALUES ( '$orderid', '$siteid',
'$timeentered', '$timecompleted', '@skuarray[$i]', '@quantityarray[$i]',
'@amountarray[$i]','$currency', '$email', '$paymenttype', '$zipcode',
'@namearray[$i]' )"; # all of this should be on one line in the perl
file

# now do the SQL statement
$dbh->do($sql) || die("INSERT error: DBI::errstr");

# end the print loop
}

# close/disconnect from the database
$dbh->disconnect;
============================================















J Graham wrote:

Thought I'd share how I insert our orders into our MySql database as they
are processed by ShopSite. This technique uses the Order API feature of
ShopSite v6 as well as PHP and of course MySql.

Below is a perl cgi script I wrote that can be submitted as and ShopSite
Order API cgi script. It's basically the same as the sample custom-dump.pl
script, except I write the order data to a file. It's the first step in
storing your orders to a MySQL database as they are processed by Shopsite in
real-time.

At the end of this script I execute a PHP script that then opens the file to
which I just wrote the order data. The PHP script then parses the data file
and creates an insert statement into my MySQL database.

There's probably a variety of ways to do this, but this seemed the most
flexible and easiest to implement.

-Jon
Homeworkout.com

====== Start order.cgi

#!/usr/bin/perl

open (OUT, ">[PATH_TO_MY_ROOTDIR]/orders/data.txt") || die "cant open
outputfile";

my $buffer;
my $method;
if ($ENV{'REQUEST_METHOD'} =~ /get/i) {
$buffer = $ENV{'QUERY_STRING'};
$method = "Get";
}
else {
read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
$method = "Post";
}

my $pair;
my @nvpairs = split(/&/, $buffer);
foreach $pair (@nvpairs)
{
($name, $value) = split(/=/, $pair);

$name =~ tr/+/ /;
$name =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
$value =~ tr/+/ /;
$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;

# Use whatever output format you like in the line below
print OUT "$name ::==:: $value\n";
}

close(OUT);

# The line below may have wrapped - should be one line
my $output = `[PATH_TO_PHP_EXECUTABLE]/php
[PATH_TO_MY_ROOTDIR]/orders/orderprocess.php`;

====== End order.cgi

[Keith Palmer]

Can you elaborate on this a little more... do you mean that I could replace
the entire Shopsite Order API with something custom written in PHP?

So I could essentially entirely bypass the Shopsite Orders section of the
Shopsite database if I wanted, go directly to a half-way decent database
that I could customize and use with other applications?




"J Graham" <jkgraham@homeworkout.com> wrote in message
news:ahso8q$duc$1@support.shopsite.com...

Thought I'd share how I insert our orders into our MySql database as they
are processed by ShopSite. This technique uses the Order API feature of
ShopSite v6 as well as PHP and of course MySql.

Below is a perl cgi script I wrote that can be submitted as and ShopSite
Order API cgi script. It's basically the same as the sample
custom-dump.pl
script, except I write the order data to a file. It's the first step in
storing your orders to a MySQL database as they are processed by Shopsite
in
real-time.

At the end of this script I execute a PHP script that then opens the file
to
which I just wrote the order data. The PHP script then parses the data
file
and creates an insert statement into my MySQL database.

There's probably a variety of ways to do this, but this seemed the most
flexible and easiest to implement.

-Jon
Homeworkout.com

====== Start order.cgi

#!/usr/bin/perl

open (OUT, ">[PATH_TO_MY_ROOTDIR]/orders/data.txt") || die "cant open
outputfile";

my $buffer;
my $method;
if ($ENV{'REQUEST_METHOD'} =~ /get/i) {
$buffer = $ENV{'QUERY_STRING'};
$method = "Get";
}
else {
read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
$method = "Post";
}

my $pair;
my @nvpairs = split(/&/, $buffer);
foreach $pair (@nvpairs)
{
($name, $value) = split(/=/, $pair);

$name =~ tr/+/ /;
$name =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
$value =~ tr/+/ /;
$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;

# Use whatever output format you like in the line below
print OUT "$name ::==:: $value\n";
}

close(OUT);

# The line below may have wrapped - should be one line
my $output = `[PATH_TO_PHP_EXECUTABLE]/php
[PATH_TO_MY_ROOTDIR]/orders/orderprocess.php`;

====== End order.cgi

[J Graham]

Yes, these would of course work as well. I was just demonstrating how I did
it.

The reason I had the Order API cgi call a seperate script is for
flexiblity... I don't have direct access to the Order API cgi script. I
write it and then submit it to the system admin who then copies it to some
cgi directory... any time I need to make a change I have to resubmit it -
this can be a pain when testing. So to solve that I just made the Order API
cgi script as simple as possible and had all of the real processing done by
a script that I can easily change and update.

Other issues I ran into - other than not having direct access to the
OrderAPI cgi - include not being able to connect to our database via perl -
missing modules. And we must use perl for our Order API cgi as I tried PHP
and it doesn't work - not sure if this is a ShopSite or ISP related issue.
Also the fact that I'm not a perl programmer (not really interested) so most
of the programming was to be done in PHP if possible.

If we had direct access to the OrderAPI cgi AND I could use PHP for the
OrderAPI script then I probably would have.

Once again you are correct in saying that I could just pass then entire
query, but this is just the way I decided to do it... It also makes the
input stream (or file) much more readable. If you have to parse it either
way regardless, you may as well make it readable.

It works. Just thought I'd try and let folks, especially non-perl folks,
one way of doing it that works well for us - not one missed order. I hope
it was helpful/useful to some folks.

-Jon


"Loren" <loren_d_c@yahoo.com> wrote in message
news:3D41F05F.D059A553@yahoo.com...

You could also write directly to the MySQL database from within the
Order API cgi. If you have to do the same amount/kind of coding from
inside the PHP file, then why not cut down on the amount of code and
just do it all in the same file. Or if you prefer writing the SQL stuff
in PHP, you could just do the whole thing in PHP, there is no rule that
says the Order API program has to be Perl, it can be anything that can
accept the CGI-type name=value pairs.

Also, rather than loop through the whole query string sent to the cgi,
if you use the perl CGI module (PHP probably has a similar module) you
can just call a param by name and get it's value. The sample
custom-dump.pl just loops through the values like it does because it is
generic and is just printing out all of the variables passed to it,
reagardless of their name. But since in writing an application you will
know what specific variables you need, it is much simpler to use the CGI
module and just get it by it's name (shown below).

Here is a bit of code that I have posted before that inserts certain
order info into an SQL database using the perl DBI module (in this case
the database is PostgreSQL, but the DBI stuff for MySQL is very
similar):

===================================
#!/usr/bin/perl

use DBI;
use CGI qw(:standard); # need the standard to do some special stuff not
included in this sample

# get all name value pairs into $formIn
my $formIn = new CGI;

# now start getting variables that we need
$orderid = $formIn->param('O-OrderNum');

...
snip
In this section of code I got all of the universal cart values I needed
just like I got the ShopSite order number above (which is the nice thing
about the CGI module, makes getting the variable values very easy if you
know their names which you should because they are in the documentation
or you can see them via the custom-dump.pl). I then did some looping
stuff to get each product's unique info (from the Bxx- variables) into
arrays, which just happened to be what the particular project called
for.
/snip
...

# connect to the postgres database called mydatabase authenticating as
the database user myuser
$dbh = DBI->connect('dbi:Pg:dbname=mydatabase', 'myuser', undef, {
RaiseError => 1, AutoCommit => 1 });

# print loop to print each unique line item
for ($i = 0; $i < $uniqueitems; $i++){

# create the SQL statement to insert to the lstrans table called
transactions
$sql = "INSERT INTO transactions VALUES ( '$orderid', '$siteid',
'$timeentered', '$timecompleted', '@skuarray[$i]', '@quantityarray[$i]',
'@amountarray[$i]','$currency', '$email', '$paymenttype', '$zipcode',
'@namearray[$i]' )"; # all of this should be on one line in the perl
file

# now do the SQL statement
$dbh->do($sql) || die("INSERT error: DBI::errstr");

# end the print loop
}

# close/disconnect from the database
$dbh->disconnect;
============================================















J Graham wrote:

Thought I'd share how I insert our orders into our MySql database as
they
are processed by ShopSite. This technique uses the Order API feature of
ShopSite v6 as well as PHP and of course MySql.

Below is a perl cgi script I wrote that can be submitted as and ShopSite
Order API cgi script. It's basically the same as the sample
custom-dump.pl
script, except I write the order data to a file. It's the first step in
storing your orders to a MySQL database as they are processed by
Shopsite in
real-time.

At the end of this script I execute a PHP script that then opens the
file to
which I just wrote the order data. The PHP script then parses the data
file
and creates an insert statement into my MySQL database.

There's probably a variety of ways to do this, but this seemed the most
flexible and easiest to implement.

-Jon
Homeworkout.com

====== Start order.cgi

#!/usr/bin/perl

open (OUT, ">[PATH_TO_MY_ROOTDIR]/orders/data.txt") || die "cant open
outputfile";

my $buffer;
my $method;
if ($ENV{'REQUEST_METHOD'} =~ /get/i) {
$buffer = $ENV{'QUERY_STRING'};
$method = "Get";
}
else {
read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
$method = "Post";
}

my $pair;
my @nvpairs = split(/&/, $buffer);
foreach $pair (@nvpairs)
{
($name, $value) = split(/=/, $pair);

$name =~ tr/+/ /;
$name =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
$value =~ tr/+/ /;
$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;

# Use whatever output format you like in the line below
print OUT "$name ::==:: $value\n";
}

close(OUT);

# The line below may have wrapped - should be one line
my $output = `[PATH_TO_PHP_EXECUTABLE]/php
[PATH_TO_MY_ROOTDIR]/orders/orderprocess.php`;

====== End order.cgi

[Andrew Greaves]

I don't know anything about php (yet), but is something similar to this
workable in .asp? at least the script that inserts into mySQL...


"Keith Palmer" <keithp@logosoftwear.com> wrote in message
news:ai3ibj$3n6$1@support.shopsite.com...

Can you elaborate on this a little more... do you mean that I could
replace
the entire Shopsite Order API with something custom written in PHP?

So I could essentially entirely bypass the Shopsite Orders section of the
Shopsite database if I wanted, go directly to a half-way decent database
that I could customize and use with other applications?




"J Graham" <jkgraham@homeworkout.com> wrote in message
news:ahso8q$duc$1@support.shopsite.com...
Thought I'd share how I insert our orders into our MySql database as
they
are processed by ShopSite. This technique uses the Order API feature of
ShopSite v6 as well as PHP and of course MySql.

Below is a perl cgi script I wrote that can be submitted as and ShopSite
Order API cgi script. It's basically the same as the sample
custom-dump.pl
script, except I write the order data to a file. It's the first step in
storing your orders to a MySQL database as they are processed by
Shopsite
in
real-time.

At the end of this script I execute a PHP script that then opens the
file
to
which I just wrote the order data. The PHP script then parses the data
file
and creates an insert statement into my MySQL database.

There's probably a variety of ways to do this, but this seemed the most
flexible and easiest to implement.

-Jon
Homeworkout.com

====== Start order.cgi

#!/usr/bin/perl

open (OUT, ">[PATH_TO_MY_ROOTDIR]/orders/data.txt") || die "cant open
outputfile";

my $buffer;
my $method;
if ($ENV{'REQUEST_METHOD'} =~ /get/i) {
$buffer = $ENV{'QUERY_STRING'};
$method = "Get";
}
else {
read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
$method = "Post";
}

my $pair;
my @nvpairs = split(/&/, $buffer);
foreach $pair (@nvpairs)
{
($name, $value) = split(/=/, $pair);

$name =~ tr/+/ /;
$name =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
$value =~ tr/+/ /;
$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;

# Use whatever output format you like in the line below
print OUT "$name ::==:: $value\n";
}

close(OUT);

# The line below may have wrapped - should be one line
my $output = `[PATH_TO_PHP_EXECUTABLE]/php
[PATH_TO_MY_ROOTDIR]/orders/orderprocess.php`;

====== End order.cgi