Getting XML file from another domain to Mysql database

Question

I'm trying to get xml information from an url to be imported into mysql trough a php script, but I'm having some trouble and my experience dosen't cover this area. The XML is formed as this example:

 <?xml version="1.0" encoding="utf-8" ?>
<rss version="2.0" xmlns:g="http://base.google.se/ns/1.0">
<channel>
  <title></title>
  <description></description>
  <link></link>
  <item>
    <g:id></g:id>
    <title></title>
    <g:product></g:product>
  </item>
  <item>
    <g:id></g:id>
    <title></title>
    <g:product></g:product>
  </item>
and so on...

with the php script:

<?php

include '../connection-to-db.php';

$str_xml = file_get_contents('http://www.example.com/xmls/xmlfile.xml');
$library = new SimpleXMLElement($str_xml);

$arr = json_decode( json_encode($library) , true);
var_dump ($arr);

echo "Array got " .sizeof($arr['item']) . " items.<br> <br>";
if (sizeof($arr['item']) > 155555500) {

    mysql_query("TRUNCATE TABLE google_stat");

    $count = 0;
    foreach ($arr['item'] as $shelf)
    {
        $gId = mysql_real_escape_string($shelf['g:id']);
        $Title = mysql_real_escape_string($shelf['title']);
        $gProductType = mysql_real_escape_string($shelf['g:product']);

        mysql_query("INSERT INTO google_stat (gid, title, gcategory) 
                    VALUES ('$gID', '$Title', '$gCategory')")
                    or die(mysql_error());

        $count ++;
    }
    echo " Counted: " . $count . "inserts";
} else {
    echo "Non counted, no insert done";
}
?>

Problem is when SimpleXMLElement it seems all items with g: in there names disappears when I look at the output, it dosen't even fint any items. I've even tried with a localfile with same XML tree and can't even make that work. I'm thankful for any help given, since I realize more and more I'm on deep water with this.

UPDATE:

<?php

    include '../connection-to-db.php';

    $str_xml = file_get_contents('http://www.example.com/xmls/xmlfile.xml');
    $library = new SimpleXMLElement($str_xml);

    $arr = json_decode( json_encode($library) , true);


    echo "Array got " .sizeof($library->channel->item) . " items.<br> <br>";
    if (sizeof($library->channel->item) > 100) {

        mysql_query("TRUNCATE TABLE google_stat");

        $count = 0;
        foreach ($library->channel->item as $shelf)
        {
            $gId = (string) $shelf->children('g', TRUE)->id;
            $Title = (string) $shelf->title;
            $gProductType = $shelf->children('g', TRUE)->product;

             echo $gId."<br />";
             echo $Title."<br />";
             echo $gProductType."<br />";

            $count ++;
        }
        echo " Counted: " . $count . "inserts";
    } else {
        echo "Non counted, no insert done";
    }
    ?>

Now I get the number of items in array, but $gId, $Title etc, dosen't echo any values.

Edit2: had to high array check, it works.

Every time you use [the `mysql_`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) database extension in new code **[this happens](https://media.giphy.com/media/kg9t6wEQKV7u8/giphy.gif)** it is deprecated and has been for years and is gone for ever in PHP7. If you are just learning PHP, spend your energies learning the `PDO` or `mysqli` database extensions and prepared statements. [Start here](http://php.net/manual/en/book.pdo.php) — RiggsFolly, Dec 18 '17 at 15:38
Some sensible code indentation would be a good idea. It helps us read the code and more importantly it will help **you debug your code** [Take a quick look at a coding standard](http://www.php-fig.org/psr/psr-2/) for your own benefit. You may be asked to amend this code in a few weeks/months and you will thank me in the end. — RiggsFolly, Dec 18 '17 at 15:38
Your script is wide open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Dec 18 '17 at 15:39

tbedner · Accepted Answer · 2017-12-18T16:35:09.323

0

It has to do with the namespace prefix you want. You can access the g: items like so:

<?php

$str_xml = file_get_contents('test.xml');
$library = new SimpleXMLElement($str_xml);

$count = 0;
foreach ($library->channel->item as $shelf)
{

  $gId = (string) $shelf->title;
  $Title = (string) $shelf->children('g', TRUE)->id;
  $gProductType = (string) $shelf->children('g', TRUE)->product;

      echo $gId."<br />";
      echo $Title."<br />";
      echo $gProductType."<br />";

          $count ++;
}
echo " Counted: " . $count . " inserts";

?>

See https://www.sitepoint.com/simplexml-and-namespaces/ for further reference.

xml.test

<?xml version="1.0" encoding="utf-8" ?>
<rss version="2.0" xmlns:g="http://base.google.se/ns/1.0">
<channel>
 <title>product</title>
 <description>lots of products</description>
 <link>www.example.com</link>
 <item>
   <g:id>ID 1</g:id>
   <title>Title 1</title>
   <g:product>Product 1</g:product>
 </item>
 <item>
   <g:id>ID 2</g:id>
   <title>Title 2</title>
   <g:product>Product 2</g:product>
 </item>
 </channel>
</rss>

Output:

Title 1

ID 1

Product 1

Title 2

ID 2

Product 2

Counted: 2 inserts

edited Dec 18 '17 at 16:35

answered Dec 18 '17 at 16:01

tbedner

323
1
10

I'm so thankful!! But I'm not able to echo out the values for some reason? – Petrus Alli Dec 18 '17 at 17:16
Can you show me what you have done so far and maybe I can help diagnose the issue? – tbedner Dec 18 '17 at 17:20
I used the code you just showed me, and it now counts 6258 items instead of 0, so that works. But trying to echo out title or id or product dosen't show anything at all. So $library->channel->item works as it counts them all. I'll update in a few. – Petrus Alli Dec 18 '17 at 17:31
Just realized my own mistake... the arraycheck I added to dont print to db until all was fine... lol thanks for your help! – Petrus Alli Dec 18 '17 at 17:43
Wonderful! Glad you have it all figured out! – tbedner Dec 18 '17 at 17:46
Do you know how I manage tweak this when some of the values in title contains ---> ' <--- ? Should backtick work?... it didn't work with backtick – Petrus Alli Dec 18 '17 at 17:59
I would imagine the backtick would play havoc with mysql. I might try $Title = str_replace("`","'",$Title); to exchange it for a regular apostrophe. – tbedner Dec 18 '17 at 18:24

Getting XML file from another domain to Mysql database

1 Answers1