Having some trouble extracting ECB exchange rates in XML with XML.. full source code in question

Question

I need to grab a list of historic Exchange rate reference rates for an internal accounting system.

This question is about the following code only, feel free to use this code if you find it useful (and of course once we get an answer to fix the final "niggle". i have included a DB structure dump in the code, all DB routines are commented out, just echoing debug data for now.

Data is taken from the European Central Bank XML found at http://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.xml

FIXED! Feel free to use this code if you need to pull a list of historic exchange rate values into a DB.. make sure to remove echo debugging and sort out the DB queries

<?php

/* DB structure:
 CREATE TABLE IF NOT EXISTS `currency_rates_history` (
  `id` int(4) NOT NULL auto_increment,
  `currency` char(3) character set utf8 collate utf8_unicode_ci NOT NULL default '',
  `rate` float NOT NULL default '0',
  `date` int(4) NOT NULL default '0',
  `est` tinyint(1) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8_unicode_ci AUTO_INCREMENT=1 ;

*/

error_reporting(E_ALL);

$table = "currency_rates_history";

$secs = '86400';

$prev_date = time();

$days = "0";

$XML=simplexml_load_file("http://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.xml");    // European Central Bank xml only contains business days! oh well....


foreach($XML->Cube->Cube as $time)                                                          // run first loop for each date section
{

    echo "<h1>".$time["time"].'</h1>';

    list($dy,$dm,$dd) = explode("-", $time["time"]);

    $date = mktime(8,0,0,$dm,$dd,$dy);

    echo ($prev_date - $date)."<br />";

    if(($prev_date - $date) > $secs)                                                        // detect missing weekend and bank holiday values.
    {

        echo "ooh";                                                                         // for debug to search the output for missing days

        $days =(round((($prev_date - $date)/$secs),0)-1);                                   // got to remove 1 from the count....

        echo $days;                                                                         // debug, will output the number of missing days

    }
    foreach($time->Cube as $_rate)                                              // That fixed it! run the 2nd loop and ad enter the new exchange values....
    {

        $rate = floatval(str_replace(",", ".", $_rate["rate"]));


        if($days > 0)                                                                       // add the missing dates using the last known value, coul dbe more accurate but at least there is some reference data to work with
        {
            $days_cc = $days;                                                               // need to keep $days in mem for the next currency

            while($days_cc > 0)
            {
                echo $rate;
                echo date('D',$date+($days_cc*$secs))."<br />";
                /*
                mysql_query("LOCK TABLES {$table} WRITE");

                mysql_query("INSERT INTO {$table}(rate,date,currency,est) VALUES('{$rate}','".($date+($days_cc*$secs))."','{$currency}','1')");

                mysql_query("UNLOCK TABLES");
                */
                $days_cc = ($days_cc - 1);  // count down
            }
        }

        $currency = addslashes(strtolower($_rate["currency"]));
        /*
        mysql_query("LOCK TABLES {$table} WRITE");
//      mysql_query("UPDATE {$table} SET rate='{$rate}',date='{$date}' WHERE currency='{$currency}' AND date='{$date}'");   // all this double checking was crashing the script
//      if (mysql_affected_rows() == 0)
//      {
            mysql_query("INSERT INTO {$table}(rate,date,currency) VALUES('{$rate}','{$date}','{$currency}')");              // so just insert, its only going to be run once anyway!
//      }

        mysql_query("UNLOCK TABLES");
        */

        echo "1&euro;=  ".$currency."   ".$rate.", date:   ".date('D d m Y',$date)."<br/>";
    }
          $days="";                                                                         // clear days value
    $prev_date = $date;                                                                     // store the previous date
}

echo "<h1>Currencies Saved!</h1>";
?>

So..... the problem is in the 2nd foreach loop: foreach($XML->Cube->Cube->Cube as $_rate), if you try running the script you will notice that the dates are right, it handles missing weekend and bank holiday dates well, but the rate values only ever reference the latest rates in the XML, ie todays values.

It should be pulling data from the relevant area in the XML, ie the rates for the given date... but its not. Is this a problem in simplexml_load_file or have I missed somthing stupid in my code? Head starting to hurt now so gonna take a break. Fresh eyes are most welcome!

Answer 1

$XML->Cube->Cube->Cube in your second loop: $XML->Cube->Cube always refers to the first second-level cube in the first first-level cube. So you were iterating over the third level cubes in that same element. Thus, you got only today's rates. Make sense?

Try this instead. I've modified your code for command-line output rather than html. The only major change is in the second foreach statement...

$XML=simplexml_load_file("http://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.xml");
foreach($XML->Cube->Cube as $time){
    echo "----".$time["time"]. "----\n";
    foreach($time->Cube as $_rate){
        $rate = floatval(str_replace(",", ".", $_rate["rate"]));
        $currency = addslashes(strtolower($_rate["currency"]));
        echo "1 euro =  ".$currency."   ".$rate . "\n";
    }
    echo "------------------\n\n";
}
echo "Done!";