[SOLVED] How to store a simplexml parsed xml feed to mysql

[dietkinnie]

Hi Guys, I have been up all night but still have not managed to find a solution for this. So your help will be greatly appreciated 

 

I want parse the below xml file using simplexml and add the contents to a mysql database.

 

XML Feed

<?xml version="1.0"?>
  <library>
   <shelf id="fiction">
    <book>
     <title>Of Mice and Men</title>
     <author>John Steinbeck</author>
    </book>
    <book>
     <title>Harry Potter and the Philosopher's Stone</title>
     <author>J.K. Rowling</author>
    </book>
   </shelf>
    <shelf id="SCIFI">
    <book>
     <title>XFILES</title>
     <author>Robert Brown</author>
    </book>
    <book>
     <title>When dogs came to earth</title>
     <author>Albert White</author>
    </book>
   </shelf>
</library>

 

 

I created the following table

 

create table books (book_id int primary key auto_increment, shelf_id varchar(40), title varchar(100), author varchar(100));

 

 

Now the problem stars when I use the below php script and try to add the shelf_id , title and author to the databse (im not sure how to do this )

 

<?php

$con1 = mysql_connect("localhost","user","pass");
if (!$con1)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("library", $con1);

   $library =  simplexml_load_file('http://XXXXXXXXXX/library.xml');



             mysql_query("INSERT INTO books (shelf_id, title, author)
       VALUES (XXXX,XXXX,XXXXX)")
      or die(mysql_error());

        }
     mysql_close($con1);

?>

 

 

Once again i would realy appreciate some assistance here !

 

Thanks in advance!!

 

[Yesideez]

I almost suggested store it in a TEXT field but then realised that wasn't what you were after!

 

What you'll be needing here is to write an XML parser using these...

http://uk2.php.net/xml

[dietkinnie]

For anyone who runs into the same problem.

 

 
<?php
//mysql connection
$con2 = mysql_connect("localhost","username","lpasswd");
if (!$con2)
  {
  die('Could not connect: ' . mysql_error());
  }

$selectdb = mysql_select_db("odds", $con2);
if (!$selectdb)
  {
die('Database not used: ; ' . mysql_error());
  }

//simplexml load xml file
   $library =  simplexml_load_file('http://abcd1234.com/library.xml');

//loop through parsed xmlfeed and print output
      foreach ($library->shelf as $shelf) {
              printf($shelf['id']);
              $current_shelf = $shelf['id'];
                     foreach ($shelf->book as $book) {
                     printf("Title: %s\n", $book->title);
                     printf("Author: %s\n", $book->author);
//insert into databse
                               mysql_query("INSERT INTO books (shelf_id, title,                                                                              author)VALUES (\"$current_shelf\",\"$book->title\", \"$book->author\")")
                               or die(mysql_error());
//show updated records
            printf ("Records inserted: %d\n", mysql_affected_rows());
            }
        }
//close connection
mysql_close($con2);

?>

[Whit3H0rse]

Hello community,

I diged this topic  from forum search and m I  trying  to implement to my case (all night long), I want to import this xml code to mysql - my script runs without errors but there is nothing in database.

So if there is some nice soul to check where I made mistake i would be very thankful.

 

I only need <description> and <traffic_out> information!

 

XML CODE:

 <?xml version="1.0" encoding="UTF-8" ?> 
- <!-- # wanstats 
  --> 
- <cacti>
- <report>
- <settings>
  <title>Wan_links</title> 
  <owner>Administrator (admin)</owner> 
  <template>wan_statistic</template> 
  <start>2010-04-03</start> 
  <end>2010-04-09</end> 
  <last_run>2010-04-10 22:34:05</last_run> 
  </settings>
- <variables>
- <variable>
  <id>3</id> 
  <name>over90percent</name> 
  <description>Your description</description> 
  <value>10</value> 
  <min_value>10</min_value> 
  <max_value>100</max_value> 
  </variable>
  </variables>
- <measurands>
- <measurand>
  <abbreviation>CAPA</abbreviation> 
  <description>kapacitet</description> 
  </measurand>
- <measurand>
  <abbreviation>CRIT</abbreviation> 
  <description>critical - time (%) more than 10% usage</description> 
  </measurand>
  </measurands>
- <data_items>
- <item>
  <description>MIAMI - wan546 - Gi1/0/8</description> 
  <subhead /> 
  <start_day>Monday</start_day> 
  <end_day>Sunday</end_day> 
  <start_time>00:00:00</start_time> 
  <end_time>24:00:00</end_time> 
  <time_zone>GMT</time_zone> 
- <results>
- <traffic_in>
  <krit measurand="KRIT" unit="%">0</krit> 
  </traffic_in>
- <traffic_out>
  <krit measurand="KRIT" unit="%">0</krit> 
  </traffic_out>
- <overall>
  <kapa measurand="KAPA" unit="Bits/s">5.76E+8</kapa> 
  </overall>
  </results>
  </item>
- <item>
  <description>LONDON[2] -  wan134_Gi4/1/0.10</description> 
  <subhead /> 
  <start_day>Monday</start_day> 
  <end_day>Sunday</end_day> 
  <start_time>00:00:00</start_time> 
  <end_time>24:00:00</end_time> 
  <time_zone>GMT</time_zone> 
- <results>
- <traffic_in>
  <krit measurand="KRIT" unit="%">0</krit> 
  </traffic_in>
- <traffic_out>
  <krit measurand="KRIT" unit="%">0</krit> 
  </traffic_out>
- <overall>
  <kapa measurand="KAPA" unit="Bits/s">32000000</kapa> 
  </overall>
  </results>
  </item>
  </data_items>
  </report>
  </cacti>

 

PHP CODE;

 

 
<?php
//mysql connection
$con2 = mysql_connect("localhost","cactiuser","cactiuser");
if (!$con2)
  {
  die('Could not connect: ' . mysql_error());
  }

$selectdb = mysql_select_db("reports", $con2);
if (!$selectdb)
  {
die('Database not used: ; ' . mysql_error());
  }

//simplexml load xml file
   $data_items = simplexml_load_file('/home/hanszimmer/temp/99/wanstats.xml');

//loop through parsed xmlfeed and print output

foreach ($data_items->item as $item) {
              printf("Description: %s\n", $item->description);
                     foreach ($item->results as $results) {
                     printf("Traffic_out: %s\n", $results->traffic_out);
                     
                   

//insert into databse
                               mysql_query("INSERT INTO report_percent (city_id, time_over_treshold) VALUES (\"$item->description\",\"$results->traffic_out\")")
                               or die(mysql_error());
//show updated records
            printf ("Records inserted: %d\n", mysql_affected_rows());
            }
        }
//close connection
mysql_close($con2);

?>