Having issue with my csv export because of how the json data is formated for an order.
{"vendorOrderNumber":"0137061","subtotal":38.95,"total":45.3,"email":"Donnar544@comcast.net","externalTransactionId":"5GS82233RB608634T","paymentModule":"PayPalStandard","paymentMethod":"PayPal / Credit Card","tax":0,"ipAddress":"73.130.161.92","couponDiscount":0,"paymentStatus":"PAID","paymentMessage":"Your order has been approved","fulfillmentStatus":"AWAITING_PROCESSING","orderNumber":7061,"refererUrl":"http://fvhardmerchandise.com/shop.html","volumeDiscount":0,"customerId":24198242,"membershipBasedDiscount":0,"totalAndMembershipBasedDiscount":0,"discount":0,"usdTotal":45.3,"globalReferer":"http://www.bing.com/search?q=wicked+tuna+hard+merchandise&qs=RI&pq=wicked+tuna+merchandise&sk=AS1&sc=5-23&sp=2&cvid=7A53D47A788E49C39B6DC837AE8D3E82&FORM=QBLH&ghc=1","createDate":"2016-01-08 01:10:18 +0000","updateDate":"2016-01-08 01:12:07 +0000","createTimestamp":1452215418,"updateTimestamp":1452215527,"items":[{"id":69376148,"productId":35290579,"categoryId":2455976,"price":14.95,"productPrice":14.95,"sku":"00275","quantity":1,"shortDescription":"Its finally here - an item lots of you have been waiting for! The Capt. Marciano Bobblehead in living color to keep yo...","tax":0,"shipping":3.0078947368421054,"quantityInStock":195,"name":"Capt. Marciano Bobblehead","isShippingRequired":true,"weight":0.9,"trackQuantity":true,"fixedShippingRateOnly":false,"imageUrl":"https://dpbfm6h358sh7.cloudfront.net/images/1039296/222187919.jpg","smallThumbnailUrl":"https://dpbfm6h358sh7.cloudfront.net/images/1039296/222187923.jpg","fixedShippingRate":0,"digital":false,"productAvailable":true,"couponApplied":false},{"id":69376149,"productId":35271535,"categoryId":2455976,"price":24,"productPrice":24,"sku":"00351","quantity":1,"shortDescription":"This slightly redesigned shirt from Hard Merchandise now has the CUDA brand knife logo on the back in their awesome whi...","tax":0,"shipping":3.3421052631578942,"quantityInStock":71,"name":"Tech Fabric Friends of Hard Merchandise Shirt","isShippingRequired":true,"weight":1,"trackQuantity":true,"fixedShippingRateOnly":false,"imageUrl":"https://dpbfm6h358sh7.cloudfront.net/images/1039296/313266743.jpg","smallThumbnailUrl":"https://dpbfm6h358sh7.cloudfront.net/images/1039296/313266748.jpg","fixedShippingRate":0,"digital":false,"productAvailable":true,"couponApplied":false,"selectedOptions":[{"name":"Size","value":"X-Large","valuesArray":["X-Large"],"type":"CHOICE"}]}],"billingPerson":{"name":"Dale E Read Sr","companyName":"","street":"544 Palm City Park","city":"Annville","countryCode":"US","countryName":"United States","postalCode":"17003","stateOrProvinceCode":"PA","stateOrProvinceName":"Pennsylvania","phone":"7176754357"},"shippingPerson":{"name":"Dale E Read Sr","companyName":"","street":"544 Palm City Park","city":"Annville","countryCode":"US","countryName":"United States","postalCode":"17003","stateOrProvinceCode":"PA","stateOrProvinceName":"Pennsylvania","phone":"7176754357"},"shippingOption":{"shippingCarrierName":"U.S.P.S.","shippingMethodName":"U.S.P.S. Priority Mail 2-Dayâ„¢","shippingRate":6.35,"estimatedTransitTime":"1-3"},"handlingFee":{"name":"Handling Fee","value":0,"description":""},"additionalInfo":{},"paymentParams":{},"hidden":false}]}
Issue comes in when the json shows the "items" details. http://developers.ecwid.com/api-documentation#get-order-details
"items":[{"id":69376148,"productId":35290579,"categoryId":2455976,"price":14.95,......
csv file gets exported but when it hits this part of the json data it outputs the next few columns as "Array" "Array" etc...
PHP File:
<?php
# An HTTP GET request example
$url = 'https://app.ecwid.com/api/v3/{STOREID}/orders?paymentStatus=PAID&fulfillmentStatus=AWAITING_PROCESSING&token={TOKEN}';
$ch = curl_init($url);
curl_setopt($ch, CURLOPT_TIMEOUT, 5);
curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 5);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$data = curl_exec($ch);
curl_close($ch);
echo $data;
$json_obj = json_decode($data, true);
$fp = fopen('hardmerch_orders.csv', 'a');
foreach ($json_obj['items'] as $items) {
fputcsv($fp, $items);
}
fclose($fp);
?>
Starting to build off of Alex Comments below but a bit lost on all the nested arrays. Not sure if I am on the right track at all.
<?php
# An HTTP GET request example
$url = 'https://app.ecwid.com/api/v3/{STOREID}/orders?paymentStatus=PAID&fulfillmentStatus=AWAITING_PROCESSING&token={TOKEN}';
$ch = curl_init($url);
curl_setopt($ch, CURLOPT_TIMEOUT, 5);
curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 5);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$data = curl_exec($ch);
curl_close($ch);
// echo $data;
$json_obj = json_decode($data, true);
//Ecwid Shopping Cart
//http://developers.ecwid.com/api-documentation#orders
$output = array();
foreach ($json_obj['items'] as $item) {
$output['total'] = $item['total'];
$output['count'] = $item['count'];
$output['offset'] = $item['offset'];
$output['limit'] = $item['limit'];
$output['items'] = $item['items'];
$output['vendorOrderNumber'] = $item['items'][0]['vendorOrderNumber'];
$output['subtotal'] = $item['items'][0]['subtotal'];
$output['total'] = $item['items'][0]['total'];
$output['email'] = $item['items'][0]['email'];
$output['externalTransactionId'] = $item['items'][0]['externalTransactionId'];
$output['paymentModule'] = $item['items'][0]['paymentModule'];
$output['paymentMethod'] = $item['items'][0]['paymentMethod'];
$output['tax'] = $item['items'][0]['tax'];
$output['ipAddress'] = $item['items'][0]['ipAddress'];
$output['couponDiscount'] = $item['items'][0]['couponDiscount'];
$output['paymentStatus'] = $item['items'][0]['paymentStatus'];
$output['paymentMessage'] = $item['items'][0]['paymentMessage'];
$output['fulfillmentStatus'] = $item['items'][0]['fulfillmentStatus'];
$output['orderNumber'] = $item['items'][0]['orderNumber'];
$output['refererUrl'] = $item['items'][0]['refererUrl'];
$output['orderComments'] = $item['items'][0]['orderComments'];
$output['volumeDiscount'] = $item['items'][0]['volumeDiscount'];
$output['customerId'] = $item['items'][0]['customerId'];
$output['membershipBasedDiscount'] = $item['items'][0]['membershipBasedDiscount'];
$output['totalAndMembershipBasedDiscount'] = $item['items'][0]['totalAndMembershipBasedDiscount'];
$output['discount'] = $item['items'][0]['discount'];
$output['usdTotal'] = $item['items'][0]['usdTotal'];
$output['globalReferer'] = $item['items'][0]['globalReferer'];
$output['createDate'] = $item['items'][0]['createDate'];
$output['updateDate'] = $item['items'][0]['updateDate'];
$output['createTimestamp'] = $item['items'][0]['createTimestamp'];
$output['updateTimestamp'] = $item['items'][0]['updateTimestamp'];
$output['customerGroupId'] = $item['items'][0]['customerGroupId'];
$output['customerGroup'] = $item['items'][0]['customerGroup'];
//['discountCoupon']
$output['name'] = $item['discountCoupon'][0]['name'];
$output['code'] = $item['discountCoupon'][0]['code'];
$output['discountType'] = $item['discountCoupon'][0]['discountType'];
$output['status'] = $item['discountCoupon'][0]['status'];
$output['discount'] = $item['discountCoupon'][0]['discount'];
$output['launchDate'] = $item['discountCoupon'][0]['launchDate'];
$output['usesLimit'] = $item['discountCoupon'][0]['usesLimit'];
$output['repeatCustomerOnly'] = $item['discountCoupon'][0]['repeatCustomerOnly'];
$output['creationDate'] = $item['discountCoupon'][0]['creationDate'];
$output['orderCount'] = $item['discountCoupon'][0]['orderCount'];
//['items']
$output['id'] = $item['items'][0]['id'];
$output['productId'] = $item['items'][0]['productId'];
$output['categoryId'] = $item['items'][0]['categoryId'];
$output['price'] = $item['items'][0]['price'];
$output['productPrice'] = $item['items'][0]['productPrice'];
$output['weight'] = $item['items'][0]['weight'];
$output['sku'] = $item['items'][0]['sku'];
$output['quantity'] = $item['items'][0]['quantity'];
$output['shortDescription'] = $item['items'][0]['shortDescription'];
$output['tax'] = $item['items'][0]['tax'];
$output['shipping'] = $item['items'][0]['shipping'];
$output['quantityInStock'] = $item['items'][0]['quantityInStock'];
$output['name'] = $item['items'][0]['name'];
$output['tangible'] = $item['items'][0]['tangible'];
$output['trackQuantity'] = $item['items'][0]['trackQuantity'];
$output['fixedShippingRateOnly'] = $item['items'][0]['fixedShippingRateOnly'];
$output['imageUrl'] = $item['items'][0]['imageUrl'];
$output['fixedShippingRate'] = $item['items'][0]['fixedShippingRate'];
$output['digital'] = $item['items'][0]['digital'];
$output['productAvailable'] = $item['items'][0]['productAvailable'];
$output['couponApplied'] = $item['items'][0]['couponApplied'];
//['files']
$output['productFileId'] = $item['files'][0]['productFileId'];
$output['maxDownloads'] = $item['files'][0]['maxDownloads'];
$output['remainingDownloads'] = $item['files'][0]['remainingDownloads'];
$output['expire'] = $item['files'][0]['expire'];
$output['name'] = $item['files'][0]['name'];
$output['description'] = $item['files'][0]['description'];
$output['size'] = $item['files'][0]['size'];
$output['adminUrl'] = $item['files'][0]['adminUrl'];
$output['customerUrl'] = $item['files'][0]['customerUrl'];
//['selectedOptions']
$output['name'] = $item['selectedOptions'][0]['name'];
$output['value'] = $item['selectedOptions'][0]['value'];
$output['valuesArray'] = $item['selectedOptions'][0]['valuesArray'];
$output['Big'] = $item['selectedOptions'][0]['Big'];
$output['type'] = $item['selectedOptions'][0]['type'];
// THERE ARE MORE SELECTED OPTIONS BUT I AM LOST AT HOW THEY SHOULD BE ADDED TO THE ARRAY.
//['taxes']
$output['name'] = $item['taxes'][0]['name'];
$output['value'] = $item['taxes'][0]['value'];
$output['total'] = $item['taxes'][0]['total'];
$output['taxOnDiscountedSubtotal'] = $item['taxes'][0]['taxOnDiscountedSubtotal'];
$output['taxOnShipping'] = $item['taxes'][0]['taxOnShipping'];
//['billingPerson']
$output['name'] = $item['billingPerson'][0]['name'];
$output['companyName'] = $item['billingPerson'][0]['companyName'];
$output['street'] = $item['billingPerson'][0]['street'];
$output['city'] = $item['billingPerson'][0]['city'];
$output['countryCode'] = $item['billingPerson'][0]['countryCode'];
$output['countryName'] = $item['billingPerson'][0]['countryName'];
$output['postalCode'] = $item['billingPerson'][0]['postalCode'];
$output['stateOrProvinceCode'] = $item['billingPerson'][0]['stateOrProvinceCode'];
$output['stateOrProvinceName'] = $item['billingPerson'][0]['stateOrProvinceName'];
$output['phone'] = $item['billingPerson'][0]['phone'];
//['shippingPerson']
$output['name'] = $item['shippingPerson'][0]['name'];
$output['companyName'] = $item['shippingPerson'][0]['companyName'];
$output['street'] = $item['shippingPerson'][0]['street'];
$output['city'] = $item['shippingPerson'][0]['city'];
$output['countryCode'] = $item['shippingPerson'][0]['countryCode'];
$output['countryName'] = $item['shippingPerson'][0]['countryName'];
$output['postalCode'] = $item['shippingPerson'][0]['postalCode'];
$output['stateOrProvinceCode'] = $item['shippingPerson'][0]['stateOrProvinceCode'];
$output['stateOrProvinceName'] = $item['shippingPerson'][0]['stateOrProvinceName'];
$output['phone'] = $item['shippingPerson'][0]['phone'];
//['shippingOption']
$output['shippingMethodName'] = $item['shippingOption'][0]['shippingMethodName'];
$output['shippingRate'] = $item['shippingOption'][0]['shippingRate'];
$output['estimatedTransitTime'] = $item['shippingOption'][0]['estimatedTransitTime'];
//['handlingFee']
$output['name'] = $item['handlingFee'][0]['name'];
$output['value'] = $item['handlingFee'][0]['value'];
$output['description'] = $item['handlingFee'][0]['description'];
//['paymentParams']
$output['Company name'] = $item['paymentParams'][0]['Company name'];
$output['Job position'] = $item['paymentParams'][0]['Job position'];
$output['PO number'] = $item['paymentParams'][0]['PO number'];
//$output['"Buyer's full name'] = $item['paymentParams'][0]['Buyer's full name'];
//['discountInfo']
$output['value'] = $item['discountInfo'][0]['value'];
$output['type'] = $item['discountInfo'][0]['type'];
$output['base'] = $item['discountInfo'][0]['base'];
$output['orderTotal'] = $item['discountInfo'][0]['orderTotal'];
$output['hidden'] = $item['discountInfo'][0]['hidden'];
}
echo $output;
$fp = fopen('hardmerch_orders.csv', 'a');
foreach ($output as $line) {
fputcsv($fp, $items);
}
fclose($fp);
?>
Taking the sample you provided here http://developers.ecwid.com/api-documentation#get-order-details as a valid JSON.
After json_decode if you inspect $json_obj['items'] you will notice is an array of arrays.
The next loop cannot be safely converted to csv format since it contains further arrays
foreach ($json_obj['items'] as $item) {
print_r($item);
}
This will output
...
[imageUrl] => http://app.ecwid.com/default-store/00006-sq.jpg
[fixedShippingRate] => 1
[digital] => 1
[productAvailable] => 1
[couponApplied] =>
[files] => Array
(
[0] => Array
(
[productFileId] => 7215101
[maxDownloads] => 0
[remainingDownloads] => 0
[expire] => 2014-10-26 20:34:34 +0000
[name] => myfile.jpg
[description] => Sunflower
[size] => 54492
[adminUrl] => https://app.ecwid.com/api/v3/4870020/products/37208340/files/7215101?token=123123123
[customerUrl] => http://mysuperstore.ecwid.com/download/4870020/a2678e7d1d1c557c804c37e4/myfile.jpg
)
)
[selectedOptions] => Array
(
[0] => Array
(
[name] => Size
[value] => Big
[valuesArray] => Array
(
[0] => Big
)
[type] => CHOICE
)
...
Notice the inner arrays such as files,selectedOptions and more.
These will be printed as Array in your csv.
You need to manually build an array based on the data available. Like this
$output = array();
foreach ($json_obj['items'] as $item) {
$output['tax'] = $item['tax'];
$output['imageUrl'] = $item['imageUrl'];
$output['adminUrl'] = $item['files'][0]['adminUrl'];
// rinse and repeat as needed
}
Then loop over it and write the csv
$fp = fopen('file.csv', 'a');
foreach ($output as $line) {
fputcsv($fp, $items);
}
fclose($fp);