importing Xml data to Mysql database - only writes the LAST record

-1

In this code all the XML records are read, but in the Mysql database only the last XML record block is written. I know that reading each record over-writes the previous one but the strange thing is that I gave an ECHO and checked that the foreach loop brings all xml records but at the time of writing to the bank it only writes the last record. Where is the problem in this logic?

Hello Mauro Alexandre, thank you for replying: complete code here:

 <?php
$servername = "xxxxx";
$username = "xxxx";
$password = "xxxxx";
$dbname = "xxxxxxx";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$affectedRow = 0;
$xml = simplexml_load_file("americana.xml") or die("Error: Cannot create object");

foreach ($xml->return as $registro) :
echo $registro->campaignID."<br/>";
echo $registro->categoryID."<br/>";

$sql = "INSERT INTO rakupom_cupom_temp (id_cupom, campaignID, categoryID, categoryName, linkID, linkName, mid, nid, imgURL, cupom_preco, cupom_codigo, cupom_desconto,clickURL, startDate, endDate,  landURL, showURL,textDisplay, clickTNNT,  cupom_bit, cupom_status)
VALUES (NULL, '".$registro->campaignID."', '".$registro->categoryID."', '".$registro->categoryName."', '".$registro->linkID."', '".$registro->linkName."', '".$registro->mid."', '".$registro->nid."', '$imgURL', '$cupom_preco', '$cupom_codigo', '$cupom_desconto', '".$registro->clickURL."', '".$registro->startDate."', '".$registro->endDate."', '".$registro->landURL."', '".$registro->showURL."', '".$registro->textDisplay."', '$clickTNNT', '$cupom_bit', '$cupom_status')";

endforeach; // FIM DO FOREACH

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?> 

XML FILE

 <getTextLinksResponse xmlns:ns1="http://endpoint.linkservice.linkshare.com/">
  <return>
    <campaignID>0</campaignID>
    <categoryID>200610822</categoryID>
    <categoryName>CUPOM</categoryName>
    <linkID>5081</linkID>
    <linkName>Cupom de 10% OFF em seleção de malas e acessórios (Código: MOCHILA10) - Válido para produtos vendidos e entregues por My Store.</linkName>
    <mid>42758</mid>
    <nid>8</nid>
    <clickURL>http://click.linksynergy.com/fs-bin/click?id=iE1EFPFJmpE&amp;offerid=575927.5081&amp;type=3</clickURL>
    <endDate>Aug 11, 2018</endDate>
    <landURL>https://www.americanas.com.br/categoria/malas-e-acessorios/f/loja-My%20Store</landURL>
    <showURL>http://ad.linksynergy.com/fs-bin/show?id=iE1EFPFJmpE&amp;bids=575927.5081&amp;type=3</showURL>
    <startDate>Jul 23, 2018</startDate>
    <textDisplay>Cupom de 10% OFF em seleção de malas e acessórios (Código: MOCHILA10) - Válido para produtos vendidos e entregues por My Store.</textDisplay>
  </return>
  <return>
    <campaignID>0</campaignID>
    <categoryID>200610822</categoryID>
    <categoryName>CUPOM</categoryName>
    <linkID>5064</linkID>
    <linkName>Cupom de 10% de desconto em Drones (Código: DRONE10) - Válido para produtos vendidos por My Store.</linkName>
    <mid>42758</mid>
    <nid>8</nid>
    <clickURL>http://click.linksynergy.com/fs-bin/click?id=iE1EFPFJmpE&amp;offerid=575927.5064&amp;type=3</clickURL>
    <endDate>Aug 12, 2018</endDate>
    <landURL>https://www.americanas.com.br/categoria/cameras-e-filmadoras/f/tag-tag-cinefoto-mystoredji</landURL>
    <showURL>http://ad.linksynergy.com/fs-bin/show?id=iE1EFPFJmpE&amp;bids=575927.5064&amp;type=3</showURL>
    <startDate>Jul 18, 2018</startDate>
    <textDisplay>Cupom de 10% de desconto em Drones (Código: DRONE10) - Válido para produtos vendidos por My Store.</textDisplay>
  </return>
</getTextLinksResponse>

PHP

    
asked by anonymous 08.08.2018 / 17:19

1 answer

0

The problem lies in the logic of the application.

Your variable $sql is overwriting the previous value within the loop repetition; the value stored in the variable will be the last one.

To solve this problem, just put the following code snippet inside the foreach , after the query.

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

At each iteration it will store its values.

    
08.08.2018 / 17:29