Question

First let me begin with what we re doing here: We re pulling an XML file from 1 server and placing it into another server which is where the php code is sitting too. Then some of the XML data needs to be imported into a local MySQL db. The XML is a copy of orders for an online store. Some orders contain multiple items the person ordered, some don t if they only ordered one item.

So for the XML example we have:

<?xml version="1.0" encoding="UTF-8"?>
<xmldata>
  <Orders>
    <OrderID>2119</OrderID>
    <AddressValidated>Y</AddressValidated>
    <Affiliate_Commissionable_Value>2.0000</Affiliate_Commissionable_Value>
    <AuthHash>d995fd9424fb99e80f58841a0ff2340f</AuthHash>
    <AVS />
    <BillingAddress1>122 27th West</BillingAddress1>
    <BillingAddress2>Floor 11</BillingAddress2>
    <BillingCity>New York</BillingCity>
    <BillingCompanyName />
    <BillingCountry>United States</BillingCountry>
    <BillingFaxNumber />
    <BillingFirstName>Dan</BillingFirstName>
    <BillingLastName>Test</BillingLastName>
    <BillingPhoneNumber>646456777</BillingPhoneNumber>
    <BillingPostalCode>10001</BillingPostalCode>
    <BillingState>NY</BillingState>
    <CardHoldersName />
    <CC_Last4 />
    <CreditCardAuthorizationDate>3/30/2012 11:52:00 AM</CreditCardAuthorizationDate>
    <CreditCardAuthorizationNumber>MANUAL</CreditCardAuthorizationNumber>
    <CreditCardTransactionID />
    <Customer_IPAddress>184.56.49.45</Customer_IPAddress>
    <CustomerID>19</CustomerID>
    <CVV2_Response />
    <GiftWrapNote />
    <IsAGift>N</IsAGift>
    <LastModBy>7</LastModBy>
    <LastModified>3/30/2012 1:39:00 PM</LastModified>
    <Locked>Y</Locked>
    <Order_Comments>Test sale - Don t process
Ignore statuses on this order</Order_Comments>
    <Order_Entry_System>PHONE</Order_Entry_System>
    <OrderDate>3/30/2012 11:01:00 AM</OrderDate>
    <OrderNotes />
    <OrderStatus>Ready to Ship</OrderStatus>
    <PaymentAmount>8.1600</PaymentAmount>
    <PaymentMethodID>17</PaymentMethodID>
    <PONum />
    <Processed_AutoEvents>Y</Processed_AutoEvents>
    <SalesRep_CustomerID>7</SalesRep_CustomerID>
    <SalesTax1>0.1700</SalesTax1>
    <SalesTax2>0.0000</SalesTax2>
    <SalesTax3>0.0000</SalesTax3>
    <SalesTaxRate>0</SalesTaxRate>
    <SalesTaxRate1>0.08375</SalesTaxRate1>
    <SalesTaxRate2>0</SalesTaxRate2>
    <SalesTaxRate3>0</SalesTaxRate3>
    <ShipAddress1>123 27th West</ShipAddress1>
    <ShipAddress2>Floor 1</ShipAddress2>
    <ShipCity>New York</ShipCity>
    <ShipCompanyName />
    <ShipCountry>United States</ShipCountry>
    <ShipFaxNumber />
    <ShipFirstName>Dan</ShipFirstName>
    <ShipLastName>Test</ShipLastName>
    <ShipPhoneNumber>646782342</ShipPhoneNumber>
    <Shipping_Locked>Y</Shipping_Locked>
    <ShippingMethodID>1</ShippingMethodID>
    <ShipPostalCode>10001</ShipPostalCode>
    <ShipResidential>Y</ShipResidential>
    <ShipState>NY</ShipState>
    <Stock_Priority>3</Stock_Priority>
    <Tax1_IgnoreNoTaxRules />
    <Tax1_Title>Tax (8.375%)</Tax1_Title>
    <Tax2_IgnoreNoTaxRules />
    <Tax2_IncludePrevious>0</Tax2_IncludePrevious>
    <Tax2_Title />
    <Tax3_IgnoreNoTaxRules />
    <Tax3_IncludePrevious>0</Tax3_IncludePrevious>
    <Tax3_Title />
    <Total_Payment_Authorized>0.0000</Total_Payment_Authorized>
    <Total_Payment_Received>7.0700</Total_Payment_Received>
    <TotalShippingCost>5.9900</TotalShippingCost>
    <VendorID>0</VendorID>
    <OrderDetails>
      <OrderDetailID>3374</OrderDetailID>
      <AutoDropShip>Y</AutoDropShip>
      <FreeShippingItem>Y</FreeShippingItem>
      <GiftTrakNumber>0</GiftTrakNumber>
      <GiftWrap />
      <GiftWrapCost>0.0000</GiftWrapCost>
      <GiftWrapNote />
      <IsKitID />
      <KitID />
      <Locked>Y</Locked>
      <OnOrder_Qty>0</OnOrder_Qty>
      <OptionIDs />
      <Options />
      <OrderID>2119</OrderID>
      <ProductCode>test-product</ProductCode>
      <ProductID>39200</ProductID>
      <ProductName>Test Product 1</ProductName>
      <ProductNote />
      <ProductPrice>1.0000</ProductPrice>
      <ProductWeight>1</ProductWeight>
      <QtyOnBackOrder>0</QtyOnBackOrder>
      <QtyOnHold>1</QtyOnHold>
      <QtyShipped>0</QtyShipped>
      <Quantity>1</Quantity>
      <TaxableProduct>Y</TaxableProduct>
      <TotalPrice>1.0000</TotalPrice>
      <Vendor_Price>0.0000</Vendor_Price>
      <Warehouses />
    </OrderDetails>
    <OrderDetails>
      <OrderDetailID>3386</OrderDetailID>
      <AutoDropShip />
      <FreeShippingItem>Y</FreeShippingItem>
      <GiftTrakNumber>0</GiftTrakNumber>
      <GiftWrap />
      <GiftWrapCost>0.0000</GiftWrapCost>
      <GiftWrapNote />
      <Locked>Y</Locked>
      <OnOrder_Qty>0</OnOrder_Qty>
      <Options />
      <OrderID>2119</OrderID>
      <ProductCode>test-product</ProductCode>
      <ProductID>39200</ProductID>
      <ProductName>Test Product 2</ProductName>
      <ProductNote />
      <ProductPrice>1.0000</ProductPrice>
      <ProductWeight>1</ProductWeight>
      <QtyOnBackOrder>0</QtyOnBackOrder>
      <QtyOnHold>1</QtyOnHold>
      <QtyShipped>0</QtyShipped>
      <Quantity>1</Quantity>
      <TaxableProduct>Y</TaxableProduct>
      <TotalPrice>1.0000</TotalPrice>
    </OrderDetails>
  </Orders>
  <Orders>
    <OrderID>2141</OrderID>
    <AddressValidated>Y</AddressValidated>
    <Affiliate_Commissionable_Value>1.0000</Affiliate_Commissionable_Value>
    <AuthHash>14cca8be8db44ca9943f0a6cbf592c83</AuthHash>
    <AVS />
    <BillingAddress1>122 27th West2</BillingAddress1>
    <BillingAddress2 />
    <BillingCity>New York2</BillingCity>
    <BillingCompanyName />
    <BillingCountry>United States</BillingCountry>
    <BillingFaxNumber />
    <BillingFirstName>Dan2</BillingFirstName>
    <BillingLastName>Test2</BillingLastName>
    <BillingPhoneNumber>646346322</BillingPhoneNumber>
    <BillingPostalCode>10002</BillingPostalCode>
    <BillingState>NY</BillingState>
    <CardHoldersName />
    <CC_Last4 />
    <CreditCardAuthorizationDate>3/30/2012 3:33:00 PM</CreditCardAuthorizationDate>
    <CreditCardAuthorizationNumber>MANUAL</CreditCardAuthorizationNumber>
    <CreditCardTransactionID />
    <Customer_IPAddress>56.75.23.34</Customer_IPAddress>
    <CustomerID>19</CustomerID>
    <CVV2_Response />
    <GiftWrapNote />
    <IsAGift>N</IsAGift>
    <LastModBy>7</LastModBy>
    <LastModified>3/30/2012 3:33:00 PM</LastModified>
    <Locked>Y</Locked>
    <Order_Comments>Test sale #2, don t process, ignore status.</Order_Comments>
    <Order_Entry_System>PHONE</Order_Entry_System>
    <OrderDate>3/30/2012 3:32:00 PM</OrderDate>
    <OrderNotes />
    <OrderStatus>Ready to Ship</OrderStatus>
    <PaymentAmount>7.0700</PaymentAmount>
    <PaymentMethodID>17</PaymentMethodID>
    <PONum />
    <Processed_AutoEvents>Y</Processed_AutoEvents>
    <SalesRep_CustomerID>7</SalesRep_CustomerID>
    <SalesTax1>0.0800</SalesTax1>
    <SalesTax2>0.0000</SalesTax2>
    <SalesTax3>0.0000</SalesTax3>
    <SalesTaxRate>0</SalesTaxRate>
    <SalesTaxRate1>0.08375</SalesTaxRate1>
    <SalesTaxRate2>0</SalesTaxRate2>
    <SalesTaxRate3>0</SalesTaxRate3>
    <ShipAddress1>123 27th West2</ShipAddress1>
    <ShipAddress2 />
    <ShipCity>New York2</ShipCity>
    <ShipCompanyName />
    <ShipCountry>United States</ShipCountry>
    <ShipFaxNumber />
    <ShipFirstName>Dan2</ShipFirstName>
    <ShipLastName>Test2</ShipLastName>
    <ShipPhoneNumber>6461234789</ShipPhoneNumber>
    <Shipping_Locked>Y</Shipping_Locked>
    <ShippingMethodID>1</ShippingMethodID>
    <ShipPostalCode>10002</ShipPostalCode>
    <ShipResidential>Y</ShipResidential>
    <ShipState>NY</ShipState>
    <Stock_Priority>3</Stock_Priority>
    <Tax1_IgnoreNoTaxRules />
    <Tax1_Title>Tax (8.375%)</Tax1_Title>
    <Tax2_IgnoreNoTaxRules />
    <Tax2_IncludePrevious>0</Tax2_IncludePrevious>
    <Tax2_Title />
    <Tax3_IgnoreNoTaxRules />
    <Tax3_IncludePrevious>0</Tax3_IncludePrevious>
    <Tax3_Title />
    <Total_Payment_Authorized>0.0000</Total_Payment_Authorized>
    <Total_Payment_Received>7.0700</Total_Payment_Received>
    <TotalShippingCost>5.9900</TotalShippingCost>
    <VendorID>0</VendorID>
    <OrderDetails>
      <OrderDetailID>3404</OrderDetailID>
      <AutoDropShip>Y</AutoDropShip>
      <FreeShippingItem>Y</FreeShippingItem>
      <GiftTrakNumber>0</GiftTrakNumber>
      <GiftWrap />
      <GiftWrapCost>0.0000</GiftWrapCost>
      <GiftWrapNote />
      <IsKitID />
      <KitID />
      <Locked>Y</Locked>
      <OnOrder_Qty>0</OnOrder_Qty>
      <OptionIDs />
      <Options />
      <OrderID>2141</OrderID>
      <ProductCode>test-product</ProductCode>
      <ProductID>39200</ProductID>
      <ProductName>Test Product</ProductName>
      <ProductNote />
      <ProductPrice>1.0000</ProductPrice>
      <ProductWeight>1</ProductWeight>
      <QtyOnBackOrder>0</QtyOnBackOrder>
      <QtyOnHold>1</QtyOnHold>
      <QtyShipped>0</QtyShipped>
      <Quantity>1</Quantity>
      <TaxableProduct>Y</TaxableProduct>
      <TotalPrice>1.0000</TotalPrice>
      <Vendor_Price>0.0000</Vendor_Price>
      <Warehouses />
    </OrderDetails>
  </Orders>
</xmldata>

由于你可以看到第1项命令包含2条<编码>和>;OrderDetails>,而第2条仅载有一条。

I have created four functions so far that get me very close.

processxml();
processxmldetails();
vl_readytoship_download();
csvimport();

第一批从其他服务器进口了xml文档,以便我们能够工作。

function vl_readytoship_download() {
echo "Downloading Ready To Ship Orders...<br>";
$ch = curl_init("http://www.link.to/vl_rts.xml");
$fh = fopen("vl_rts.xml", "w");
curl_setopt($ch, CURLOPT_FILE, $fh);
curl_exec($ch);
curl_close($ch);
}

The second function converts the XML to CSV so we can better read it for importing (This step may not be required or the best method, which is why I m here, however, it does work.)

function processxml() {
vl_readytoship_download();
echo "Processing Ready To Ship Orders...<br>";
$filexml= vl_rts.xml ;
if (file_exists($filexml)) {
    $xml = simplexml_load_file($filexml);
$f = fopen( vl_rts.csv ,  w );
foreach ($xml->Orders as $Orders) {
    fputcsv($f, get_object_vars($Orders), , , " );
}
fclose($f);
}
}

Now I didn t at first notice that the CSV doesn t contain the data from any <OrderDetails> tags. I only realized it when I began work in the importing to MySQL function.

因此,我不是落后,而是提前获得<代码><Order>至少进口的数据:

function csvimport() { 
if(!file_exists("vl_rts.csv")) { echo "WARNING! CSV Not Found!<br>"; exit; }
$row = 0;
$mycsvfile = array();
if (($handle = fopen("vl_rts.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $num = count($data);
        $row++;
        for ($c=0; $c < $num; $c++) {
        }
        $mycsvfile[] = $data; //add the row to the main array.
    }
    fclose($handle);
}
echo "<br>Reading Rows:<br>";
//[row][column] so [1][$csvrow] = 2nd row, first column since 0=1
$csvrow = 0;
while ($mycsvfile[$csvrow][0] != $null) {
$query = mysql_query("SELECT * from vtiger_purchaseorder WHERE orderid=" . $mycsvfile[$csvrow][0]) or die(mysql_error());
if(mysql_num_rows($query)!=0)
{
//if row exists
$csvrow++;
echo "Row exists, changing rows...<br>";
echo "DO SOMETHING HERE, ANYTHING BUT THIS<br>"; //This is a "todo" spot for later
}
else //if row doesn t exist
{

$currentdate = date( Y-m-d );
$orderid = $mycsvfile[$csvrow][0];
$subject = "Uploading..."; //This is set to Uploading... now, in another function we ll come back change the subject to the name of the item sold.
$quoteid = $mycsvfile[$csvrow][0];
if ($mycsvfile[$csvrow][72] == 0) {
$vendorid = "1500"; //right now, new orders go into a temp vendor of 1500, then we ll change it based on the product ordered to see which vendor it should be assigned to.
echo "Vendor is " . $vendorid . "<br>";
$shipcity = "Shipcity"; This info is in <OrderDetails> which we don t have yet.

//This other database is vtiger, which has a lot of different area where it needs to store data, so here we insert it in a way vtiger can accept it.
$query1 = "SELECT * from vtiger_crmentity";
$query2 = "INSERT INTO vtiger_crmentity (smcreatorid,smownerid,modifiedby,setype,orderid) VALUES( 1 , 1 , 1 , PurchaseOrder , " . $orderid . " )";

$result1 = mysql_query($query1);
echo mysql_error() . "<br>";
$result2 = mysql_query($query2);
$crmid = mysql_insert_id();

$query3 = "SELECT * from vtiger_purchaseorder";
$query4 = "INSERT INTO vtiger_purchaseorder (purchaseorderid,subject,quoteid,vendorid,duedate,orderid) VALUES(" . $crmid . ", " . $subject . " , " . $quoteid . " , " . $vendorid . " , " . $currentdate . " , " . $orderid . " )";

$query5 = "SELECT * from vtiger_poshipads";
$query6 = "INSERT INTO vtiger_poshipads (poshipaddressid,ship_city,orderid) VALUES(" . $crmid . ", " . $shipcity . " , " . $orderid . " )";

echo mysql_error() . "<br>";
$result3 = mysql_query($query3);
echo mysql_error() . "<br>";
$result4 = mysql_query($query4);
echo mysql_error() . "<br>";
$result5 = mysql_query($query5);
echo mysql_error() . "<br>";
$result6 = mysql_query($query6);
echo mysql_error() . "<br>";
echo "CRMID " . $crmid . "<br>";

$csvrow++;
echo "Order doesn t exist, inserting order number " . $orderid . " from CSV row " . $csvrow . "<br>";
//CHANGE ORDER FROM READY TO SHIP TO PENDING SHIPMENT ON OTHER SITE (to do)


}
} 
}
echo "Import Complete.<br>";

}

现在迅速回头。因此,我们走了这段话。 XML文件来自其他服务器。我们从<代码><Orders>中提取了一切(除所有儿童外)物品,并把它列入特别安全局,现在我们已经将特别安全局的数据输入MySQL DB,以作出新的定购单。

新的定购单是存在的,但缺失的数据来自<代码><OrderDetails>tags。

我试图将所有<代码>和编号;OrderDetails>数据输入一个阵列,但我只收到第2个标号,载于第1个<代码><Order>。

理想的情况是,我将热衷于提取所有<代码><OrderDetails>tags,并将其插入另一个CSV文档。我从那儿起,用上述相同方法添加。我曾尝试过这一点,但加拿大社会安全委员会已经停止了,两行走了,我只剩下头两tag,并非全部。

我对任何事情都持开放态度,因此让我知道......。

Answer 1

你们应当直接处理XML。你试图将数据转换成CSV档案,从而失去了数据等级。

你可以使用简单的XML来读所有数据。它希望:

$xml = new SimpleXMLElement($content);
foreach ($xml->Orders as $order) {
  $order_id = $order->OrderId;
  $order_status = $order->OrderStatus;

  // Here we have a single order
  // You can insert this order into the database

  foreach ($order->OrderDetails as $order_details) {
    $order_detail_id = $order_details->OrderDetailID;
    $product_id = $order_details->ProductID;

    // Here we have a single order detail that belongs to $order_id
    // You can insert this order detail into the database
  }
}

友情链接