LiveCode Forums.

Hello all.

I am using a MySQL Database and have been able to login and query data thus far, however, the shopping cart I am using puts everything together in massive strings.

I need to separate certain parts so that I can add them to an array of variables. The end result is to separate all the purchases from each record (there are hundreds) and figure out what they bought (like 2 gold short sleeve polo shirts, as shown below). Then, knowing what each item is worth, write a variable to that array record. For example, each gold short sleeve polo shirt = $2.00 and two were bought, so I write $4.00 into a variable. All of these same variables will later be added together to form the sum total of what is owed to the client.

These are the fields of the database:

Fields:

ID of person = id

Actual ordering = ipnline

Not sure the difference, but it's there anyway = orderline

ID of order = orderid

Date of order = created

Status of payment = payment_status

Verified or not = verified

Reason/Notes = reason

So, I have to first get the ID (the person shopping) and the orderID (I assume the present order). I then have to see if in that record, the payment_status = 'completed'. If it does, then the order went through and I can then parse what data I need. If not, then it was never an order, so ignore it.

Here is the kind of CRAP I have to deal with now (I have replaced client-sensitive data with nonsense text):
So, the important stuff is the following:

&mc_gross=51.80
&item_number1=&tax=2.80
&payment_date=11%3A18%3A52+Jul+25%2C+2014+PDT
&payment_status=Completed
&num_cart_items=2
&item_name1=Gold+Short+Sleeve+Polo+Shirt+Size+Adult+Small
&item_name2=shipping
&quantity1=2
&quantity2=1
&mc_gross_1=40.00
&mc_gross_2=9.00
&payment_gross=51.80

Most important is the number of items and what they actually are (so in the above example, 2 items were bought and it seems they were both the exact same shirt (&item_name1=Gold+Short+Sleeve+Polo+Shirt+Size+Adult+Small). That particular item's % equals $2.00 and 2 were purchased (&num_cart_items=2) so the variable to be written to is $4.00 (the payout commission for those items).

Unfortunately, each shopping cart item has a different % value, so the client is sending me a list of each item and its % payout.

SO, how to I parse the above text for each record and then store the important data into an array so that I can then figure out what was purchased, how many of them, assign the payout commission value to each item and then add up the sum total payout commission for that order AND then do that for ALL orders for a set number of days (every two weeks, monthly, etc). I assume I have to use the date/time field (&payment_date=11%3A18%3A52+Jul+25%2C+2014+PDT) to help segment it into 14 or 30 day increments?

The end result is a payout commission in a certain amount of days (so the client owes the vendor $200 over all orders for the last 30 days).

Thank you to ANYONE that can help me with this in any way, shape or form. I just need the right direction (how to parse the text, how to set up and write to the array and then how to do the math/divide it up into 2 week structures) and a bit of math help and I am pretty sure I can do the rest.

Mike

karmacomposer wrote:

Code: Select all

cmd=_notify-validate&mc_gross=51.80&protection_eligibility=Eligible&address_status=confirmed&item_number1=&tax=2.80&item_number2=&payer_id=blahblah&address_street=blah+blahblah+blah&payment_date=11%3A18%3A52+Jul+25%2C+2014+PDT&payment_status=Completed&charset=windows-1252&address_zip=blah&mc_shipping=0.00&mc_handling=0.00&first_name=blah&mc_fee=1.80&address_country_code=US&address_name=blah+blah&notify_version=3.8&custom=34_probably private info8&payer_status=unverified&business=fg%40blah-blah.com&address_country=United+States&num_cart_items=2&mc_handling1=0.00&mc_handling2=0.00&address_city=blah&verify_sign=noneofyourbusiness&payer_email=blahblahblah&mc_shipping1=0.00&mc_shipping2=0.00&txn_id=noneofyourbusiness&payment_type=instant&last_name=blah&address_state=FL&item_name1=Gold+Short+Sleeve+Polo+Shirt+Size+Adult+Small&receiver_email=fg%40blahblah.com&item_name2=shipping&payment_fee=1.80&quantity1=2&quantity2=1&receiver_id=private&txn_type=cart&mc_gross_1=40.00&mc_currency=USD&mc_gross_2=9.00&residence_country=US&transaction_subject=34_likelyprivate&payment_gross=51.80&ipn_track_id=private-a-HTTP/1.1 200 OK

Mike,

This looks like coming from paypal behind the scene, is it?

You might look at their site and development tools for an answer with already made code...

I've done something like that 3-4 years ago but unfortunately not in Livecode.

HTH,

Thierry

Hmmm.

That is very interesting. Yes, this is all done through paypal with a very standard shopping cart than bunches all the data together. I need to do it there because I have to make sure the shopper PAID successfully, which is written to the database in a certain field.

If you look above, I changed a lot because I talked with my client and it has to be done per purchased item and not the total items purchased. It seems there is a different commission dollar value per item.

I will look at paypal and see if their API has something for this, but in the end, it really is a substitution table (figure out the item and then assign the commission amount for that item - make an array to hold all commissions for a set period of time and then display that to the client). I may not need to go to Paypal and do anything.

However, I am not sure how to parse the text - new to LiveCode, but not to other languages. Looks like tokens may work the best. Just not sure how to capture exactly what I need using tokens. The token in question is, for example:

&item_name1=Gold+Short+Sleeve+Polo+Shirt+Size+Adult+Small

How do I know where that ENDS. Where it begins is easy. However, I believe the & separates all data, so I look for the trailing & and then grab everything between &item_name1= and the trailing &. In the example above, I also need to remove all plus signs and write the resulting text to a variable ("Gold Short Sleeve Polo Shirt Size Adult Small")

I also noticed that the &quantity has to do with the number of each item purchased, so its &item_name(number) times &quantity = payout value times &quantity for each item.

&item_name=shipping seems to be the end of all items, so that is where I cut off for that record.

However, I need to create an array because items purchased were 2 and only one item shows up, meaning they bought to of the same item. How do I parse the text, write to the array and figure out how to put the values into the array depending on the number of items purchased? Lastly, I need to assign a dollar amount (the commission payout) per item, which means a kind of lookup table (each item has a specific dollar amount payout).

Mike

karmacomposer wrote:Hmmm.

That is very interesting. Yes, this is all done through paypal with a very standard shopping cart than bunches all the data together.

Mike

Ok, a starter...
Please, don't trust me... experiment

Regards,

Thierry

Hi.

It looks like the ampersand is a perfect itemDelimiter here, no? If the order of data segments is always the same, this is a snap. Even if it was not, you can write an itemOffset function to order the several bits and pieces as needed.

Am I missing the point? The data looks like a big mess, but really isn't.

Craig Newman

Thierry wrote:

karmacomposer wrote:Hmmm.

That is very interesting. Yes, this is all done through paypal with a very standard shopping cart than bunches all the data together.

Mike

Ok, a starter...

Code: Select all

   put fld 1 into s -- fld 1 contains your datas
   put urldecode(s) into s
   replace "&"  with return in s
   put s

Please, don't trust me... experiment

Regards,

Thierry

Wow. That was a hell of a help!!!!!!

I may have a few more questions, but that really did the trick.

Mike

Hi Mike,

I made a little stack that puts your data into an array and reads from that array into some fields. If you want to see the array step through the code in the debugger.

Kind regards

Bernd

karmacomposer wrote: Wow. That was a hell of a help!!!!!!
Mike

You were lucky as I didn't suggest any regex this day!
It would have been really hellish for some

Thierry

Off topic (insider)

Thierry, no worry,

today is not my day, Craig thought I had emptied all the bottles of beer in my fridge

Kind regads

Thierry wrote:

karmacomposer wrote: Wow. That was a hell of a help!!!!!!
Mike

You were lucky as I didn't suggest any regex this day!
It would have been really hellish for some

Thierry

Let me tell you something:

Support is the reason I use or not use a product, language, etc.

Whether or not you work for LiveCode, it is support like this that makes me LOVE LiveCode.

Thank you very, very much for your help.

Sorry to be a dunce, but what is a RegEx?

Mike

bn wrote:Hi Mike,

I made a little stack that puts your data into an array and reads from that array into some fields. If you want to see the array step through the code in the debugger.

Kind regards

Bernd

THANK YOU!

I will study this example and learn from it/use it in my program.

I may just get this working soon.

Mike

bn wrote:Off topic (insider)
today is not my day, Craig thought I had emptied all the bottles of beer in my fridge

Umm, depend how many bottle were in the fridge...

Kind regards,

Thierry

This is OT to my own thread, but I noticed the backer badges on the right - I opted for the $500 kickstarter option (Indie all Commercial PLUS HTML 5 when it comes out) - so where's my badge?

LOL.

Mike

karmacomposer wrote: but what is a RegEx?
Mike

Might be a good start:

http://en.wikipedia.org/wiki/Regular_expression

Plus you can use regular expressions within Livecode too.
Check this forum for some examples..

Thierry

What you want is the split command, which is all set up to deal this type of thing. It separates the data by items and puts the whole thing into an array.
There is a PayPal script for LiveCode on my site here
http://hyperactivesw.com/cgipaypal/index.html

In particular, take a look at the checkOrderData function, which parses the incoming data.