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