How to edit my code to save to mySQL from the beginning of XML?

Question

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>

I don't think that you can compensate low question quality by putting bounty on it. — MightyPork, Dec 29 '14 at 16:11
Check this: http://stackoverflow.com/questions/486757/how-to-generate-xml-file-dynamically-using-php — vaso123, Dec 29 '14 at 16:12
You do...but I think you might stop getting the down vote barrage if you show some examples of what you've tried. — Joe Swindell, Dec 29 '14 at 16:12
This is stackoverflow, you're openly asking for code in a site where you should post **your code first**... What did you try yet? this question should not have an opened bounty, because it is most likely a sort of a duplicate. — briosheje, Dec 29 '14 at 16:12
If one could only mark this as a douplicate. It's quite simliar to [this](http://stackoverflow.com/questions/2161722/parsing-xml-data-using-php-to-put-into-mysql-database), right? — Rangad, Dec 29 '14 at 16:15
@Xalloumokkelos So you want to read in the xml file and then store the products in your db like in your example(first code box)? — Rizier123, Dec 29 '14 at 17:18
@Xalloumokkelos Then i hope my answer helps and solves your problem :D — Rizier123, Dec 29 '14 at 18:20
@Xalloumokkelos This has been up for a while. Has any of the answers given below, solved the issue? The grace period ends soon. — Funk Forty Niner, Jan 06 '15 at 15:37

harry · Answer 1 · 2015-01-06T06:28:01.990

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);
}
?>

Rizier123 · Accepted Answer · 2014-12-29T17:59:42.200

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>";

?>

Why downvote? Please explain, so i can improve/change my answer. (and that i can learn it for the future) — Rizier123, Jan 05 '15 at 16:03

Rajesh Ujade · Answer 3 · 2015-01-01T13:48:29.730

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.

When you use mywebstore node as a parent then its intermediate child node is products and products child node is product.
But when you use products node as a parent then its child node is product.

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'.\n";
}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 \n";
                    mysql_query($query);
            }
    }else{
            echo "Invalid XML Format.Missing parent node '<mywebstore> or <products>'. \n";
    }
}

XML:

Format with <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>

Format with <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>

`

Format with <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>

`

Format with <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.

Your answer isn't complete at all! You're missing like half of the question. OP also want to insert the date in his DB, and you don't have a connection and no query execution. Also `mysql_*` extension is deprecated as of PHP 5.5.0, and will be removed in the future. — Rizier123, Jan 01 '15 at 11:56
@Rizier123 The question is regarding to parse xml file properly. I have provided the solution for the same. I have used the same mysql* methods which questioner used. I believe the questioner will insert data as per his library requirement like he may use `PDO` or `MySQLi`. — Rajesh Ujade, Jan 01 '15 at 12:29
@Rizier123 If you like to test the solution then please comment `$value = mysql_real_escape_string($value);` and check.With both xml which I have provided in answer. — Rajesh Ujade, Jan 01 '15 at 12:32
MAn what he said only is he don't want data from ``. Thats it.According to your comment only. — Rajesh Ujade, Jan 01 '15 at 12:38
He stated in his question `The code until now it works only if the XML was beginning from and not from `. And solution for the same. — Rajesh Ujade, Jan 01 '15 at 12:39
BTW: you don't even use the right xml file! You just created your own version. plus if you use your xml you have 2 different order of query statements which you then would insert into the wrong table! — Rizier123, Jan 01 '15 at 12:39
I think let decide the questioner what he wants. I have used the same XML file from question. Cross verify it. — Rajesh Ujade, Jan 01 '15 at 12:40
Okay, if you used OP's xml then your output is: `INSERT INTO projectx (``) VALUES ('') INSERT INTO projectx (``) VALUES ('') INSERT INTO projectx (``) VALUES ('') INSERT INTO projectx (``) VALUES ('') INSERT INTO projectx (``) VALUES ('') INSERT INTO projectx (``) VALUES ('')` — Rizier123, Jan 01 '15 at 12:46
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/68027/discussion-between-rajesh-ujade-and-rizier123). — Rajesh Ujade, Jan 01 '15 at 13:02

score 0 · Answer 4 · answered Jan 03 '15 at 11:40

0

Simply change this:

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

with this:

foreach($xml->products[0] as $product)
{

answered Jan 03 '15 at 11:40

score 0 · Answer 5 · edited May 23 '17 at 11:57

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");

if test.xml contains "<mywebstore> <products> <product> (...)" then $xml points at <mywebstore>
if 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.

Demodave · Answer 6 · 2015-01-05T16:13:17.857

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.

How to edit my code to save to mySQL from the beginning of XML?

6 Answers6

Linked