Currency exchange in your application

db, development, ideas, php, zend Add comments

That’s easy, you need 2 things:

  1. Fresh currencies exchange rates
  2. Some way to excange amount from one currency to another.

This how I did it: get values from European Central Bank (ECB) for step #1 and wrote MySQL user defined function for step #2.

Here is how to export currencies rates from ECB (EUR is a base currency, and I add self rate as 1:1). First I create such database table:

CREATE TABLE IF NOT EXISTS `currency` (
  `code` char(3) NOT NULL DEFAULT '',
  `rate` decimal(10,5) NOT NULL COMMENT 'Rate to EUR got from www.ecb.int',
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Currency rates (regularly updated)';

Now let’s fill it with rates:

<?php

class CurrencyController extends Controller_Ajax_Action {

  public function importAction() {
  
    $db = Zend_Registry::get('db');
    $db->beginTransaction();
    
    $url = 'http://www.ecb.int/stats/eurofxref/eurofxref.zip?1c7a343768baab4322620e3498553b5a';
    try {
      $contents = file_get_contents($url);
      $contents = archive::unzip($contents);
      $contents = explode("\n", $contents);
      
      $names = explode(',', $contents[0]);
      $rates = explode(',', $contents[1]);
      
      $names[] = 'EUR';
      $rates[] = 1;
    
      for ($i = 1; $i < sizeof($names); $i++) {
        if (!(float) $rates[$i]) continue;
        $db->query( sprintf('INSERT INTO `currency`(`code`, `rate`)
              VALUES ("%s", %10.5f)
              ON DUPLICATE KEY UPDATE `rate`=VALUES(`rate`)', 
             trim( $names[$i] ), 
             trim( $rates[$i] )
        ) );
      }
      
      $db->commit();
    } catch ( Exception $O_o ) {
      error_log( $O_o->getMessage() );
      $db->rollback();
    }
    
  }
}

Now let’s create a SQL function for handy converts. Create a udf.sql file and add this in it:

DELIMITER //

DROP  FUNCTION IF EXISTS EXCHANGE;
CREATE FUNCTION EXCHANGE( amount DOUBLE, cFrom CHAR(3), cTo CHAR(3) ) RETURNS DOUBLE READS SQL DATA DETERMINISTIC
    COMMENT 'converts money amount from one currency to another'
BEGIN
    DECLARE rateFrom DOUBLE DEFAULT 0;
    DECLARE rateTo DOUBLE DEFAULT 0;
    
    
    SELECT `rate` INTO rateFrom FROM `currency` WHERE `code` = cFrom;
    SELECT `rate` INTO rateTo   FROM `currency` WHERE `code` = cTo;
    
    IF ISNULL( rateFrom ) OR ISNULL( rateTo ) THEN
        RETURN NULL;
    END IF;
    
    RETURN amount * rateTo / rateFrom;
END; //

DELIMITER ;

and run this command in your shell:

mysql --user=USER --password=PASS DATABASE < udf.sql

This is how you can use this function — how to convert 10 US dollars to Canadian dollars:

SELECT EXCHANGE( 10, 'USD', 'CAD')

which results in $10 = 10.93 Canadian dollars.

P.S. Consider adding the currency export action call to your cron scripts.

P.P.S. A function to unzip the data file can be got at php.net

One Response to “Currency exchange in your application”

  1. Adam Says:

    Good code for currency exchange. It solved my problem so thanks to you.

WP Theme & Icons by N.Design Studio
Entries RSS Comments RSS Log in