Page 1 of 1

help with mysql query

Posted: Sun Aug 14, 2011 3:22 pm
by casey90
hey,

i have this query

Code: Select all

$query = "UPDATE `currency` SET `eur` = '$allcur', `eurrate` = '$newrate', `sek` = '$newsek' WHERE `id`='1'";
but the problem is that the user is going to select wich item to update in the database so the table name is fixed but the fields with be variables and i want it to be like this

Code: Select all

$query = "UPDATE `currency` SET `$currency` = '$allcur', `$newrate` = '$newrate', `$sek` = '$newsek' WHERE `id`='1'";
but it is not updating the database if i write the second code with variables so any idea how to update the db with the variables??

thanks

Re: help with mysql query

Posted: Sun Aug 14, 2011 3:58 pm
by oscardog
You can't just 'whip up' columns that don't exist. And generating potentially hundreds or even thousands of columns, to allow them to use whatever currency they like, is a bad idea. I would recommend you rethink your approach and instead of having columns use a single column to describe each one.

For example, have a table with currency_type, currency, newrate_type, newrate, sek_type, sek and then fill those fields in where appropriate. So if they entered...

Code: Select all

$currencyType = 'GBP';
$currency = 100;
$newRateType = 'VAT';
$newRate =  '20%';
$sekType = 'Whatever';
$sek = 44;
You would use the following query:

Code: Select all

$query = "UPDATE `currency` SET currency_type = '$currentyType', currency = '$currency' WHERE `id`='1'";
And so forth. I hope that makes sense.

Re: help with mysql query

Posted: Sun Aug 14, 2011 4:20 pm
by casey90
hi, you didnt get what i mean

the software which im doing is going to be run at local server and the currency option is not a field but it is a dropdown menu so for example if it had usd, eur, nok then if i select nok then i want the nok coloum to be updated not a nother one so it should be like

Code: Select all

$currency = "nok";  the user selected that currency
so the update will be like that

Code: Select all

$query = "UPDATE `currency` SET `$currency` = '$allcur', `$newrate` = '$newrate', `$sek` = '$newsek' WHERE `id`='1'";
and in this case it means

Code: Select all

$query = "UPDATE `currency` SET `nok` = '$allcur', `$newrate` = '$newrate', `$sek` = '$newsek' WHERE `id`='1'";