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

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.

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

Member Avatar for iamthwee

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.

Member Avatar for iamthwee

*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.

Got it, thanks!!

/Adam

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.