Parsing XML and db insertion

Hi

I have a mysql table with the following: id, name, model, qty

What I'm looking to do is parse the following XML and depending on the XML, either insert or update rows.

<?xml version="1.0" ?>
<data>
    <Item>
        <Code>901AF001</Code>
        <ShortDesc>SPECIAL LARYNGOFOAM</ShortDesc>
        <Quantity>0</Quantity>
    </Item>
    <Item>
        <Code>901AF002</Code>
        <ShortDesc>SPECIAL BUCHANAN</ShortDesc>
        <Quantity>0</Quantity>
    </Item>
</data>

Code (markup):

Now, if there is a row in the XML with the <Code> of '901AF001' and this record exists in the DB, then the script needs to update the qty field in the DB with the value of <Quantity>

If there is no record that matches the XML <Code> then this needs to insert in to the DB.

I've been looking at ways of how to match the XML to the DB row and I'm having alittle bit of a problem.

Would it it be easier to get each <Code> value, do a search in the DB, if no result is row is returned, then I'll know this record doesn't exist, so insert, if a row is found, simply update?

I'd like to know other peoples ideas on this, as it's been bothering me for a couple of hours.

Many thanks

 

Have a look at simplexml_load_string(). It'll read the XML into an object which can then be easily manipulated using php to do what your need.

http://www.php.net/manual/en/function.simplexml-load-string.php

 

use REPLACE or ON DUPLICATE KEY UPDATE depending on which you prefer (and which version of MySQL you are using). Examples here :

http://www.kavoir.com/2009/05/mysql-insert-if-doesnt-exist-otherwise-update-the-existing-row.html

 

Ok, thanks all. I have a working version, but I am having a problem with some characters that are in the '<ShortDesc>' field.

Some of the records in the XML are in the form of:

<ShortDesc>Sticks & Stones</ShortDesc>

This gives me an error like:

Warning: simplexml_load_file() [function.simplexml-load-file]: stockdata.xml:1: parser error : xmlParseEntityRef: no name in /../import.php on line 5

Warning: simplexml_load_file() [function.simplexml-load-file]: Code><ShortDesc>Sticks & Stones</ShortDesc> in /../import.php on line 5

Warning: simplexml_load_file() [function.simplexml-load-file]: ^ in /../import.php on line 5

Code (markup):

It seems not to like & or / chars when parsing. Is there a way around this?

Thanks

 

A work around for this is to use simplexml_load_string ( use file_get_contents to load the file as a string) and use:

htmlspecialchars($string, ENT_QUOTES);