Extract data from Mysql (Wordpress) meta_value field

[SarasotaSam]

I have been using Filemaker for many years, but just recently connected Filemaker Pro 12 to a Wordpress database with ODBC and am confronted with trying to extract data from a meta_value field. I don;t have a clue!!!

 

Do I use EsecuteSQL?

 

Here is the example:

 

I have a meta_key of custom_field

and the meta_value is

a:5:{s:5:"phone";s:14:"(xxx) xxx-xxxx";s:16:"terms_conditions";s:1:"1";s:13:"refund_policy";s:523:"Since Company is offering non-tangible, irrevocable, digital goods; we generally do NOT issue refunds. During checkout, upon clicking the "Submit" button, your purchase is charged to the credit/debit card that you supplied. If you are taking advantage of our Free Trial, your credit card will not be charged until the 15 day trial period has ended. You may cancel your Free Trial any time before 15 days. All sales are final. You cannot cancel a purchase or receive a refund for a purchase after your Free Trial has ended.";s:16:"how_did_you_hear";s:10:"Web search";s:11:"member_type";s:5:"Trial";}

 

All the way at the end, I am trying to extract into a new calculation field "member_type";s:5:"Trial".

 

If someone can show me how to do this, it will help me extract data from other meta_value.

 

Thanks in advance for your help - Sam

 

[Fitch]

This question is not really about ESS or ExecuteSQL -- if I understand correctly, you have a block of text and you want to extract a portion of it.

 

I'd suggest you go to http://www.briandunning.com/filemaker-custom-functions/ and search for "parse" -- that should get you started.

[brianrich]

This article from Makah Encarnacao of Soliant Consulting might give you some pointers about parsing the data into Filemaker

 

http://www.soliantconsulting.com/blog/2011/12/how-to-parse-json-or-any-other-language-in-filemaker

 

Brian

[SarasotaSam]

Thank you both. I will look both links over and see if my mind can do it.

 

Sam

[comment]

This (extracting a specific part from a given text) is actually rather trivial. The problem here is that we only have one example and no indication what is constant in the given text. Normally, you would define the task in terms such as 'extract the part in quotes immediately after the last occurrence of "s:11:"' (returning "member_type" in your example), or similar.

 

Once you have that, it's easy to use Position() to find the start and end points of the desired part, and Middle() to extract it - see, for example:

http://fmforums.com/forum/topic/59491-advanced-text-calculation/#entry281168.