How to insert multiple XML prices into SQL table with PHP

Question

I've been trying to find the answer to the following question, but can't seem to find the solution. I've been able to insert one product price in my SQL table, but all the possibilities I try for multiple products aren't working. This is my working code for one product.

<html>
<head>
<meta charset="UTF-8">
<title>Test</title>
</head>

<body>

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "dbname";

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

$xml=simplexml_load_file("URL") or die("Error: Cannot create object");

foreach ($xml->product as $row) {
    $price = $row -> price;

$sql = "INSERT INTO `tablename` (`price`) 
        VALUES ('$price')"; 

}

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

$conn->close();
?>


</body>
</html>

What should I add or change to make the query go through the whole XML file and look for all the prices of the products?

Thanks in advance!

Hi! please take the time to learn about prepared statements. As it stands, at the moment your code is not secure. Thanks! — STT LCU, Jan 13 '17 at 15:51
I've been reading some information on this and tried to rebuild my code: [link](http://stackoverflow.com/questions/41648694/execute-statement-for-all-xml-rows). Can you recommend me a website where I can read about this more detailled? Or is my renewed code already better? Code: [link](http://stackoverflow.com/questions/41648694/execute-statement-for-all-xml-rows) — Hn3M, Jan 16 '17 at 08:52
yes the new code is much better. Look at this SO question for a starting point https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 — STT LCU, Jan 16 '17 at 10:22

dikey · Answer 1 · 2017-01-13T16:35:19.920

0

You can do it with mysqli::multi_query. Only put the semicolon at end of sql and execute with multi_query:

$sql = "";


foreach ($xml->product as $row) {

    $price = $row -> price;

    $sql .= "INSERT INTO `product` (`price`)
        VALUES ('$price');";

}


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

edited Jan 13 '17 at 16:35

answered Jan 13 '17 at 15:52

dikey

21
4

Still only inserts one price – Hn3M Jan 13 '17 at 15:56
Sorry, I forgot initiate the sql and concatenate the sql variable sql. I think now this will work. – dikey Jan 13 '17 at 16:39
It keeps inserting only one price. – Hn3M Jan 13 '17 at 16:49
Hn3M is your xml have one product tag? I test and simulate the code with a xml and the mysql database and i don´t figure out what is wrong. Try to echo the product variable with count() or sizeof(). – dikey Jan 13 '17 at 17:12
No it has several product tags. The XML looks something like this: ` f150d822615whjb 5.95 5.95 ... ... ... ... ... ... ... ... ` – Hn3M Jan 14 '17 at 08:42
You cannot access the product element directly in simplexml if it is not child of root. You must acess the products as `$xml->programs->program->products->children()` and the price as `$price = $row->product_info->price;`. – dikey Jan 16 '17 at 17:36

How to insert multiple XML prices into SQL table with PHP

1 Answers1