Please help inserting array

[lingo5]

Hi,

I have this code to retrieve currency exchange rates from the ECB:

 

<?php

    $XMLContent=file("http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml");
    //the file is updated daily between 2.15 p.m. and 3.00 p.m. CET
            
    foreach($XMLContent as $line){
        if(preg_match("/currency='([[:alpha:]]+)'/",$line,$currencyCode)){
            if(preg_match("/rate='([[:graph:]]+)'/",$line,$rate)){
                //Output the value of 1EUR for a currency code
                echo'1€='.$rate[1].' '.$currencyCode[1].'<br/>';
                //--------------------------------------------------
                //Here you can add your code for inserting
                //$rate[1] and $currencyCode[1] into your database
                //--------------------------------------------------
            }
        }
}
?> 

I have a DB with 2 fields, one is for the exchange rate (rate) and  the other for thecurrency name (currencyCode).

 

My problem is that I don't know how to insert the $rate[1] and $currencyCode[1] in my DB.

Thanks for your help.

[sunfighter]

I'd make the table with 34 columns. Name the columns after the currency name plus an id and place the rate in the column. Maybe you want the date also??

 

If your going to replace the info by destroying the old data you might think on using an array to store the info.

 

But back to my idea. Assume table already set up with data and an id column where the id is set to 1.

UPDATE table_name SET $currencyCode[1] = $rate[1] WHERE id = 1;

 

This will set up your db

/* Database export results for db hours*/

/* Preserve session variables */
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS;
SET FOREIGN_KEY_CHECKS=0;

/* Export data */

/* table structure for places */
CREATE TABLE `exchange` (
  `id` tinyint(1) NOT NULL,
  `USD` decimal(10,5) NOT NULL,
  `JPY` decimal(10,5) NOT NULL,
  `BGN` decimal(10,5) NOT NULL,
  `CZK` decimal(10,5) NOT NULL,
  `DKK` decimal(10,5) NOT NULL,
  `GBP` decimal(10,5) NOT NULL,
  `HUF` decimal(10,5) NOT NULL,
  `LTL` decimal(10,5) NOT NULL,
  `LVL` decimal(10,5) NOT NULL,
  `PLN` decimal(10,5) NOT NULL,
  `RON` decimal(10,5) NOT NULL,
  `SEK` decimal(10,5) NOT NULL,
  `CHF` decimal(10,5) NOT NULL,
  `NOK` decimal(10,5) NOT NULL,
  `HRK` decimal(10,5) NOT NULL,
  `RUB` decimal(10,5) NOT NULL,
  `TRY` decimal(10,5) NOT NULL,
  `AUD` decimal(10,5) NOT NULL,
  `BRL` decimal(10,5) NOT NULL,
  `CAD` decimal(10,5) NOT NULL,
  `CNY` decimal(10,5) NOT NULL,
  `HKD` decimal(10,5) NOT NULL,
  `IDR` decimal(10,5) NOT NULL,
  `ILS` decimal(10,5) NOT NULL,
  `INR` decimal(10,5) NOT NULL,
  `KRW` decimal(10,5) NOT NULL,
  `MXN` decimal(10,5) NOT NULL,
  `MYR` decimal(10,5) NOT NULL,
  `NZD` decimal(10,5) NOT NULL,
  `PHP` decimal(10,5) NOT NULL,
  `SGD` decimal(10,5) NOT NULL,
  `THB` decimal(10,5) NOT NULL,
  `ZAR` decimal(10,5) NOT NULL,

  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/* data for table places */
insert into exchange values(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1);

 

This is the php to update it:

<?php
require ('./inc/emmy_connect.php');
    $XMLContent=file("http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml");
    //the file is updated daily between 2.15 p.m. and 3.00 p.m. CET
$i = 0;
    foreach($XMLContent as $line){
        if(preg_match("/currency='([[:alpha:]]+)'/",$line,$currencyCode)){
            if(preg_match("/rate='([[:graph:]]+)'/",$line,$rate)){
                //Output the value of 1EUR for a currency code
                echo '1€=' . $rate[1] . ' ' . $currencyCode[1] . ' -- ' . $i++.'<br/>';
                $query = "UPDATE exchange SET " . $currencyCode[1] . " = " . $rate[1] . " WHERE id = 1";
			$result = mysql_query($query);
            }
        }
}
?>

[lingo5]

thanks Sunfighter,

what will happen when a new currency is added to the ECB xml?, I will have to modify my DB by hand?...and will the values b inserted in my db in the correct order if new currencies are added to their xml?

Is there a way to do this so that currences are added to my DB automatically even if they add new ones?

Please correct me if I'm wrong. Thanks again

[sunfighter]

While it is posible to add columns and drop them from a table by programming, it may be easier to use a multidimensional array. It is easy to store the array in a file if need be. Below is the code for making the array with a dump for showing the array's contents and a method for displaying the individual rates.

PS. The database method I gave you earlier suffers from updating each column one at a time. While 33 accesses may not be too bad, it's not elegant.

 

<?php
    $XMLContent=file("http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml");
    //the file is updated daily between 2.15 p.m. and 3.00 p.m. CET
foreach($XMLContent as $line){
    if(preg_match("/currency='([[:alpha:]]+)'/",$line,$currencyCode)){
        if(preg_match("/rate='([[:graph:]]+)'/",$line,$rate)){
            //Output the value of 1EUR for a currency code
            //echo '1€ = ' . $rate[1] . ' ' . $currencyCode[1] . ' -- ' . $i++.'<br/>';
		$name[] = $currencyCode[1];
		$money[] = $rate[1];
        }
    }
}
$big = array_combine($name, $money);
print_r($big);
echo '<br />===================<br />';
$first = 'USD';
$last = 'ZAR';

echo 'The exchange rate for '.$first.' is '.$big[$first].'<br />';
echo 'The exchange rate for '.$last.' is '.$big[$last].'<br />';
?>

[lingo5]

Thanks a lot for your help Sunfighter....I have finally solved it by using the first method you suggested.

Thanks again for your time.