Page 1 of 1

Data from XML to MYSQL using PHP

Posted: Tue May 31, 2005 11:16 pm
by xfusion
Hey, I've not seen this on the board but I need help to create a function that takes data from an XML file and inserts it into a mysql database using PHP.

The data looks like this (automatically created by MP3 Collector):

Code: Select all

<?xml version=&quote;1.0&quote;?>
<mp3info creationdate=&quote;6/2/2005 12:11:59 AM&quote;><mp3metadata><field name=&quote;Index&quote; label=&quote;Index&quote;/><field name=&quote;Track Index&quote; label=&quote;Track Index&quote;/><field name=&quote;Disk Index&quote; label=&quote;Disk Index&quote;/><field name=&quote;Track Artist&quote; label=&quote;Track Artist&quote;/><field name=&quote;Album Artist&quote; label=&quote;Album Artist&quote;/><field name=&quote;Title&quote; label=&quote;Title&quote;/><field name=&quote;Title Sort&quote; label=&quote;Title Sort&quote;/><field name=&quote;Title The&quote; label=&quote;Title The&quote;/><field name=&quote;Year&quote; label=&quote;Year&quote;/><field name=&quote;Album&quote; label=&quote;Album&quote;/><field name=&quote;Album Title&quote; label=&quote;Album Title&quote;/><field name=&quote;Sub Title&quote; label=&quote;Sub Title&quote;/><field name=&quote;Genre&quote; label=&quote;Genre&quote;/><field name=&quote;Copyright&quote; label=&quote;Copyright&quote;/><field name=&quote;Label&quote; label=&quote;Label&quote;/><field name=&quote;Notes&quote; label=&quote;Notes&quote;/><field name=&quote;Track Artist&quote; label=&quote;Track Artist&quote;/><field name=&quote;Genre&quote; label=&quote;Genre&quote;/><field name=&quote;Label&quote; label=&quote;Label&quote;/><field name=&quote;Year&quote; label=&quote;Year&quote;/><field name=&quote;Album&quote; label=&quote;Album&quote;/><field name=&quote;Album Artist&quote; label=&quote;Album Artist&quote;/><field name=&quote;Playlist Index&quote; label=&quote;Playlist Index&quote;/><field name=&quote;Playlist Title&quote; label=&quote;Playlist Title&quote;/><field name=&quote;Original Artist&quote; label=&quote;Original Artist&quote;/><field name=&quote;Composer&quote; label=&quote;Composer&quote;/><field name=&quote;Lyricist&quote; label=&quote;Lyricist&quote;/><field name=&quote;Orchestra&quote; label=&quote;Orchestra&quote;/><field name=&quote;Conductor&quote; label=&quote;Conductor&quote;/><field name=&quote;Composer&quote; label=&quote;Composer&quote;/><field name=&quote;Lyricist&quote; label=&quote;Lyricist&quote;/><field name=&quote;Orchestra&quote; label=&quote;Orchestra&quote;/><field name=&quote;Conductor&quote; label=&quote;Conductor&quote;/><field name=&quote;Original Artist&quote; label=&quote;Original Artist&quote;/><field name=&quote;Encoded By&quote; label=&quote;Encoded By&quote;/><field name=&quote;BPM&quote; label=&quote;BPM&quote;/><field name=&quote;Lyrics&quote; label=&quote;Lyrics&quote;/><field name=&quote;Tempo&quote; label=&quote;Tempo&quote;/><field name=&quote;Mood&quote; label=&quote;Mood&quote;/><field name=&quote;Situation&quote; label=&quote;Situation&quote;/><field name=&quote;Preference&quote; label=&quote;Preference&quote;/><field name=&quote;Biography&quote; label=&quote;Biography&quote;/><field name=&quote;Tempo&quote; label=&quote;Tempo&quote;/><field name=&quote;Mood&quote; label=&quote;Mood&quote;/><field name=&quote;Situation&quote; label=&quote;Situation&quote;/><field name=&quote;Preference&quote; label=&quote;Preference&quote;/><field name=&quote;Audio URL&quote; label=&quote;Audio URL&quote;/><field name=&quote;Artist URL&quote; label=&quote;Artist URL&quote;/><field name=&quote;General URL&quote; label=&quote;General URL&quote;/><field name=&quote;Location&quote; label=&quote;Location&quote;/><field name=&quote;Filename&quote; label=&quote;Filename&quote;/><field name=&quote;Filepath&quote; label=&quote;Filepath&quote;/><field name=&quote;Size&quote; label=&quote;Size&quote;/><field name=&quote;Length&quote; label=&quote;Length&quote;/><field name=&quote;Channels&quote; label=&quote;Channels&quote;/><field name=&quote;Bitrate&quote; label=&quote;Bitrate&quote;/><field name=&quote;SampleRate&quote; label=&quote;SampleRate&quote;/><field name=&quote;VBR&quote; label=&quote;VBR&quote;/><field name=&quote;Filename Format&quote; label=&quote;Filename Format&quote;/><field name=&quote;Format&quote; label=&quote;Format&quote;/><field name=&quote;Tags&quote; label=&quote;Tags&quote;/><field name=&quote;New Filename&quote; label=&quote;New Filename&quote;/><field name=&quote;ID&quote; label=&quote;ID&quote;/></mp3metadata><mp3list><mp3><filepath>\Trinity_-_Wonderful-Promo-CDM-2005-TN\03-trinity-wonderful_original_mix-tn.mp3</filepath><filename>03-trinity-wonderful_original_mix-tn.mp3</filename><artist><displayname>Trinity</displayname></artist><title>Wonderful Original Mix</title><album><displayname>Wonderful</displayname></album><year><displayname>2005</displayname></year><genre><displayname>Dance</displayname></genre><notes>- TRANCE NATiON -</notes><size>4,947 KB</size><length>04:26</length><channels>Joint Stereo</channels><bitrate>152</bitrate><samplerate>44100</samplerate><vbr>Yes</vbr><tags>ID3v1</tags></mp3><mp3><filepath>Trinity_-_Wonderful-Promo-CDM-2005-TN\04-trinity-wonderful_alternative_mix-tn.mp3</filepath><filename>04-trinity-wonderful_alternative_mix-tn.mp3</filename><artist><displayname>Trinity</displayname></artist><title>Wonderful Alternative Mix</title><album><displayname>Wonderful</displayname></album><year><displayname>2005</displayname></year><genre><displayname>Dance</displayname></genre><notes>- TRANCE NATiON -</notes><size>5,688 KB</size><length>05:23</length><channels>Joint Stereo</channels><bitrate>144</bitrate><samplerate>44100</samplerate><vbr>Yes</vbr><tags>ID3v1</tags></mp3><mp3><filepath>\1-trinity-wonderful_radio_edit-tn.mp3</filepath><filename>01-trinity-wonderful_radio_edit-tn.mp3</filename><artist><displayname>Trinity</displayname></artist><title>Wonderful Radio Edit</title><album><displayname>Wonderful</displayname></album><year><displayname>2005</displayname></year><genre><displayname>Dance</displayname></genre><notes>- TRANCE NATiON -</notes><size>4,017 KB</size><length>03:37</length><channels>Joint Stereo</channels><bitrate>151</bitrate><samplerate>44100</samplerate><vbr>Yes</vbr><tags>ID3v1</tags></mp3><mp3><filepath>\02-trinity-wonderful_extended_mix-tn.mp3</filepath><filename>02-trinity-wonderful_extended_mix-tn.mp3</filename><artist><displayname>Trinity</displayname></artist><title>Wonderful Extended Mix</title><album><displayname>Wonderful</displayname></album><year><displayname>2005</displayname></year><genre><displayname>Dance</displayname></genre><notes>- TRANCE NATiON -</notes><size>5,356 KB</size><length>04:50</length><channels>Joint Stereo</channels><bitrate>151</bitrate><samplerate>44100</samplerate><vbr>Yes</vbr><tags>ID3v1</tags></mp3></mp3list></mp3info>
I hope someone can help me, I can't seem to figure this one out.

- Kevin

Posted: Tue May 31, 2005 11:29 pm
by Burrito
use

Code: Select all

or

Code: Select all

tags when posting code...it makes it much easier to read.

take a look [url=http://us3.php.net/xml]here[/url]

Posted: Tue May 31, 2005 11:51 pm
by xfusion
Sorry about the mess of an XML file, but when I tired making it a code it would make itself 2 lines. This was a little more organized.

Has anyone seen a working example of an XML to MySql script, because that would help me more than anything.

JCART | I put it into

Code: Select all

tags anyway, so the page doesn't scew.[/color]

Posted: Wed Jun 01, 2005 1:14 am
by xfusion
alright I have my script, and it says that it runs sucessfully, but there isnt anything added into the database.

Code: Select all

function insertintodb($DBFILENAME)
{
      $xml_file = fopen($DBFILENAME, &quote;r&quote;);

      $db = connect2db();
      $query1 = &quote;TRUNCATE TABLE mp3&quote;;
      mysql_query($query1);

      echo &quote;<B><H1>Inserting mp3s</H1></B>&quote;;

      $this->xml->read_file_handle($xml_file);

      $numRows = $this->xml->roottag->num_subtags();

      for ($i = 0; $i < $numRows; $i++) {
           $arrFields = null;
       $arrValues = null;

       $row = $this->xml->roottag->tags&#1111;$i];
           $numFields = $row->num_subtags();

           for ($ii = 0; $ii < $numFields; $ii++) {
             $field = $row->tags&#1111;$ii];
              $arrFields&#1111;] = $field->name;
              $arrValues&#1111;] = &quote;\&quote;&quote;.$field->cdata.&quote;\&quote;&quote;;
           }

           $fields = join($arrFields, &quote;, &quote;);
           $values = join($arrValues, &quote;, &quote;);

           $this->recordSet->exec(&quote;Insert Into $dbname ($fields) Values ($values)&quote;);
      }
         fclose ($xml_file);
         mysql_close($db);
   }
Does anybody know where I've gone wrong?