Please help echoing select label

lingo5 · February 20, 2012

Hi,

I have a select menu like this:

<form id="form1" name="form1" method="post" action="<?php htmlentities($_SERVER['PHP_SELF']); ?>">
  <table width="100%" border="0" align="center" cellpadding="2" cellspacing="0" >
    <tr>
      <td width="38" align="left" class="UsuarioNombreHeader"><label>
        <input name="CurrencyAmount" type="text" class="CP_ExchangeFormFields" id="CurrencyAmount" size="6" />
      </label></td>
      <td width="11" align="left" nowrap="nowrap" class="personalTitulo">€</td>
      <td width="11" align="left" nowrap="nowrap" class="UsuarioNombreHeader">to</td>
      <td width="204" align="left" nowrap="nowrap" class="UsuarioNombreHeader"><select name="selectCurrencyTo" class="CP_loginFormFields" id="selectCurrencyTo">
        <option value="value" >Select currency</option>
        <option value="<?php echo $row_currency_RS['AUD']; ?>" >Australia Dollar (AUD)</option>
        <option value="<?php echo $row_currency_RS['BRL']; ?>" >Brazil Real (BRL)</option>
        <option value="<?php echo $row_currency_RS['BGN']; ?>" >Bulgaria Lev (BGN)</option>
        <option value="<?php echo $row_currency_RS['CAD']; ?>" >Canada Dollar (CAD)</option>
        <option value="<?php echo $row_currency_RS['CNY']; ?>" >China Yuan Renminbi (CNY)</option>
        <option value="<?php echo $row_currency_RS['HRK']; ?>" >Croatia Kuna (HRK)</option>
        <option value="<?php echo $row_currency_RS['CZK']; ?>" >Czech Republic Koruna (CZK)</option>
        <option value="<?php echo $row_currency_RS['DKK']; ?>" >Denmark Krone (DKK)</option>
        <option value="<?php echo $row_currency_RS['HKD']; ?>" >Hong Kong Dollar (HKD)</option>
        <option value="<?php echo $row_currency_RS['HUF']; ?>" >Hungary Forint (HUF)</option>
        <option value="<?php echo $row_currency_RS['INR']; ?>" >India Rupee (INR)</option>
        <option value="<?php echo $row_currency_RS['IDR']; ?>" >Indonesia Rupiah (IDR)</option>
        <option value="<?php echo $row_currency_RS['ILS']; ?>" >Israel Shekel (ILS)</option>
        <option value="<?php echo $row_currency_RS['JPY']; ?>" >Japanese Yen (JPY)</option>
        <option value="<?php echo $row_currency_RS['KRW']; ?>" >Korea (South) Won (KRW)</option>
        <option value="<?php echo $row_currency_RS['LVL']; ?>" >Latvia Lat (LVL)</option>
        <option value="<?php echo $row_currency_RS['LTL']; ?>" >Lithuania Litas (LTL)</option>
        <option value="<?php echo $row_currency_RS['MYR']; ?>" >Malaysia Ringgit (MYR)</option>
        <option value="<?php echo $row_currency_RS['MXN']; ?>" >Mexico Peso (MXN)</option>
        <option value="<?php echo $row_currency_RS['NZD']; ?>" >New Zealand Dollar (NZD)</option>
        <option value="<?php echo $row_currency_RS['NOK']; ?>" >Norway Krone (NOK)</option>
        <option value="<?php echo $row_currency_RS['PHP']; ?>" >Philippines Peso (PHP)</option>
        <option value="<?php echo $row_currency_RS['PLN']; ?>" >Poland Zloty (PLN)</option>
        <option value="<?php echo $row_currency_RS['RON']; ?>" >Romania New Leu (RON)</option>
        <option value="<?php echo $row_currency_RS['RUB']; ?>" >Russia Ruble (RUB)</option>
        <option value="<?php echo $row_currency_RS['SGD']; ?>" >Singapore Dollar (SGD)</option>
        <option value="<?php echo $row_currency_RS['ZAR']; ?>" >South Africa Rand (ZAR)</option>
        <option value="<?php echo $row_currency_RS['SEK']; ?>" >Sweden Krona (SEK)</option>
        <option value="<?php echo $row_currency_RS['CHF']; ?>" >Switzerland Franc (CHF)</option>
        <option value="<?php echo $row_currency_RS['THB']; ?>" >Thailand Baht (THB)</option>
        <option value="<?php echo $row_currency_RS['TRY']; ?>" >Turkey Lira (TRY)</option>
        <option value="<?php echo $row_currency_RS['GBP']; ?>" >United Kingdom Pound (GBP)</option>
        <option value="<?php echo $row_currency_RS['USD']; ?>" >US Dollar (USD)</option>
      </select></td>
      <td width="58" align="left" nowrap="nowrap" class="UsuarioNombreHeader"><input type="submit" name="button" id="button" value="convert" /></td>
      <td width="38" align="left" nowrap="nowrap" class="UsuarioNombreHeader"><input name="CurrencyResult" type="text" class="CP_ExchangeFormFields" id="CurrencyResult" value="<?php echo $total_euros ?>" size="6" /></td>
      <td width="819" align="left" nowrap="nowrap" class="UsuarioNombreHeader"><?php echo $currency_amount?> € = <?php echo $total_euros?> <?php echo $select_currency?> </td>
    </tr>
  </table>
</form>

This is part of a currency conversion script I have done.

Now I am outputting the result of the currency conversion like this:

<?php echo $currency_amount?> € = <?php echo $total_euros?> <?php echo HERE_I_WANT_THE_CURRENCY_NAME?>

but because the select men labels are not dynamically pulled from my DB (due to the way the DB had to be done), I can't figure out how to echo the currency name at the end of the conversion result.

Any ideas?...Thanks

Psycho · February 20, 2012

You need to have the data that associates the currency "id" (e.g. 'AUD', 'BRL', etc) with the currency name is some for. This can be done in the database or in an array. Then you don't have to have 30+ lines of code to create your select list. Then you should use the ID as the value of the select list instead of $row_currency_RS[<ID VALUE>]

Then when the user POSTS the data you can get the $row_currency_RS[<ID VALUE>] value from the ID passed AND you can get the name using that ID.

lingo5 · February 20, 2012

Thanks Psycho....but the DB had to be organised with a column for each currency, being the currency name the title for the column. This was done like this because the data for this DB is retrieved fom the European Central Bank feed in that form.

Psycho · February 20, 2012

Thanks Psycho....but the DB had to be organised with a column for each currency, being the currency name the title for the column. This was done like this because the data for this DB is retrieved fom the European Central Bank feed in that form.

What you are doing does not make sense. You can do as I stated without changing your current database. Whether you decide to put this new information in the database or a flat-file is not important. But, you need to create the data so you can associate the currently code with the currency name.

If you have a table with the currency name, add another column for the currency code. Then use THAT for the value you pass.

I just checked the feeds available for the European Central Bank and they are all in the format

http://www.ecb.europa.eu/rss/fxref-[CURRENCY_CODE].html

So, why you are not using the currency codes is beyond me.

Psycho · February 20, 2012

If you would like further help from me, please post the entire script as well as a description of the database tables/fields.

lingo5 · February 21, 2012

Thanks Psycho,

I am a bit confued with this, so I am posting some code here.

This is how I read the ECB feed and nsert currency values in my DB:

<?php
// set the default timezone to use. Available since PHP 5.1
date_default_timezone_set('Europe/Madrid');
$datetime = date("Y-m-d H:i:s",time());
$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 MY_DATABASE.exchange SET ratedate= '$datetime'," . $currencyCode[1] . " = " . $rate[1] . " WHERE id = 1";
$result = mysql_query($query);
}
}
}
?>

This is my DB structure:

id` tinyint(1) NOT NULL,                           
            `ratedate` datetime DEFAULT '0000-00-00 00:00:00',  
            `USD` double(15,2) NOT NULL,                        
            `JPY` double(15,2) NOT NULL,                        
            `BGN` double(15,2) NOT NULL,                        
            `CZK` double(15,2) NOT NULL,                        
            `DKK` double(15,2) NOT NULL,                        
            `GBP` double(15,2) NOT NULL,                        
            `HUF` double(15,2) NOT NULL,                        
            `LTL` double(15,2) NOT NULL,                        
            `LVL` double(15,2) NOT NULL,                        
            `PLN` double(15,2) NOT NULL,                        
            `RON` double(15,2) NOT NULL,                        
            `SEK` double(15,2) NOT NULL,                        
            `CHF` double(15,2) NOT NULL,                        
            `NOK` double(15,2) NOT NULL,                        
            `HRK` double(15,2) NOT NULL,                        
            `RUB` double(15,2) NOT NULL,                        
            `TRY` double(15,2) NOT NULL,                        
            `AUD` double(15,2) NOT NULL,                        
            `BRL` double(15,2) NOT NULL,                        
            `CAD` double(15,2) NOT NULL,                        
            `CNY` double(15,2) NOT NULL,                        
            `HKD` double(15,2) NOT NULL,                        
            `IDR` double(15,2) NOT NULL,                        
            `ILS` double(15,2) NOT NULL,                        
            `INR` double(15,2) NOT NULL,                        
            `KRW` double(15,2) NOT NULL,                        
            `MXN` double(15,2) NOT NULL,                        
            `MYR` double(15,2) NOT NULL,                        
            `NZD` double(15,2) NOT NULL,                        
            `PHP` double(15,2) NOT NULL,                        
            `SGD` double(15,2) NOT NULL,                        
            `THB` double(15,2) NOT NULL,                        
            `ZAR` double(15,2) NOT NULL,                        
            PRIMARY KEY (`id`)                                  
          ) ENGINE=InnoDB DEFAULT CHARSET=latin1

...and this is the select menu:

<form id="form1" name="form1" method="post" action="<?php htmlentities($_SERVER['PHP_SELF']); ?>">
  <table width="100%" border="0" align="center" cellpadding="2" cellspacing="0" >
    <tr>
      <td width="38" align="left" class="UsuarioNombreHeader"><label>
        <input name="CurrencyAmount" type="text" class="CP_ExchangeFormFields" id="CurrencyAmount" size="6" />
      </label></td>
      <td width="11" align="left" nowrap="nowrap" class="personalTitulo">€</td>
      <td width="11" align="left" nowrap="nowrap" class="UsuarioNombreHeader">to</td>
      <td width="204" align="left" nowrap="nowrap" class="UsuarioNombreHeader"><select name="selectCurrencyTo" class="CP_loginFormFields" id="selectCurrencyTo">
        <option value="value" >Select currency</option>
        <option value="<?php echo $row_currency_RS['AUD']; ?>" >Australia Dollar (AUD)</option>
        <option value="<?php echo $row_currency_RS['BRL']; ?>" >Brazil Real (BRL)</option>
        <option value="<?php echo $row_currency_RS['BGN']; ?>" >Bulgaria Lev (BGN)</option>
        <option value="<?php echo $row_currency_RS['CAD']; ?>" >Canada Dollar (CAD)</option>
        <option value="<?php echo $row_currency_RS['CNY']; ?>" >China Yuan Renminbi (CNY)</option>
        <option value="<?php echo $row_currency_RS['HRK']; ?>" >Croatia Kuna (HRK)</option>
        <option value="<?php echo $row_currency_RS['CZK']; ?>" >Czech Republic Koruna (CZK)</option>
        <option value="<?php echo $row_currency_RS['DKK']; ?>" >Denmark Krone (DKK)</option>
        <option value="<?php echo $row_currency_RS['HKD']; ?>" >Hong Kong Dollar (HKD)</option>
        <option value="<?php echo $row_currency_RS['HUF']; ?>" >Hungary Forint (HUF)</option>
        <option value="<?php echo $row_currency_RS['INR']; ?>" >India Rupee (INR)</option>
        <option value="<?php echo $row_currency_RS['IDR']; ?>" >Indonesia Rupiah (IDR)</option>
        <option value="<?php echo $row_currency_RS['ILS']; ?>" >Israel Shekel (ILS)</option>
        <option value="<?php echo $row_currency_RS['JPY']; ?>" >Japanese Yen (JPY)</option>
        <option value="<?php echo $row_currency_RS['KRW']; ?>" >Korea (South) Won (KRW)</option>
        <option value="<?php echo $row_currency_RS['LVL']; ?>" >Latvia Lat (LVL)</option>
        <option value="<?php echo $row_currency_RS['LTL']; ?>" >Lithuania Litas (LTL)</option>
        <option value="<?php echo $row_currency_RS['MYR']; ?>" >Malaysia Ringgit (MYR)</option>
        <option value="<?php echo $row_currency_RS['MXN']; ?>" >Mexico Peso (MXN)</option>
        <option value="<?php echo $row_currency_RS['NZD']; ?>" >New Zealand Dollar (NZD)</option>
        <option value="<?php echo $row_currency_RS['NOK']; ?>" >Norway Krone (NOK)</option>
        <option value="<?php echo $row_currency_RS['PHP']; ?>" >Philippines Peso (PHP)</option>
        <option value="<?php echo $row_currency_RS['PLN']; ?>" >Poland Zloty (PLN)</option>
        <option value="<?php echo $row_currency_RS['RON']; ?>" >Romania New Leu (RON)</option>
        <option value="<?php echo $row_currency_RS['RUB']; ?>" >Russia Ruble (RUB)</option>
        <option value="<?php echo $row_currency_RS['SGD']; ?>" >Singapore Dollar (SGD)</option>
        <option value="<?php echo $row_currency_RS['ZAR']; ?>" >South Africa Rand (ZAR)</option>        <option value="<?php echo $row_currency_RS['SEK']; ?>" >Sweden Krona (SEK)</option>
        <option value="<?php echo $row_currency_RS['CHF']; ?>" >Switzerland Franc (CHF)</option>
        <option value="<?php echo $row_currency_RS['THB']; ?>" >Thailand Baht (THB)</option>
        <option value="<?php echo $row_currency_RS['TRY']; ?>" >Turkey Lira (TRY)</option>
        <option value="<?php echo $row_currency_RS['GBP']; ?>" >United Kingdom Pound (GBP)</option>
        <option value="<?php echo $row_currency_RS['USD']; ?>" >US Dollar (USD)</option>
      </select></td>
      <td width="58" align="left" nowrap="nowrap" class="UsuarioNombreHeader"><input type="submit" name="button" id="button" value="convert" /></td>
      <td width="38" align="left" nowrap="nowrap" class="UsuarioNombreHeader"><input name="CurrencyResult" type="text" class="CP_ExchangeFormFields" id="CurrencyResult" value="<?php echo $total_euros ?>" size="6" /></td>
      <td width="819" align="left" nowrap="nowrap" class="UsuarioNombreHeader"><?php echo $currency_amount?> € = <?php echo $total_euros?> <?php echo $select_currency?> </td>
    </tr>
  </table>
</form>

I would like to know how to add the CURRENCY_CODE field to my existing database. Perhaps I need to modify my DB structure?....I'm in a mess....

Psycho · February 21, 2012

Well, I am going to suggest a complete rewrite. The DB structure is wrong. You should have individual records for each currency type - not a field for each currency. That type of structure does not scale. I.e. If currencies are added/removed you should not have to change your DB structure. Your table should only need three fields: code, name, rate, ratedate.

CREATE TABLE `exchange` (
  `code` varchar(4) collate latin1_general_ci NOT NULL,
  `name` varchar(50) collate latin1_general_ci NOT NULL,
  `rate` float NOT NULL,
  `ratedate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`code`)
)

Note the use of an auto-update timestamp field - so you don't have to set the time when creating/updating records, it will be done automatically.

Here is the code to insert/update the database records. Note, this will work even if rates are added/removed from the feed.

//Load XML file
$XML=simplexml_load_file("[url=http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml]http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml[/url]");

//Process data into values for DB
$insertValsAry = array();
foreach($XML->Cube->Cube->Cube as $rate)
{
    $code = mysql_real_escape_string(trim($rate['currency']));
    $rate = floatval($rate['rate']);
    $insertValsAry[] = "('$code', '$rate')";
}

//Create and run INSERT/UPDATE query
$insertVals = implode(', ', $insertValsAry);
$query = "INSERT INTO exchange (`code`, `rate`)
          VALUES $insertVals
          ON DUPLICATE KEY UPDATE `rate` = VALUES(`rate`)";
$result = mysql_query($query) or die(mysql_error());

NOTE: The data feed does not include the 'name' of the currency, so you would have to add that value for each record one time. You could create a page to do that, but since it would be so rare, you should just do it directly in the DB.

And, here is the page to allow the user to get the exchange rate value

$fromCode = isset($_POST['selectCurrencyTo']) ? strtoupper($_POST['selectCurrencyTo']) : '';
$fromAmount = isset($_POST['from_amount']) ? floatval($_POST['from_amount']) : 0;
$output = '';

//Create/run query to get list of available rate codes
$query = "SELECT `code`, `name`, `rate` FROM `exchange` ORDER BY `name`";
$result = mysql_query($query) or die(mysql_error());
$rateOptions = '';
while($row = mysql_fetch_assoc($result))
{
    if($row['code'] != $fromCode)
    {
        $selected = '';
    }
    else
    {
        $selected = ' selected="selected"';
        $toAmount = round($row['rate'] * $fromAmount, 2);
        $output = "{$fromAmount} € = {$toAmount} {$row['code']}";
    }

    $rateOptions .= "<option value=\"{$row['code']}\" {$selected}>{$row['name']} ({$row['code']})</option>\n";
}

?>
<html>
<head></head>
<body>

<form id="form1" name="form1" method="post" action="<?php htmlentities($_SERVER['PHP_SELF']); ?>">
    <input name="from_amount" type="text" class="CP_ExchangeFormFields" id="CurrencyAmount" size="6" value="<?php echo $fromAmount; ?>" />€
    <span style="padding:5px;">to</span>
    <select name="selectCurrencyTo" class="CP_loginFormFields" id="selectCurrencyTo">
        <option value="" >Select currency</option>
        <?php echo $rateOptions; ?>
    </select>
    <input type="submit" name="button" id="button" value="convert" />
    <br><br>
    <?php echo $output; ?>
    
</form>

</body>
</html>

There is still alot of clean up I would do on this though. I'll leave that to you.

lingo5 · February 21, 2012

Thanks a lot Psycho,

I get this error message though

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON DUPLICATE KEY UPDATE `rate` = VALUES(`rate`)' at line 3

Psycho · February 21, 2012

It worked for me. As I said, there is still some cleanup needed - such as better error handling. Echo the entire query to the page. That should make it easier to debug. Also, I assume you needed to make same minor changes to the queries I provided. Please post the lines that create the query as well.

You did make the 'code' field the primary field, right?

lingo5 · February 22, 2012

Psycho, thanksso much for your help!!!!. I did a bit of cleaning up and your code worked great.

Thanks again for your expert advice...I really really reaaaally appreciate it.

Sign In

Please help echoing select label

Recommended Posts

lingo5

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

lingo5

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

lingo5

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

lingo5

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

lingo5

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information