Importing XML to MySQL Database: A little PHP script

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.