7 Replies Latest reply on Dec 11, 2013 11:59 PM by datanaut

    How can I extract data from a Xcart array.



      How can I extract data from a Xcart array.


           Xcart is a popular shopping cart. In one table called 'order_details' one field 'extra_data'  has text in it which is an array like this ;
           s:15:"product_options"; a:4:{
           i:465; s:4:"4213";
           i:466; s:4:"4220";
           i:467; s:4:"4227";
           i:473; s:0:"";
           s:5:"taxes"; a:1:{
           s:5:"taxid"; s:1:"2";
           s:8:"tax_name"; s:6:"VAT_20";
           s:7:"formula"; s:5:"ST+SH";
           s:12:"address_type"; s:1:"S";
           s:6:"active"; s:1:"Y";
           s:18:"price_includes_tax"; s:1:"Y";
           s:21:"display_including_tax"; s:1:"Y";
           s:12:"display_info"; s:0:"";
           s:9:"regnumber"; s:10:"6973114311";
           s:8:"priority"; s:1:"0";
           s:10:"rate_value"; d:20;
           s:9:"rate_type"; s:1:"%";
           s:16:"tax_display_name"; s:3:"VAT";
           s:17:"tax_value_precise"; d:36.5;
           s:9:"tax_value"; d:36.5;
           s:11:"taxed_price"; d:219;
           s:7:"display"; a:3:{
           s:5:"price"; s:6:"182.50";
           s:16:"discounted_price"; s:6:"182.50";
           s:8:"subtotal"; s:6:"182.50";
           s:8:"subtotal"; s:6:"182.50";
           s:6:"weight"; s:4:"0.00";
           s:10:"event_data"; N;
           I am trying to extract some data from this text field that is an array. The array is dynamic so I can't count on size to find the data I require. I could write something to 'walk' the array or maybe better still I could use a string search for the elements I want.

           My internet searches give me the impression that dealing with this sort of problem in FM Pro 12 Advanced V12.0v4  ain't easy. Is there a smarter way to make this text into something I can access the data from much easier than the options I've mentioned. I've been using FM Pro for a month and write some PHP so if you can take that into consideration it would be a help.

        • 1. Re: How can I extract data from a Xcart array.

               What I recommend is that you use Import Records to do an XML import of this data. That should strip out most/all of the extra "stuff" leaving you just data in fields and records of a FileMaker table--which then should be fairly easy to work with.

               This can be done even if you are currently planning to paste this text into a text field in FileMaker as a script can export the text and then use Import Records to pull it back into the table.

          • 2. Re: How can I extract data from a Xcart array.

                 Thanks for the reply Phil,

                 I am connected via ODBC to the xcart MySQL database and It's just the one field from each of the the records in the 'order_details' table that I'm trying to deal with. It's the data within the text field that is laid out as an array that i'm interested in.

                 I've not understood you suggestion but that might well be because I've not described the question very well. 

            • 3. Re: How can I extract data from a Xcart array.

                   I remain convinced that you need to do an XML import as the by far simplest way to do this. And you can import records from an ODBC source, so on paper this sounds quite possible, though it's not something that I myself have tried. It might even require exporting the contents of this ODBC field to your temporary folder as text and then importing it via XML and an XSLT "grammar" set up to parse the data for you. Once that is accomplished, getting the data from that "one field" amongst all the other XML "stuff" shown in your example should be very straight forward.

                   But I will also note that you haven't actually identified precisely what data from that mass you actually need to extract.

              • 4. Re: How can I extract data from a Xcart array.

                     Hi Phil,

                     I'll explain what I want while I look into XML and XSLT

                     The product_options are my first interest. Here are the first part of that string from the 'extra_data' field. I've reformatted it for clarity but the actual data has no spaces in it.
                     s:15:"product_options";                a:4:{i:465; s:4:"4213";i:466; s:4:"4220";i:467; s:4:"4227";i:473; s:0:"";}
                          s:5:"taxes";                                      a:1:{s:6:"VAT_20";a:16:{s:5:"taxid"; s:1:"2";s:8:"tax_name"; s:6:"VAT_20";.........;}
                     The looks like an array of 6 records of two fields.   The first field is the option name and the second the option selected.
                     The products_options is what is of most importance and the option for that is an array. That that has two fields but a variable number of records.
                     i:465; is an int value of 465.
                     s:4:"4213" is a four char string containing "4213" but could s:3:"Red"
                     The taxes table option contains an array within an array and I'd like to get at some of those tax values as well because we ship internationally. 

                     Thanks for already taking time to look at this.  
                • 5. Re: How can I extract data from a Xcart array.

                       There are custom functions for parsing this data and you can use the various text functions that are available in FileMaker to parse this text as well, but I think the import, if you can get it to work will solve a lot of headaches here.

                  • 6. Re: How can I extract data from a Xcart array.

                         Thanks again Phil,

                         I looked at XML but couldn't get my head around it so I'm off  down the 'walk the data' method.  Your "custom functions for parsing this data" got me this.


                         I will go back and look again at the XML as it a skill that will be reusable but I don't have the time at the moment. If you know any good XML resorses I'd be happy to follow links.

                         So I'm writing my own little script and it will grab anything I want out of this structure. I could write a custom function but that's for another day too. I could grab a custom function and tweek it but I'd rather write my own plus it helps me get the hang of FM.

                    • 7. Re: How can I extract data from a Xcart array.

                           Xcart uses PHP and Smarty. The array I am trying to get data out of is a serialized array.

                           In PHP I can use this

                           $phpArray = unserialize($xcart_extra_data);

                           I can't find a FileMaker equivalent. Maybe you know of one?