Inserting XML into MySQL database

software development

#1

A client of mine wants me to build him a new personal website which has all of the articles he’s written. I’ve created the database already and I asked his previous host for a dump of his old database. (He’s got like 140 articles and I really don’t want to insert them manually). They sent me an XML file. I’m at a loss though, as I don’t know how to insert that data into his new MySQL database.

Can anyone provide any instructions on how to do this? Keep in mind I don’t know too much about XML. I think I understand the jest of it: Create a loop in PHP which extracts each piece of data from the XML, sets it equal to a variable, then inserts those variables as normal into the database. Problem is, I don’t know how to interact with XML using PHP. Are there any tutorials, programs, or scripts out there that can help me out or do this?

Thanks.


#2

There are several PHP tools for working with XML.

You may want to take a look at PHP DOM:
http://us2.php.net/manual/en/ref.domxml.php

I have never worked with DOM, but I have worked with PHP XML. DOM will turn the XML data into a PHP object, so you can iterate through that, but if that doesn’t work out you can try PHP XML:
http://us2.php.net/xml

With PHP XML, PHP reads through the XML data, and each time it hits a different type of data, it will call a specified function that you made. For example, every time it hits a start tag, it will call your start tag function and tell you the name of the tag and its attributes. When you hit a content tag, it will call your content function and give it the data that is in the content. You then have to keep track, usually with an array, of where in the xml document you are, IE (pretend its html data):
$doc[0] = ‘html’;
$doc[1] = ‘body’;
$doc[2] = ‘p’;
$doc[3] = ‘span’;

is

Where's Waldo??

You would have a variable that says what level you’re at (then find the current tag with $doc[$lvl]), and in the start tag function you would increment that level variable and add the next item to the array, and in the end tag function you would decrement the level variable.