Помогите разобраться с PHP для выгрузки в JSON

[ionesu]

Добрый день!

Требуется помощь в написании маленького скрипта для выгрузки JSON - это начатый скрипт

<?php
// Initialize variable for database credentials
$dbhost = 'localhost';
$dbuser = 'dbuser';
$dbpass = 'dbpass';
$dbname = 'dbname';

//Create database connection
  $dblink = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

//Check connection was successful
  if ($dblink->connect_errno) {
     printf("Failed to connect to database");
     exit();
  }

//Fetch rows from table
  $result = $dblink->query("SELECT * FROM oc_order");
//store the entire response
$response = array();
//the array that will hold
$customer = array();
while($row=$result->fetch_assoc()) //mysql_fetch_array($sql)
{ 
$customerid=$row['customer_id']; 
$customername=$row['firstname'].' '.$row['lastname'];
$customercurrency=$row['currency_code']; 
$customercountry=$row['payment_country']; 
$customercity=$row['payment_city'];
$customeradress=$row['payment_address_1'].' '.$row['payment_address_2'];
$customerpostcode=$row['payment_postcode'];
$customertelephone=$row['telephone']; 
$customerfax=$row['fax']; 
$customeremail=$row['email'];
//each item from the rows go in their respective vars 
$customer[] = array('CustomerCode'=> $customerid, 'CustomerName'=> $customername, 'DefaultCurrency'=> $customercurrency, 'Country'=> $customercountry, 'City'=> $customercity, 'Address'=> $customeradress, 'PostCode'=> $customerpostcode, 'Phone'=> $customertelephone, 'Fax'=> $customerfax, 'Email'=> $customeremail); 
} 
//the posts array goes into the response
$response['Customer'] = $customer;
//creates the file
$fp = fopen('sales_order.json', 'w');
fwrite($fp, json_encode($response));
fclose($fp);

?> 

Разобрался с запросами в БД, выводом строк в JSON, но столкнулся с проблемой, что не могу воссоздать полностью структуру которая требуется. Это то, что у меня получилось -

 

{"Customer":[{"CustomerCode":"0","CustomerName":"Ivan SSsss","DefaultCurrency":"EUR","Country":"Germany","City":"rrrrrrrr","Address":"rrrrrrrrrr ","PostCode":"197022","Phone":"777777777777777","Fax":"","Email":"[email protected]"},{"CustomerCode":"0","CustomerName":"Ivan sushskov","DefaultCurrency":"EUR","Country":"El Salvador","City":"No","Address":"No 3333 ","PostCode":"1977888","Phone":"89333322222","Fax":"","Email":"[email protected]"}]}

 

А вот как требуется

{
  "OrderNumber": "1234",
  "OrderDate": "2015-03-10T00:00:00",
  "OrderCurrency": "PLN",
  "DeliveryDays": 3,
  "LeadTime": 1,
  "DeliveryName": "Plac budowy XYZ",
  "DeliveryCountry": "PL",
  "DeliveryCity": "Gdynia",
  "DeliveryAddress": "Testowa 12",
  "DeliveryPostCode": "81-123",
  "CustomerReferenceNumber": "MY ORDER XYZ",
  "DeliveryPhone": "+48605234987",
  "DeliveryContactPerson": "Jan Kowalski",
  "ShipperCode": "NO LIMIT",
  "ShopId": "VS2",
  "SalesLines": [
    {
      "LineNum": 1,
      "ItemCode": "1-4-0101-0036",
      "Qty": 4,
      "DiscountPercent": 10.50,
      "UnitSalesPrice": 1440.00,
      "Currency": "PLN",
      "ItemName": "VR1"
    },
    {
      "LineNum": 2,
      "ItemCode": "1-4-0101-0104",
      "Qty": 3,
      "DiscountPercent": 10.50,
      "UnitSalesPrice": 200.00,
      "Currency": "PLN",
      "ItemName": "Konsola montaїowa do  VR"
    }
  ],
  "Customer": {
    "CustomerCode": "1234567",
    "CustomerName": "Klima Dominator Sp. z o.o.",
    "DefaultCurrency": "PLN",
    "VatRegistrationNumber": "345-432-55-77",
    "Country": "PL",
    "City": "Skierniewice",
    "Address": "Przemysіowa 12/5",
    "PostCode": "45-456",
    "Phone": "+4822123456",
    "Fax": "+4822123458",
    "Email": "[email protected]"
  }
}

 

[ionesu]

1 час назад, nikifalex сказал:

ну копипаст не сработал, включайте разум

 

С этим разобрался.

Другой вопрос теперь, как связать таблицу oc_order и oc_order_product, чтобы связать эту информацию

 

 "SalesLines": [
    {
      "LineNum": 1,
      "ItemCode": "1-4-0101-0036",
      "Qty": 4,
      "DiscountPercent": 10.50,
      "UnitSalesPrice": 1440.00,
      "Currency": "PLN",
      "ItemName": "VR1"
    },
    {
      "LineNum": 2,
      "ItemCode": "1-4-0101-0104",
      "Qty": 3,
      "DiscountPercent": 10.50,
      "UnitSalesPrice": 200.00,
      "Currency": "PLN",
      "ItemName": "Konsola montaїowa do  VR"
    }
  ],

 

[ionesu]

С этим тоже разобрался связав их таким образом

 

$result = $dblink->query("SELECT * FROM oc_order, oc_order_product WHERE oc_order.order_id = oc_order_product.order_id");