ShopSite Forums

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

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

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

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