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

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>

Solution

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

?>