Over the next few blog posts I am going to explain how to create an exchange rate graphing script in PHP, which takes the two currencies as input as well as the period to calculate for, and then outputs an image showing the graph of how the exchange rate has varied over time.

One of the biggest problems when trying to display exchange rate data is where to get the data to display, but that is not a major worry, as there are several sources of this information freely available on the web. mybloggingplanet.com

The source where I got the historical data is from the European Central Bank, which publishes a list of exchange rates for many currencies against the Euro, with historical data dating back to 1999. The data is available for download here.

Of particular note here is the historical file, which can be downloaded as a CSV, which we are going to use to populate our database.

The first thing we need to do though is to create a MySQL database to store the exchange rate data in. The query to create the table we need is as follows:

CREATE TABLE `exchangerates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ratedate` date DEFAULT NULL,
  `GBP` decimal(10,4) DEFAULT NULL,
  `USD` decimal(10,4) DEFAULT NULL,
  `ZAR` decimal(10,4) DEFAULT NULL,
  `AUD` decimal(10,4) DEFAULT NULL,
  `NZD` decimal(10,4) DEFAULT NULL,
  `CHF` decimal(10,4) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

Now that we have a database structure and have the historical exchange rate data, we need a script to populate our database. This is done with this little script.

what this script does is parse the CSV file, and then selects the columns in the CSV corresponding to the currencies we are interested in – we don’t want most of the currencies. The script builds an insert statement for each line and then executes the query against the database, and at the end of it, we have the full historical exchange rate data since 1999 in our database.

require('csvparser.php');
$row[ipaddress] = "localhost";
$row[dbusername] = "root";
$row[dbpassword] = "";
$row[dbname] = "exchange";

$data = csvParser::readCSV('eurofxref-hist.csv');
//var_dump($data);		
$dbconn = mysql_connect($row[ipaddress], $row[dbusername], $row[dbpassword]);
mysql_select_db($row[dbname], $dbconn);

echo "Rows: " . count($data)."
";
for($i = 1; $i < count($data); $i++){
	$sql = "INSERT INTO `exchangerates` (`ratedate`, `GBP`, `USD`, `ZAR`, `AUD`, `NZD`, `CHF`) VALUES ('".$data[$i][0]."', '".$data[$i][8]."', '".$data[$i][1]."', '".$data[$i][40]."', '".$data[$i][26]."', '".$data[$i][36]."', '".$data[$i][19]."')";
	echo $sql."
";
	mysql_query($sql, $dbconn);
}
mysql_close($dbconn);

The csvparser.php file is as follows:

class csvParser{
	
	public static function readCSV($filename, $delimiter = ",", $lineLength = 2000){
		if($handle = fopen($filename, "r")){
			$csvData = array();
			while (($row = fgetcsv($handle, $lineLength, $delimiter)) !== false) {
				$csvData[] = $row;
			}
			fclose($handle);
			return $csvData;
		}else{
			return false;
		}
	}
	
	public static function insertQuotes($str, $delimiter = ",")
	{
		$str = str_replace('"', '""', $str);
		if (strpos($str, $delimiter) == false){
			return $str;
		}else{
			return '"'.$str.'"';
		}
	}
		
	public static function formatCSV($contentArray, $headerArray = null, $delimiter = ",")
	{
		if (!is_array($contentArray)){
			throw new customException("Cannot convert to CSV - data is not an array");
		} 
		if ((!is_array($headerArray)) && (!is_null($headerArray))){
			throw new customException("Cannot convert to CSV - header data is not an array");
		} 
		$csvStr = "";
		if (!is_null($headerArray)){
			for($i = 0; $i < sizeof($headerArray); $i++){
				if ($i != 0){
					$csvStr .= $delimiter;
				}
				$csvStr .= csvParser::insertQuotes($headerArray[$i], $delimiter); 
			}
			$csvStr = substr($csvStr, 1);
			$csvStr .= "\\r\\n";
		}
		for($j = 0; $j < sizeof($contentArray); $j++){

			for($i = 0; $i < sizeof($contentArray[$j]); $i++){
				if ($i != 0){
					$csvStr .= $delimiter;
				}
				$csvStr .= csvParser::insertQuotes($contentArray[$j][$i], $delimiter); 
			}

			$csvStr .= "\\r\\n";
		}
		return $csvStr;
	} 

}

In the next part, we are going to look at how to draw the graph using this data.

Share