Importing XML to MySQL Database: A little PHP script

| Wednesday, June 24, 2009

I backed up my database using myphpadmin by exporting to XML. Thought it would be dandy. Was wrong, because to load back to phpmyadmin, I have to have it in SQL format. Original database was no longer available. Thought I was in some deep shit, even though some people say you can "easily" write a PHP script to get it back. Which is true, kind of. You'd think, that with the number of people making this mistake, there'd be scripts out there available already for this.

Well, here's my little contribution.

Use when your XML file looks like this:

<database>
    <tablename>
        <id>1</id>
        <name>George</name>
        <type>fish</type>
    </tablename>
    <tablename>
        <id>3</id>
        <name>Renee</name>
        <type>apple</type>
    </tablename>
</database>


Here's a start to your script to load your table back in:
$table = simplexml_load_file("backup.xml");

foreach( $table->children() as $entry )
{
  $query1 = "INSERT INTO mytable ( ";
  $query2 = " ) VALUES( ";

  foreach( $entry->children() as $info )
  {
    $query1 .= $info->getName() . ", ";
    $query2 .= "\"" . addslashes($info) . "\", ";
  }
  // kill trailing commas and form into full query
  $query = substr( $query1, 0, -2 ) . substr( $query2, 0, -2 ) . " )";
  mysql_query( $query ) or die( mysql_error() );
}


Notes
  • I used PHP's SimpleXML. You need to be using PHP 5 for this to work.
  • This only imports one table.
  • Probably not the most memory efficient; for very large XML files this will probably time out.
  • I started writing this post and never completed it, but I saw it again today (7/19/09) and figured I might as well post... I've still got a thing or two to learn about prompt documentation.

3 comments:

Visconti said...

Nagisa, you really need to teach me PHP, so I can actually understand what the heck is going on there. <.>

Overmind said...
This comment has been removed by the author.
Overmind said...

Hey, its been a little while since I caught up on your blog (or added to my own for that matter). Anyway, I was curious how long did it take you to learn PHP? I feel like an idiot because I haven't learned PHP but need to, hows that for procrastination?

By the way, what happened to nuggit.nu?