XMLReader php xml insert to mysql duplicates-CodePudding

I have an XML as:

<?xml version="1.0" encoding="UTF-8"?>
<products currency="EUR">
<product id="457654754" vat="13.0">
    <price gross="0.09" net="0.07"/>
    <sizes>
        <size id="0"  name="gfdgfdgfdg" panel_name="dfgfdgfdgfd" code_producer="fgdfgfdg"  
         iaiext:code_external="457547547547"  code="354643643643"  weight="4"  iaiext:weight_net="10" >
        <stock id="1" quantity="333"/>
        </size>
    </sizes>
</product>
</products>

when I parse it as:

<?php
$xml = new XMLReader;
$xml->open('XML_URL');
while ($xml->read()) {
if ($xml->nodeType === XMLReader::ELEMENT && $xml->name == 'product')
    echo $xml->getAttribute('id').'<br />';
if ($xml->nodeType === XMLReader::ELEMENT && $xml->name == 'price')
    echo $xml->getAttribute('net').'<br />';
if ($xml->nodeType === XMLReader::ELEMENT && $xml->name == 'size')
    echo $xml->getAttribute('iaiext:code_external').'<br />';
    if ($xml->nodeType === XMLReader::ELEMENT && $xml->name == 'stock')
    echo $xml->getAttribute('quantity').'<br />';
 }

 ?>

I get the values displayed correctly. But How can I insert them into a mysql table? I tried it inside the same while loop and I got instead of e.g 2500 rows over 48000 rows as duplicates. How can I achieve it?

database

CREATE TABLE `table` (
`auto_increm` int(11) NOT NULL,
`un_id` int(11) NOT NULL,
`price` decimal(15,2) NOT NULL,
`quantity` int(11) NOT NULL,
`ean` varchar(255) NOT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

 ALTER TABLE `table`
 ADD PRIMARY KEY (`auto_increm`);

 ALTER TABLE `table`
 MODIFY `auto_increm` int(11) NOT NULL AUTO_INCREMENT;
 COMMIT;

id attribute in php = un_id in mysql table

net attribute in php = price in mysql table

iaiext:code_external attribute in php = ean in mysql table

quantity attribute in php = quantity in mysql table.

In a flat XML I was always able to perform the insert into, such as:

<?php    
$doc = new DOMDocument();
$doc->load('feed.xml');
$arrFeeds = array();
foreach ($doc->getElementsByTagName('item') as $node) {
$itemRSS = array ( 
 'sku' => $node->getElementsByTagName('itemcode')->item(0)->nodeValue,
  'stock' => $node->getElementsByTagName('stock')->item(0)->nodeValue,
     'price' => $node->getElementsByTagName('price_eur')->item(0)->nodeValue
   );
   array_push($arrFeeds, $itemRSS);

  }


  $counter = 0;
  foreach( $arrFeeds as $RssItem){
  $sku = $RssItem["sku"];
  $stock = $RssItem["stock"];
  $price = $RssItem["price"];
  $sql = "INSERT INTO table(sku,stock,price) VALUES ('" . $sku . "','" . 
  $stock . "','" . $price . "')";
  $counter  ;
  $result = mysqli_query($conn, $sql);

  }

  echo $counter;

  mysqli_close($conn);


   ?>

CodePudding user response：

This is a the kind of tasks where you really want to use some sort of SQL query builder. I will post (the untested) code with the Laravel's one. You might choose any other, or build the query manually. So, what we do - is accumulate the data for every product in a temporary array and push it to the DB every time we meet the next product. Note: your XML might have several sizes for one product. The approach below will utilize only the last one, since you don't have tables for product sizes.

<?php
use Illuminate\Support\Facades\DB;

$xml = new XMLReader;
$xml->open('XML_URL');

// We will collect single product data here
$product_data = [];
while ($xml->read()) {    

    if ($xml->nodeType === XMLReader::ELEMENT && $xml->name == 'product'){

        // This is the key block
        // For a non-empty product data we insert a new row
        if( !empty( $product_data ) ){

            // Using Laravel query builder.
            // Any other will do the trick, i just dont want to mess
            // with building SQL manually
            DB::table('table')->insert($product_data);

            // Reinit product data with empty array
            $product_data = [];
        }

        $product_data['un_id'] = $xml->getAttribute('id');
        
    }

    if ($xml->nodeType === XMLReader::ELEMENT && $xml->name == 'price')
        $product_data['price'] = $xml->getAttribute('net');

    /*
    ... and so on ...

    I'd recommend making an external map of some sort like:
    [
        'size' => [
            'attribute' => 'iaiext:code_external',
            'target_db_field' => 'ean'
        ]
    ]

    And fill in the product_data using this map

    */    

}

// At this point we might have a non empty product data that was not inserted yet
// coz we will never meet another <product> tag.. just insert what we have:
if( !empty( $product_data ) )
    DB::table('table')->insert($product_data);

CodePudding user response：

To answer my question I finally imported the XML structure via xpath

  <?xml version="1.0" encoding="UTF-8"?>
  <products currency="EUR">
  <product id="457654754" vat="13.0">
  <price gross="0.09" net="0.07"/>
  <sizes>
    <size id="0"  name="gfdgfdgfdg" panel_name="dfgfdgfdgfd" 
     code_producer="fgdfgfdg"  
     iaiext:code_external="457547547547"  code="354643643643"  weight="4"  
  iaiext:weight_net="10" >
    <stock id="1" quantity="333"/>
    </size>
 </sizes>
 </product>
 </products>

     <?php
     $conn = mysqli_connect("xxx","xxx","xxx","xxx")
      $context  = stream_context_create(array('http' => array('header' => 
  'Accept: 
    application/xml')));
    $xml_file = 'FULL_URL_OF_XML_FILE';
    $xmlsss = file_get_contents($xml_file, false, $context);
    $xml = simplexml_load_string($xmlsss);
    $count = count($xml->xpath('/products/product'));
   for($i = 1; $i <= $count; $i  ){
    $id = $xml->xpath('/products/product['.$i.']/@id')[0];
        $stock = $xml->xpath('/products/product['.$i.']/sizes/size/stock/@quantity')[0];
    $price = $xml->xpath('/products/product['.$i.']/price/@net')[0];
    $ean = $xml->xpath('/products/product['.$i.']/sizes/size/@iaiext:code_external')[0];

    $sql = "INSERT INTO table(id,price,quantity,ean) VALUES ('" . $id . "','" 
 . $price . "','" . $stock . "','" . $ean . "')";
  mysqli_query($conn, $sql);
 }
 mysqli_close($conn);
 ?>