«
»

mySQL Database Structure for FoxyCart xml Datafeed

Posted by Corey on March 1, 2009 – 9:51 pm      
Filed under interwebs
Tagged as , , , , , , , , ,

Bah!  The lack of a mySQL or any other type of SQL structure for FoxyCart’s XML datafeed on this great interwebs has prompted me to post the one I created.  Feel free to use it.  Save some precious time.

This FoxyCart to mySQL db design should be viewed mainly as a solid start (I certainly modified it past this) and also as a good representation of FoxyCart’s version .8 XML datafeed in a usable database structure.  If you found this by searching Google, then you know what you’re looking at and know what to do with it… if otherwise, just email me.  I’m glad to help.

– phpMyAdmin SQL Dump
– version 2.11.9.3
– http://www.phpmyadmin.net
–
– Host: coreyfoster.com
– Generation Time: Mar 01, 2009 at 07:25 PM
– Server version: 5.0.67
– PHP Version: 5.2.6SET SQL_MODE=”NO_AUTO_VALUE_ON_ZERO”;–
– Database: `foxycart`
—- ———————————————————-
– Table structure for table `custom_field`
–CREATE TABLE IF NOT EXISTS `custom_field` (
`custom_field_id` int(11) NOT NULL auto_increment,
`transaction_id` int(11) NOT NULL,
`custom_field_name` varchar(200) NOT NULL,
`custom_field_value` varchar(200) NOT NULL,
PRIMARY KEY  (`custom_field_id`),
KEY `transaction_id` (`transaction_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;–
– Dumping data for table `custom_field`
—- ———————————————————-
– Table structure for table `discount`
–CREATE TABLE IF NOT EXISTS `discount` (
`discount_id` int(11) NOT NULL auto_increment,
`transaction_id` int(11) NOT NULL,
`discount_amount` float NOT NULL,
PRIMARY KEY  (`discount_id`),
KEY `transaction_id` (`transaction_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;–
– Dumping data for table `discount`
–

– ——————————————————–

–
– Table structure for table `shipto_address`
–

CREATE TABLE IF NOT EXISTS `shipto_address` (
`shipto_address_id` int(11) NOT NULL,
`transaction_id` int(11) NOT NULL,
`address_name` varchar(100) NOT NULL,
`shipto_first_name` varchar(100) NOT NULL,
`shipto_last_name` varchar(100) NOT NULL,
`shipto_company` varchar(100) NOT NULL,
`shipto_address1` varchar(100) NOT NULL,
`shipto_address2` varchar(100) NOT NULL,
`shipto_city` varchar(50) NOT NULL,
`shipto_state` varchar(50) NOT NULL,
`shipto_postal_code` int(11) NOT NULL,
`shipto_country` varchar(50) NOT NULL,
`shipto_shipping_service_description` varchar(100) NOT NULL,
`shipto_subtotal` float NOT NULL,
`shipto_tax_total` float NOT NULL,
`shipto_shipping_total` float NOT NULL,
`shipto_total` float NOT NULL,
PRIMARY KEY  (`shipto_address_id`),
KEY `transaction_id` (`transaction_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

–
– Dumping data for table `shipto_address`
–

– ——————————————————–

–
– Table structure for table `shipto_custom_field`
–

CREATE TABLE IF NOT EXISTS `shipto_custom_field` (
`shipto_custom_field_id` int(11) NOT NULL auto_increment,
`shipto_address_id` int(11) NOT NULL,
`shipto_custom_field_name` varchar(200) NOT NULL,
`shipto_custom_field_value` varchar(200) NOT NULL,
PRIMARY KEY  (`shipto_custom_field_id`),
KEY `transaction_id` (`shipto_address_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

–
– Dumping data for table `shipto_custom_field`
–

– ——————————————————–

–
– Table structure for table `shipto_tax`
–

CREATE TABLE IF NOT EXISTS `shipto_tax` (
`shipto_tax_id` int(11) NOT NULL auto_increment,
`shipto_address_id` int(11) NOT NULL,
`shipto_tax_rate` float NOT NULL,
`shipto_tax_name` varchar(200) NOT NULL,
`shipto_tax_amount` float NOT NULL,
PRIMARY KEY  (`shipto_tax_id`),
KEY `transaction_id` (`shipto_address_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

–
– Dumping data for table `shipto_tax`
–

– ——————————————————–

–
– Table structure for table `tax`
–

CREATE TABLE IF NOT EXISTS `tax` (
`tax_id` int(11) NOT NULL auto_increment,
`transaction_id` int(11) NOT NULL,
`tax_rate` float NOT NULL,
`tax_name` varchar(200) NOT NULL,
`tax_amount` float NOT NULL,
PRIMARY KEY  (`tax_id`),
KEY `transaction_id` (`transaction_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

–
– Dumping data for table `tax`
–

– ——————————————————–

–
– Table structure for table `transaction`
–

CREATE TABLE IF NOT EXISTS `transaction` (
`id` int(11) NOT NULL,
`store_id` int(11) NOT NULL,
`transaction_date` varchar(50) NOT NULL,
`processor_response` varchar(200) NOT NULL,
`customer_id` int(11) NOT NULL,
`customer_first_name` varchar(100) NOT NULL,
`customer_last_name` varchar(100) NOT NULL,
`customer_company` varchar(100) NOT NULL,
`customer_address1` varchar(100) NOT NULL,
`customer_address2` varchar(100) NOT NULL,
`customer_city` varchar(50) NOT NULL,
`customer_state` varchar(50) NOT NULL,
`customer_postal_code` int(11) NOT NULL,
`customer_country` varchar(50) NOT NULL,
`customer_phone` varchar(50) NOT NULL,
`customer_email` varchar(100) NOT NULL,
`customer_ip` varchar(100) NOT NULL,
`shipping_first_name` varchar(100) NOT NULL,
`shipping_last_name` varchar(100) NOT NULL,
`shipping_company` varchar(100) NOT NULL,
`shipping_address1` varchar(100) NOT NULL,
`shipping_address2` varchar(100) NOT NULL,
`shipping_city` varchar(50) NOT NULL,
`shipping_state` varchar(50) NOT NULL,
`shipping_postal_code` int(11) NOT NULL,
`shipping_country` varchar(50) NOT NULL,
`shipping_phone` varchar(50) NOT NULL,
`shipping_service_description` varchar(100) NOT NULL,
`purchase_order` varchar(50) NOT NULL,
`product_total` float NOT NULL,
`tax_total` float NOT NULL,
`shipping_total` float NOT NULL,
`order_total` float NOT NULL,
`payment_gateway_type` varchar(50) NOT NULL,
`receipt_url` varchar(200) NOT NULL,
`customer_password` varchar(100) NOT NULL,
PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

–
– Dumping data for table `transaction`
–

– ——————————————————–

–
– Table structure for table `transaction_detail`
–

CREATE TABLE IF NOT EXISTS `transaction_detail` (
`transaction_detail_id` int(11) NOT NULL,
`transaction_id` int(11) NOT NULL,
`product_name` varchar(200) NOT NULL,
`product_price` float NOT NULL,
`product_quantity` int(11) NOT NULL,
`product_weight` float NOT NULL,
`product_code` varchar(200) NOT NULL,
`subscription_frequency` varchar(100) NOT NULL,
`subscription_startdate` varchar(50) NOT NULL,
`next_transaction_date` varchar(50) NOT NULL,
`shipto` varchar(200) NOT NULL,
`category_description` varchar(200) NOT NULL,
`category_code` varchar(200) NOT NULL,
`product_delivery_type` varchar(200) NOT NULL,
PRIMARY KEY  (`transaction_detail_id`),
KEY `transaction_id` (`transaction_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

–
– Dumping data for table `transaction_detail`
–

– ——————————————————–

–
– Table structure for table `transaction_detail_option`
–

CREATE TABLE IF NOT EXISTS `transaction_detail_option` (
`transaction_detail_option_id` int(11) NOT NULL auto_increment,
`transaction_detail_id` int(11) NOT NULL,
`product_option_name` varchar(200) NOT NULL,
`product_option_value` varchar(200) NOT NULL,
`price_mod` float NOT NULL,
`weight_mod` float NOT NULL,
PRIMARY KEY  (`transaction_detail_option_id`),
KEY `transaction_detail_id` (`transaction_detail_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

–
– Dumping data for table `transaction_detail_option`
–

I promise to update this post soon with a PHP example of how to get the elusive data in FoxyCart’s XML feed into the SQL structure mentioned above.  Right now… I need some shut-eye.

Ah – the promised update (using SimpleXML):  (thanks for the reminder, Chris)

The following code is not complete as these feeds are used in different ways per the application.  I pulled this code from part of my final implementation, so it’s likely some of it will not be applicable to everyone.  So pick, choose and modify from the code below to achieve your goals.  As always – please contact me if you run into any snags (I know I did) or if the example is not clear.

<?

$key = “yourkeygoeshere”;

if (isset($_POST["FoxyData"])) {

$FoxyData_encrypted = urldecode($_POST["FoxyData"]);
$FoxyData_decrypted = rc4crypt::decrypt($key,$FoxyData_encrypted);

$enc = mb_detect_encoding($FoxyData_decrypted);
$FoxyData_decrypted = mb_convert_encoding($FoxyData_decrypted, ‘UTF-8′, $enc);

if (!mysql_connect(‘servername’, ‘username’, ‘password’)) die(“no connection to MySQL”);
if (!mysql_select_db(‘databasename’)) die(“couldn’t select database”);

$today = date(“Y-m-d H:i:s”);

$xml = new SimpleXMLElement($FoxyData_decrypted);

// Register a new feed event
$query = “insert into feed values (”, ‘$today’, ’0′)”;
$result = mysql_query($query);
if (!$result) {
echo “DB Error, could not query the database.\n”;
echo ‘MySQL Error: ‘ . mysql_error();
exit;
}
// Get the feed entry we just pushed
$query = “select * from feed order by feed_id desc limit 1″;
$result = mysql_query($query);
if (!$result) {
echo “DB Error, could not query the database.\n”;
echo ‘MySQL Error: ‘ . mysql_error();
exit;
}
$row = mysql_fetch_array($result);
$feed_id = $row['feed_id'];

foreach ($xml->xpath(‘//transaction’) as $transaction) {

// Add transaction data to db
$query = “insert into transaction values (
”,
‘$feed_id’,
‘$transaction->id’,
‘$transaction->store_id’,
‘$transaction->transaction_date’,
‘”.addSlashes($transaction->processor_response).”‘,
‘”.addSlashes($transaction->customer_id).”‘,
‘”.addSlashes($transaction->customer_first_name).”‘,
‘”.addSlashes($transaction->customer_last_name).”‘,
‘”.addSlashes($transaction->customer_company).”‘,
‘”.addSlashes($transaction->customer_address1).”‘,
‘”.addSlashes($transaction->customer_address2).”‘,
‘”.addSlashes($transaction->customer_city).”‘,
‘”.addSlashes($transaction->customer_state).”‘,
‘”.addSlashes($transaction->customer_postal_code).”‘,
‘”.addSlashes($transaction->customer_country).”‘,
‘”.addSlashes($transaction->customer_phone).”‘,
‘”.addSlashes($transaction->customer_email).”‘,
‘”.addSlashes($transaction->customer_ip).”‘,
‘”.addSlashes($transaction->shipping_first_name).”‘,
‘”.addSlashes($transaction->shipping_last_name).”‘,
‘”.addSlashes($transaction->shipping_company).”‘,
‘”.addSlashes($transaction->shipping_address1).”‘,
‘”.addSlashes($transaction->shipping_address2).”‘,
‘”.addSlashes($transaction->shipping_city).”‘,
‘”.addSlashes($transaction->shipping_state).”‘,
‘”.addSlashes($transaction->shipping_postal_code).”‘,
‘”.addSlashes($transaction->shipping_country).”‘,
‘”.addSlashes($transaction->shipping_phone).”‘,
‘”.addSlashes($transaction->shipping_service_description).”‘,
‘”.addSlashes($transaction->purchase_order).”‘,
‘”.addSlashes($transaction->product_total).”‘,
‘”.addSlashes($transaction->tax_total).”‘,
‘”.addSlashes($transaction->shipping_total).”‘,
‘”.addSlashes($transaction->order_total).”‘,
‘”.addSlashes($transaction->payment_gateway_type).”‘,
‘”.addSlashes($transaction->payment_receipt_url).”‘,
‘”.addSlashes($transaction->customer_password).”‘)”;
$result = mysql_query($query);
if (!$result) {
echo “DB Error, could not query the database.\n”;
echo ‘MySQL Error on transaction: ‘ . mysql_error();
exit;
}
// Get the id of the transaction we just pushed
$query = “select * from transaction where id = ‘$transaction->id’ order by transaction_id desc limit 1″;
$result = mysql_query($query);
if (!$result) {
echo “DB Error, could not query the database.\n”;
echo ‘MySQL Error: ‘ . mysql_error();
exit;
}
$row = mysql_fetch_array($result);
$transaction_id = $row['transaction_id'];
$foxy_trans_id = stripslashes($row['id']);
$customer_name = stripslashes($row['customer_first_name']).” “.stripslashes($row['customer_last_name']);
$customer_company = stripslashes($row['customer_company']);
$email_address = stripslashes($row['customer_email']);

// Get tax data
foreach ($xml->xpath(‘//tax’) as $tax) {
$query = “insert into tax values (
”,
‘$transaction_id’,
‘$tax->tax_rate’,
‘”.addSlashes($tax->tax_name).”‘,
‘$tax->tax_amount’)”;
$result = mysql_query($query);
if (!$result) {
echo “DB Error, could not query the database.\n”;
echo ‘MySQL Error on tax: ‘ . mysql_error();
exit;
}
}

// Get custom data
foreach ($xml->xpath(‘//custom_field’) as $custom_field) {
$query = “insert into custom_field values (
”,
‘$transaction_id’,
”,
‘”.addSlashes($custom_field).”‘)”;
$result = mysql_query($query);
if (!$result) {
echo “DB Error, could not query the database.\n”;
echo ‘MySQL Error on custom_field: ‘ . mysql_error();
exit;
}
}

// Get deeper data (discount in this case)
foreach ($xml->xpath(‘//transaction[id='.$foxy_trans_id.']/discounts/discount’) as $di) {
$query = “insert into discount values (
”,
‘$transaction_id’,
‘”.addSlashes($di->code).”‘,
‘”.addSlashes($di->name).”‘,
‘$di->amount’,
‘”.addSlashes($di->display).”‘,
‘”.addSlashes($di->coupon_discount_type).”‘,
‘”.addSlashes($di->coupon_discount_details).”‘)”;
//echo($query.’<br><br>’);
$result = mysql_query($query);
if (!$result) {
echo “DB Error, could not query the database.\n”;
echo ‘MySQL Error on transaction_detail: ‘ . mysql_error();
exit;
}
$disc_amount=$di->amount;
$disc_code=addSlashes($di->code);
}

// Get transaction detail data by id.
foreach ($xml->xpath(‘//transaction[id='.$foxy_trans_id.']/transaction_details/transaction_detail’) as $td) {
$query = “insert into transaction_detail values (
”,
‘$transaction_id’,
‘”.addSlashes($td->product_name).”‘,
‘$td->product_price’,
‘$td->product_quantity’,
‘$td->product_weight’,
‘$td->product_code’,
‘”.addSlashes($td->subscription_frequency).”‘,
‘”.addSlashes($td->subscription_startdate).”‘,
‘”.addSlashes($td->next_transaction_date).”‘,
‘”.addSlashes($td->shipto).”‘,
‘”.addSlashes($td->category_description).”‘,
‘”.addSlashes($td->category_code).”‘,
‘”.addSlashes($td->product_delivery_type).”‘)”;
//echo($query.’<br><br>’);
$result = mysql_query($query);
if (!$result) {
echo “DB Error, could not query the database.\n”;
echo ‘MySQL Error on transaction_detail: ‘ . mysql_error();
exit;
}
}
}

echo “foxy”;

?>
Comments RSS Feed   Post a comment   Trackback URL   Share on Twitter   Share on Facebook

Related Posts

5 Comments

  1. Chris says:
    August 5, 2009 at 1:45 pm

    Hey Corey,

    Great site first of all. I am using foxycart and want to dump the XML data to mySQL. I found a php file that foxycart provides on their wiki and it dumps all of the data into a single table row. Here is that code.

    mysql_query(“INSERT INTO foxy_orders (created_at,foxy_data) VALUES (NOW(),’”.mysql_escape_string($FoxyData_decrypted)”‘)”);

    The entire XML file that you document in this post is all thrown into foxy_data. How do I separate all that info and put them into their own tables like you have.

    Thanks for your time.
    Chris

  2. Corey says:
    August 6, 2009 at 10:12 am

    Hey hey, Chris. Thanks for the kind words about the site… wish I had more time to update it.

    I have updated the post to (hopefully) help you out. In my final code, I ended up doing a lot of custom haxxoring to achieve some project-specific goals and I have tried to remove most of that extra stuff. Depending on how familiar you are with PHP, you should have little trouble duplicating and modifying my code to break up the XML from Foxy and organize it neatly into your mySQL structure. Happy coding! And hit me up if I can help out any more.

  3. Nickie says:
    August 6, 2009 at 3:45 pm

    yes

  4. Chris says:
    August 6, 2009 at 6:17 pm

    Corey, this code is absolutely brilliant. i will definitely be telling others about how much you helped me. While browsing the foxycart forums, there are quite a few people that need help doing this exact thing. I will be sure to send them here.

    Thank you very much for your help.

  5. Corey says:
    August 7, 2009 at 8:02 am

    Right on – I’m glad I could help out.

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*