Hi - I need to take the transaction XML datafeed and put the data into a mySQL database, properly parsed and put into separate tables etc etc. I could do this myself but, to be honest, I'm hoping that somebody has done something similar already. This is largely on the grounds that I'm lazy and a bit of a charlatan, and would much rather take some existing code and amend it rather than have to write it from scratch myself.
Has anybody published anything like this already?
cheers,
Gordon
Have you already checked out the integrations here:
http://wiki.foxycart.com/integration/start
While there isn't specifically an XML to MySQL example there, there are many examples of parsing out the XML. From there, it's just a matter of writing a simple insert query.
Does this help?
Lance
1) If you really want to do it "right", you'll need maybe a dozen tables. That, in and of itself, isn't a problem, but if you then want to reassemble an order you're talking about some pretty serious queries. In most cases it could work to grab the XML, get the pieces you actually care about to do whatever you're going to do with them (like date, ID, customer email and maybe name, etc.), then store the full XML (or a PHP serialized object) in a single field. That way you can get what you need for searching, filtering, or whatever, and if you _do_ need to drill down to see the entire thing you could just load up the object and do whatever you need to do.
2) You may want to look at using the API. Obviously it won't work for everything, but as of v0.7.0/1 it's much more functional than it was in previous versions, and that may save you considerable time and data replication.
http://wiki.foxycart.com/static/redirect/api
What're you looking to do?
Maybe this will get you started on your own database scheme. Note the users table at the end is extracted from the customer information including the password. Originally we used the password hash from foxy but several customers feared we could use it to clean out their card. A bogus fear - we dont know the password. We have a hash of it. We gave in to make them feel safer and generate a separate password. Customer is always right even if they say the world is flat.
create_table "customers", :force => true do |t|
t.string "foxy_id"
t.string "first_name"
t.string "last_name"
t.string "address1"
t.string "address2"
t.string "city"
t.string "state"
t.string "postal_code"
t.string "country"
t.string "email"
t.string "phone"
end
create_table "line_items", :force => true do |t|
t.integer "order_id"
t.integer "quantity"
t.string "product_code"
t.string "product_name"
t.decimal "product_price", :precision => 8, :scale => 2
t.string "category_code"
t.boolean "has_shipped", :default => false
end
create_table "orders", :force => true do |t|
t.integer "customer_id"
t.integer "foxy_tid"
t.datetime "date_bought"
t.string "purchase_order"
t.string "shipping_service_description"
t.decimal "product_total", :precision => 8, :scale => 2
t.decimal "tax_total", :precision => 8, :scale => 2
t.decimal "shipping_total", :precision => 8, :scale => 2
t.decimal "order_total", :precision => 8, :scale => 2
end
create_table "shippings", :force => true do |t|
t.integer "order_id"
t.string "first_name"
t.string "last_name"
t.string "address1"
t.string "address2"
t.string "city"
t.string "state"
t.string "postal_code"
t.string "country"
t.string "phone"
end
create_table "subscriptions", :force => true do |t|
t.integer "user_id"
t.string "product_name"
t.string "frequency"
t.datetime "start_date"
t.datetime "expiration"
t.boolean "stop_renewal", :default => false
t.string "sub_token_url"
end
create_table "users", :force => true do |t|
t.integer "customer_id"
t.string "email"
t.string "md5_password"
end
cheers,
Gordon
Gordon
cheers,
Gordon
// decrypt the XML
$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);
$FoxyDataArray = new SimpleXMLElement($FoxyData_decrypted);
$custID = $FoxyDataArray->transactions->transaction->transaction_details->transaction_detail->transaction_detail_options->transaction_detail_option->product_option_value;
$f_name = $FoxyDataArray->transactions->transaction->customer_first_name;
$l_name = $FoxyDataArray->transactions->transaction->customer_last_name;
$custPhone = $FoxyDataArray->transactions->transaction->customer_phone;
$duesPaid = $FoxyDataArray->transactions->transaction->transaction_details->transaction_detail->product_price;
$datePurchase = $FoxyDataArray->transactions->transaction->transaction_date;
if (!mysql_connect('localhost','USER','PASSWORD')) die("no connection to MySQL");
if (!mysql_select_db(myDB')) die("couldn't select database");
mysql_query("INSERT INTO myTable (CUSTOMERID, FNAME, LNAME, PHONE, AMT_PAID, DATE_PURCHASED ) VALUES ('".mysql_escape_string($custID) ."', '".mysql_escape_string($f_name) ."', '".mysql_escape_string($l_name) ."','".mysql_escape_string($custPhone) ."','".mysql_escape_string($duesPaid) ."','".mysql_escape_string($datePurchase) ."')");
}