Extract from xml

[adishardis]

Hi, everyday I get a xml file that looks something like this:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<account-information report-date="2012-12-31T23:59:59.000+01:00" xmlns="http://www.XXX.SE/XXX">
    <account>
        <account-number>00000001</account-number>
        <status>Open</status>
        <application-array>
            <application-type>NEW</application-type>
            <application-date>2013-01-11</application-date>
        </application-array>
        <application-array>
            <application-type>TOP</application-type>
            <application-date>2013-04-18</application-date>
        </application-array>
        <account-information-type>
            <first-name-main>John</first-name-main>
        </account-information-type>
    </account>
</account-information>

Today I import each account to our db with only the latest application-array (application-date). The code looks something like this:

  $xml = new SimpleXMLElement('../../../../analys/BaseAccount-19.2.xml', null, true);

    foreach($xml->loanaccount as $mess){
        $accountnumber = $mess->{'account-number'};
        $accountstatus = mysql_real_escape_string($mess->{'account-status'});
                $applicationdate = '';
                    foreach($mess->{'application-array'} as $aa){
                        $totaltlanebelopp +=  mysql_real_escape_string($aa->{'application-amount'}); 
                            if((string) $aa->{'applicationdate'} > $applicationdate){
                                $noterefno =  mysql_real_escape_string($aa->{'note-ref-no'});
                                $applicationtype =  mysql_real_escape_string($aa->{'application-type'});
                                $applicationdate =  mysql_real_escape_string($aa->{'application-date'});
                            }
                    }
        $firstname= mysql_real_escape_string($mess->{'account-information-type'}->{'first-name-main'});
        $lastname= mysql_real_escape_string($mess->{'account-information-type'}->{'last-name-main'});
        ...

        $results= $DBH->prepare("insert ignore INTO XXX(...) VALUES (...)")or die(mysql_error());
        $results->execute();
     }

What I want to do is to insert every 'application-array' in an own row instead of having the 'account-number' with only the latest 'application-array'. I would create a new unique identifier consisting of 'account-number' and 'application-date'.

So in the above case I would like my db to look like this:

------unique_id-----account-number-----status-----application-type-----application-date-----first-name-main----
2013-01-11_00000001-----00000001--------Open------------NEW----------------2013-01-11--------------John--------
2013-04-18_00000001-----00000001--------Open------------NEW----------------2013-04-18--------------John--------

Any ideas on how to change my query to get this result?

Cheers
Adam

[bops]

Instead of doing a single query at the end, do a query inside the innermost loop where you are iterating through each application-array object. I'd recommend just having the account number and application date as separate fields though. You can then create a composite key on these to uniquely identify a row.

[adishardis]

Thanks for your reply! I honestly don't know/understand what that query (in the innermost loop) would look like. I would be eternally grateful if you could give me a hint :)

Thanks
Adam

By hint do you mean exact code. It doesn't get much clearer than that.

You would just execute the query from INSIDE the if statement rather than at the end.

*Edit if you can't do that (because the first name is only given at the end) you could create some temp storage (array) then loop through the array at the end.

[adishardis]

Got it, thanks!!

/Adam