I have this XML feed below I am trying to import into MySQL for all the products. For example, inside the table XML_FEED I want something like
shop - product_id - product_name - product_link - .......
mywebstore - 322233 - MadBiker 600 - .........
mywebstore - 324633 - Samsung S4 - .........
The code until now it works only if the XML begins from <products> and not from <mywebstore>
How to change my code to do this ?
$xml = simplexml_load_file("test.xml");
foreach($xml->product as $product)
{
$columns = array();
$data = array();
foreach($product->children() as $child)
{
echo $child->getName() . ": " . $child . "<br />";
$columns[] = $child->getName();
$data[] = mysql_real_escape_string((string)$child);
}
$col = '`'. implode('`,`',$columns) .'`';
$val = "'". implode("','",$data)."'";
$query = "INSERT INTO XML_FEED ($col) VALUES ($val)";
echo $query;
mysql_query($query);
}
Here is the XML:
<?xml version="1.0" encoding="UTF-8"?>
<mywebstore>
<created_at>2010-04-08 12:32</created_at>
<products>
<product>
<id>322233</id>
<name><![CDATA[MadBiker 600]]></name>
<link><![CDATA[http://www.mywebstore.co.uk/product/322233]]></link>
<image><![CDATA[http://www.mywebstore.co.uk/product/322233.jpg]]></image>
<category><![CDATA[Outdor > Extreme Sports]]></category>
<price_with_vat>322.33</price_with_vat>
</product>
...
...
...
</products>
</mywebstore>
This should work for you:
<?php
//Xml stuff
$xml = simplexml_load_file("file.xml");
//Database stuff
$hostname = "localhost";
$username = "root";
$password = "";
try {
//DB Connection
$dbh = new PDO("mysql:host=$hostname;dbname=dbname", $username, $password);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected to Database<br/>";
foreach($xml->products->product as $data) {
$sql = "INSERT INTO XML_FEED (shop, product_id, product_name, product_link, product_image, product_category, product_price_with_vat)
VALUES (:SHOP, :ID, :NAME, :LINK, :IMAGE, :CATEGORY, :PRICE)";
$stmt = $dbh->prepare($sql);
$params = array(
"SHOP" => $xml->getName(),
"ID" => $data->id ,
"NAME" => $data->name,
"LINK" => $data->link,
"IMAGE" => $data->image,
"CATEGORY" => $data->category,
"PRICE" => $data->price_with_vat
);
$stmt->execute($params);
}
//Close Connection
$dbh = null;
} catch(PDOException $e) {
echo $e->getMessage();
}
?>
Site Note:
Add error reporting to the top of your file(s) which will help during production testing.
<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
?>
Also if you want to show/see the data in html you can use this:
<?php
//Xml stuff
$xml = simplexml_load_file("file.xml");
echo "<table border='1'>";
echo "<tr>
<td>Shop</td>
<td>Product ID</td>
<td>Product Name</td>
<td>Product Link</td>
<td>Product Image</td>
<td>Product Category</td>
<td>Product Price with vat</td>
</tr>";
foreach($xml->products->product as $data) {
echo "<tr>
<td>" . $xml->getName() . "</td>
<td>" . $data->id . "</td>
<td>" . $data->name . "</td>
<td>" . $data->link . "</td>
<td>" . $data->image . "</td>
<td>" . $data->category . "</td>
<td>" . $data->price_with_vat. "</td>
</tr>";
}
echo "</table>";
?>
You are not able to get data because of missing or invalid parent node.
While working with XML needs to handle parent and child(children) node relationship carefully. Unfortunately you have missed the exact same thing.
In your code you have handled the second condition. You have to handle the both conditions or you can use the first condition in your code.
Example:
<?php
$file = 'test.xml';
$xml = simplexml_load_file($file, null, LIBXML_NOCDATA);
if($xml === false){
echo "Failed to load '$file'.
";
}else{
$productsArr = Array();
if(isset($xml->products)){
$productsArr = $xml->products;
}else if(isset($xml->product)){
$productsArr = $xml;
}
if(sizeof($productsArr) > 0){
foreach($productsArr->product as $productArr){
$productArr = (array) $productArr;
$id = null;
if(isset($productArr['@attributes'])){
$id = $productArr['@attributes']['id'];
unset($productArr['@attributes']);
}
if(!isset($productArr['id']) && !empty($id)){
$productArr['id'] = $id;
}
array_walk_recursive($productArr, function (&$value) {
$value = htmlentities($value,ENT_QUOTES,'UTF-8');
$value = mysql_real_escape_string($value);
});
$col = '`'. implode('`,`',array_keys($productArr)) .'`';
$val = "'". implode("','",array_values($productArr))."'";
$query = "INSERT INTO projectx ($col) VALUES ($val)";
echo "$query
";
mysql_query($query);
}
}else{
echo "Invalid XML Format.Missing parent node '<mywebstore> or <products>'.
";
}
}
XML:
<mywebstore> as parent node XML with attribute ID:`
<?xml version="1.0" encoding="UTF-8"?>
<mywebstore>
<created_at>2010-04-08 12:32</created_at>
<products>
<product id="322233">
<name><![CDATA[MadBiker' 600]]></name>
<link><![CDATA[http://www.mywebstore.co.uk/product/322233]]></link>
<image><![CDATA[http://www.mywebstore.co.uk/product/322233.jpg]]></image>
<category><![CDATA[Outdor > Extreme Sports]]></category>
<price_with_vat>322.33</price_with_vat>
</product>
<product>
<id>322234</id>
<name><![CDATA[MadBiker 700]]></name>
<link><![CDATA[http://www.mywebstore.co.uk/product/322233]]></link>
<image><![CDATA[http://www.mywebstore.co.uk/product/322233.jpg]]></image>
<category><![CDATA[Outdor > Extreme Sports]]></category>
<price_with_vat>344.00</price_with_vat>
</product>
</products>
</mywebstore>
<mywebstore> as parent node XML without attribute ID (Same as Question XML):`
<?xml version="1.0" encoding="UTF-8"?>
<mywebstore>
<created_at>2010-04-08 12:32</created_at>
<products>
<product>
<id>322233</id>
<name><![CDATA[MadBiker 600]]></name>
<link><![CDATA[http://www.mywebstore.co.uk/product/322233]]></link>
<image><![CDATA[http://www.mywebstore.co.uk/product/322233.jpg]]></image>
<category><![CDATA[Outdor > Extreme Sports]]></category>
<price_with_vat>322.33</price_with_vat>
</product>
</products>
</mywebstore>
`
<products> as parent node XML with attribute ID:`
<?xml version="1.0" encoding="UTF-8"?>
<products>
<product id="322233">
<name><![CDATA[MadBiker' 600]]></name>
<link><![CDATA[http://www.mywebstore.co.uk/product/322233]]></link>
<image><![CDATA[http://www.mywebstore.co.uk/product/322233.jpg]]></image>
<category><![CDATA[Outdor > Extreme Sports]]></category>
<price_with_vat>322.33</price_with_vat>
</product>
<product>
<id>322234</id>
<name><![CDATA[MadBiker 700]]></name>
<link><![CDATA[http://www.mywebstore.co.uk/product/322233]]></link>
<image><![CDATA[http://www.mywebstore.co.uk/product/322233.jpg]]></image>
<category><![CDATA[Outdor > Extreme Sports]]></category>
<price_with_vat>344.00</price_with_vat>
</product>
</products>
`
<products> as parent node XML without attribute(ID):`
<?xml version="1.0" encoding="UTF-8"?>
<products>
<product>
<id>322233</id>
<name><![CDATA[MadBiker 600]]></name>
<link><![CDATA[http://www.mywebstore.co.uk/product/322233]]></link>
<image><![CDATA[http://www.mywebstore.co.uk/product/322233.jpg]]></image>
<category><![CDATA[Outdor > Extreme Sports]]></category>
<price_with_vat>322.33</price_with_vat>
</product>
</products>
Conclusion: In valid handling of parents & child node relationship.
Simply change this:
foreach($xml->product as $product)
{
with this:
foreach($xml->products[0] as $product)
{
Firstly, simplexml_load_file() returns a pointer to the root element of the XML feed, i.e. the very first XML tag in the input file. In other words, when you write:
$xml = simplexml_load_file("test.xml");
test.xml contains "<mywebstore> <products> <product> (...)" then $xml points at <mywebstore>test.xml contains "<products> <product> (...)" then $xml points at <products>Secondly, $xml->[tagName] looks for direct children only, not recursively. Therefore $xml->product finds something only if a <product> tag exists as a child of the root element.
In general, it is better for the code to match the input structure exactly. Adapt your outer loop to the expected input:
foreach($xml->product as $product) {
...
}
or
foreach($xml->products->product as $product) {
...
}
If for some reason the <products> tag can be at various locations in the input XML feed, perhaps proceed in two steps:
// try to locate the <product> nodes
if (count($xml->product) !== 0) {
$productNodes = $xml->product;
} else if (count($xml->products->product) !== 0) {
$productNodes = $xml->products->product;
} else {
throw new Exception('No <product> node found');
}
// do the job
foreach($productNodes as $product){
...
}
Or for extreme flexibility, use an xpath. The below will return a list of all <product> nodes anywhere in the XML feed.
$productNodes = $xml->xpath('//product');
foreach($productNodes as $product){
...
}
I trust the MySQL part is not an issue, so I will just stick to the usual incantation:
Please, don't use
mysql_*functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.
Use This code:
<?php
$xml = simplexml_load_file('test.xml');
foreach($xml->products->product as $product)
{
$columns = array();
$data = array();
foreach($product->children() as $child)
{
echo $child->getName() . ": " . $child . "<br />";
$columns[] = $child->getName();
$data[] = mysql_real_escape_string((string)$child);
}
$col = '`'. implode('`,`',$columns) .'`';
$val = "'". implode("','",$data)."'";
$query = "INSERT INTO XML_FEED ($col) VALUES ($val)";
echo $query;
mysql_query($query);
}
?>
In order to get the different products you should use xpath
Code:
$xml = simplexml_load_file("xml.xml");
// will search for array of products no matter what it is nested inside of
$products = $xml->xpath('//product');
foreach($products as $product)
{
$columns = array();
$data = array();
foreach($product->children() as $child)
{
echo $child->getName() . ": " . $child . "<br />";
$columns[] = $child->getName();
$data[] = mysql_real_escape_string((string)$child);
}
$col = '`'. implode('`,`',$columns) .'`';
$val = "'". implode("','",$data)."'";
$query = "INSERT INTO XML_FEED ($col) VALUES ($val)";
echo $query;
mysql_query($query);
}
Explanation:
Xpath for simplexml simply returns an array of the simple xml objects or here the product xml elements.
Since we want to return an array of all the products, we search "foreach" occurrence of the product using xpath.
Inside of the xpath string, A double slash (//) signals that all elements in the XML document that match the search criteria are returned, regardless of location/level within the document.