XML datafeed to mySQL via PHP

cowtancowtan Member
in Help edited May 2011
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
Comments
  • lancelance Member, Community Support Member
    @cowtan -

    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
  • brettbrett FoxyCart Team
    I totally get where you're coming from, but you may want to rethink the approach for two reasons.

    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?
  • a9ka9k Member
    edited May 2011
    Here's a rails schema for what we use. When you see something ending in _id it usually is the id of a record in another table for joins.
    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
  • cowtancowtan Member
    Hi Guys - Thanks for the various suggestions. At the end of the day I took the xml to csv example and cannibalised it. I don't have to deal with subscriptions and there is some of the other stuff I can throw away so it wasn't that big a deal really. I was going to post my code - not because it's that great but just because somebody might find it useful - but it looks like it would have made the post too long so I haven't.

    cheers,
    Gordon
  • cowtancowtan Member
    @Brett I meant to add, does the API meant that you're deprecating the XML datafeed to some extent?

    Gordon
  • sparkwebsparkweb Member, Integration Developer, FoxyShop, Order Desk
    Hey Gordon, can you post your code to Pastie and leave a link here for posterity? It sounds like a good solution to have access to.
  • cowtancowtan Member
    edited May 2011
    Ummmm.... Ok, here it is. I make no claims regarding the quality of my PHP so would be happy for someone to point out improvements (I'm aware I need to do things to the date before putting it into the database... just haven't got round to it yet) - php code here
  • brettbrett FoxyCart Team
    @Brett I meant to add, does the API meant that you're deprecating the XML datafeed to some extent?
    No, the instant datafeed will likely always remain, as instant notification is kind of the only way to do certain things. We _will_ likely add a JSON option at some point, and we've contemplated changing the way it works to be more a notification (so you could then query the API) rather than including all the details in the datafeed, but I don't imagine we'll ever get rid of something like what we have currently. Nothing to fear there ;)
  • cowtancowtan Member
    OK, thanks Brett. I might change to using the API rather than the XML at some point (I can see clear advantages) but the client isn't paying me to do it twice so for the moment I won't.

    cheers,
    Gordon
  • if (isset($_POST["FoxyData"])) {
    // 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) ."')");
    }
  • lukeluke FoxyCart Team
    Hello @alloyking. Do you have a question for us or...? Sorry, I'm just a bit confused by your post.
Sign In or Register to comment.