Now, that we can draw a graph of the exchange rate, the info contained within our database will quickly grow outdated if we do not update it with the new daily data. mybloggingplanet.com

Fortunately, the European Central Bank comes to the rescue once again. They have an XML feed which contains the latest exchange rate for all the currencies that are listed in the history data. All we need to do is get the contents and then extract the values we need out of it.

The XML feed can be found at http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml.

Using an XML parser, which converts the XML into an object, we can quickly find currencies we want, and then create an insert statement to update our database.

This script would need to be run daily to keep the database fully up-to-date, so one way to do this would be to run the script as a cron job on your server.

$row[ipaddress] = "localhost";
$row[dbusername] = "root";
$row[dbpassword] = "password";
$row[dbname] = "exchange";

$contents = '';
$file = "http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml";
if($handle = @fopen($file, "r")){
	while (!feof($handle)) {
	   $contents .= fread($handle, 8192);
	}
	fclose($handle);

	if (strlen($contents) > 0){

		$data = simplexml_load_string($contents);
		$currdate = (string)$currdate = $data->Cube->Cube['time'];
		foreach($data->Cube->Cube->Cube as $curr){
			switch ((string)$curr['currency']){
				case 'GBP':
					$gbp = (string)$curr['rate'];
					break;
				case 'USD':
					$usd = (string)$curr['rate'];
					break;
				case 'ZAR':
					$zar = (string)$curr['rate'];
					break;
				case 'NZD':
					$nzd = (string)$curr['rate'];
					break;
				case 'AUD':
						$aud = (string)$curr['rate'];
				case 'CHF':
						$chf = (string)$curr['rate'];
					break;
			}
		}		
		$dbconn = mysql_connect($row[ipaddress], $row[dbusername], $row[dbpassword]);
		mysql_select_db($row[dbname], $dbconn);
		echo $gbp;

		$sql = "SELECT * FROM `exchangerates` WHERE `ratedate` = '$currdate'";
		$res = mysql_query($sql, $dbconn);
		if (mysql_num_rows($res) == 0){
			$sql = "INSERT INTO `exchangerates` (`ratedate`, `GBP`, `USD`, `ZAR`, `AUD`, `NZD`, `CHF`) VALUES ('$currdate', '$gbp', '$usd', '$zar', '$aud', '$nzd', '$chf')";
			echo $sql;
			mysql_query($sql, $dbconn);
		}
		mysql_close($dbconn);
	}
}

In the next part of the series, we will be putting the finishing touches on creating the graph

Share