importing Xml data into Mysql database - Only save the LAST record

Question

In this code all XML records are read, but in the Mysql database only the last XML block is saved. I know that reading each record over-writes the previous one but the strange thing is that I gave an ECHO and I proved that the foreach loop brings all the xml records but when recording in the database only records the last record. Where’s the problem in that logic?

Hello Mauro Alexandre, thank you for answering: 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

Answer 1

The problem lies in the logic of the application.

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

To solve this problem, simply place the following code snippet within the foreach, after the query.

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

With each iteration it will store its values.