Importing XML into MySQL table?

Question

I'm a little new to MySQL -- so, help is very much appreciated.

Does anyone know how I could import every item from an XML feed into a MySQL table and then have the items automatically delete after 24 hours, and if I imported another XML feed, it wouldn't replace the existing items but add to it (and wouldn't add duplicates)?

I would like to do all of this with PHP.

Thanks if you can help or point me the correct direction.

Answer 1

>> Does anyone know how I could import every item from an XML feed into a MySQL table



It depends on the feed, but generally speaking, you can parse XML in PHP one of two ways: Via the XMLReader class or the built-in XML functions.



http://us2.php.net/manual/en/ref.xmlreader.php



http://us2.php.net/manual/en/ref.xml.php



>> and then have the items automatically delete after 24 hours



You can do this with a cron job. Just record in your database the import time and have the cron job delete anything more than 24 hours old.



http://www.sitepoint.com/article/introducing-cron



>> and if I imported another XML feed, it wouldn't replace the existing items but add to it (and wouldn't add duplicates)?



How you define a "duplicate" depends on a number of factors. It's easiest to have a unique key in the XML document for each record, but if that's not the case, you'd probably want to compare the contents of some tag -- creation date, title, body text, etc -- vs. all entries in the table. If there's an exact match, that's a dupe and you don't want it.

Answer 2

You need to use php to read the data from the page input, insert it into the database using key fields as a way of preventing duplicates. If you turn off the mysql_error() (just don't set up an error trap), duplicate keys cause a silent failure. Include a time() marker as a field in one of the tables, Everytime someone opens a particular page it can execute a delete of any thing with a 24 hour old timestamp.

Answer 3

Either use the dump command from a terminal, or install phpMyAdmin and export the table with a few clicks. If you want to do it in PHP yourself, read the table and write to a text file for each record. (Create the file first, and close it when you're done.) (This is definitely a programming question, but the best solution may not be writing a program.)