database help

[fazzfarrell]

rI have created a database with currency conversions like this:

CREATE TABLE `currency` (
`ID` double default NULL,
`Cont` varchar(255) default NULL,
`Rate` double default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `currency`
--

INSERT INTO `currency` (`ID`, `Cont`, `Rate`) VALUES (1, 'American Dollars', 1.87121),
(2, 'Euros', 1.48475),
(3, 'British Pounds', 1);


I then have a drop down menu on the page to choose 'Euros' British Pounds' etc.

<form id="frmCurr" name="frmCurr" method="post">
<select name="Currency" OnChange="UpdateQuantity()">
<option selected="selected">GBP</option>
<option value="British Pounds">British Pounds</option>
<option value="American Dollars">American Dollars</option>
<option value="Euro">Euro</option>

</select>
</form>

I want to be able to select a currency and call that data from the database, do I do this via a session?

I am new to this any help would be great thanks

[dymon]

I don't really understand, do you want to call the data using JS, or why do you use "UpdateQuantity()".

If you want to extract data from DB, you should put as a value of the option the id of the currency.

I would advice you to make like this, if I understood the question write:

[code]
//file1.php
<?
$q = "SELECT * FROM `currency";
$r = mysql_query ($r);
?>
<form action="file2.php" id="frmCurr" name="frmCurr" method="post">
<select name="Currency" OnChange="UpdateQuantity()">
    <option selected="">GBP</option>
<?
while ($row = mysql_fetch_array($r))
?>
    <option value="<?=$row['id']?>"><?=$row['Cont']?></option>
?>
<?
    <input type="submit" name="submit" value="Submit"/>
</select>
</form>
<?

[/code]

Now when you press the Submit button you do:

[code]
//file2.php
if ($_POST['submit']) {
    $q = "SELECT * FROM currency WHERE id={$_POST['Currency']}";
    $r = mysql_query ($q);
    $currency = mysql_fetch_array($r);
    print $currency['id'] . ' / ' . $currency['Cont'] . ' / '. $currency['Rate'];
}
[/code]

The last code will return all the data for the selected currency.

[fazzfarrell]

Hi Thanks,

It cant be called to another page, there are two funtions on the page one that controls the postage and one that will chage the currency.

I tried something else but cant get it to work code below:

mysql_select_db($database_jag, $jag);
$query_rsCurrency = "SELECT * FROM `currency`";
$rsCurrency = mysql_query($query_rsCurrency, $jag) or die(mysql_error());
$row_rsCurrency = mysql_fetch_assoc($rsCurrency);
$totalRows_rsCurrency = mysql_num_rows($rsCurrency);


<?php
  $sql = "SELECT * FROM currency";
$result = mysql_query($sql);
echo '<form id="frmCurr" name="frmCurr" method="post">
<select name="Currency" OnChange="UpdateQuantity()">';
while ($row = mysql_fetch_assoc($result)) {
echo '<option value="'.$row_rsCurr['Cont'].'">'.$row_rsCurr['Cont'].'</option>';
}
echo '</select></form>';  ?>


I need it change from the updateQuantity, so it calleds in the data from the database. And applies the maths function:

<?php
//Rob Farrell and Leyon Nevett: add up design cost x hrs
            $number1 = $HTTP_SESSION_VARS["icJag"]->col("ItemSubTotal");
$number2 = $row_rsCurrency['Rate'];
            $newnumber = ($number1 * $number2);
echo $english_format_number = number_format($newnumber, 2, '.', '');

?>

[dymon]

[quote]It cant be called to another page, there are two funtions on the page one that controls the postage and one that will chage the currency.[/quote]
Are these two functions JavaScript functions or PHP?
[quote]
mysql_select_db($database_jag, $jag);
$query_rsCurrency = "SELECT * FROM `currency`";
$rsCurrency = mysql_query($query_rsCurrency, $jag) or die(mysql_error());
$row_rsCurrency = mysql_fetch_assoc($rsCurrency);
$totalRows_rsCurrency = mysql_num_rows($rsCurrency);
<?php
              $sql = "SELECT * FROM currency";
$result = mysql_query($sql);
echo '<form id="frmCurr" name="frmCurr" method="post">
<select name="Currency" OnChange="UpdateQuantity()">';
while ($row = mysql_fetch_assoc($result)) {
echo '<option value="'.$row_rsCurr['Cont'].'">'.$row_rsCurr['Cont'].'</option>';
}
echo '</select></form>';  ?>
[/quote]
$row = mysql_fetch_assoc($result) should be $row_rsCurr = mysql_fetch_assoc($result) or you won't get any items in the select component and $row_rsCurr['Cont'] in the value you should put $row_rsCurr['id'] to be easier to extract from database. And what is not working, I don't see any visual errors, can you write what error you get, or the result?

[code]   function UpdateQuantity(value) {
   <?
          $q = "SELECT * FROM currency WHERE id=?>value<?";//I didn't try but theoreticaly it should work
          $r = mysql_query ($q);
          $currency = mysql_fetch_array($r);
   ?>
          alert (<?=currency['Cont']?>);//Or what do you need
   }
[/code]
And in the form you do this : <select name="Currency" OnChange="UpdateQuantity(this.options[this.selectedIndex].value)">

[fazzfarrell]

HI thanks for your help!

I have had to start again hopefully I can explain myself better

[b]The Database:[/b]
mysql_select_db($database_jag, $jag);
$query_rsCurrency = "SELECT * FROM `currency`";
$rsCurrency = mysql_query($query_rsCurrency, $jag) or die(mysql_error());
$row_rsCurrency = mysql_fetch_assoc($rsCurrency);
$totalRows_rsCurrency = mysql_num_rows($rsCurrency);


[b]
The Drop down menu:[/b]
<form id="frmCurr" name="frmCurr" method="post">
<select name="Currency" id="Currency" OnChange="UpdateQuantity()">
<option selected="selected">GBP</option>
<option value="British Pounds">British Pounds</option>
<option value="American Dollars">American Dollars</option>
<option value="Euro">Euro</option>

</select>
</form>

[b]The result[/b]
<?php
//Price x currency to give result
            $number1 = $HTTP_SESSION_VARS["icJag"]->col("ItemSubTotal");
$number2 = $row_rsCurrency['Rate'];
            $newnumber = ($number1 * $number2);
echo $english_format_number = number_format($newnumber, 2, '.', '');

?>



What I am trying to do is:
The user selects there currency
The price is in GBP on the page, when the uaer selects the currency it calculates the result on the page with out going to another page.

The url for the page is http://gtr.spgrouponline.co.uk/results2.php?&Cars=XJ&Year=2007&Lang=English&imageField_x=56&imageField_y=10

As you can see I have a postage area option too this works fine and there are some results on the bottom right where the calculation works (as it is picking up the first option in the database)

Thanks

[HuggieBear]

You my friend, are in need of AJAX.  This will allow you to update the content on the page without reloading it.

I'd also recommend having your select list created dynamically too, this way, you can add additional currencies to the database without having to amend the php pages at all.

I'll try to post an example for you (using your code) when I get home from work this evening.

Regards
Huggie

[fazzfarrell]

Brilliant! Im banging my head against a wall at the mo!

Great stuff, Thanks

[fazzfarrell]

my file!