Iam doing an application. I need to update Prices table on a button click. I have 2 tables, one is "Currency" table and another is "Price" table.The db structure is as follows:
Currency Table
______________
id | currency | rate
_____________________
1 USD 1
2 INR 66.7
3 GBP 0.66264
Price Table
____________
id | Currency | Total | UpdatedPrice
____________________________________
1 USD 300 89898
1 INR 500 500
1 USD 6000 87878787
1 GBP 6777 9887
2 USD 89 4567
2 INR 34 7676
3 USD 878 8889
The scenario is on a button click i need to update the Price table. The latest prices are downloaded to the currency table. There are some formulaes involved. The system should check the currency value from the price table. The formulae is
IF (INR)
Just show the total field
IF USD
total * rate (it should take from the currency table)
IF GBP
rate(INR Rate Currency Table)/GBP Rate (Currency Table)
And the system should update the Price (Updated Price) field. I just wanted to know how to approach this.
Is this query fine?
UPDATE comparitive_st_sup fc
JOIN currency1 fer ON fc.tender_id = $tender_id
SET fc.total_inr = (CASE
WHEN fc.currency = 'INR' THEN fc.total
WHEN fc.currency = 'USD' THEN ((fc.total / fer.rate) * 66.678609)
WHEN fc.currency = 'GBP' THEN ((fc.total / fer.rate) * 66.678609)
END)
To answer on how to APPROACH it you can start with this:
<?php
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
$conn = new mysqli('localhost', 'root', 'mysql', 'db_name');
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
/*
* SELECT ACTIONS FROM ONE TABLE
*/
/*
* CALCULATIONS
*/
/*
* UPDATE ACTIONS TO ANOTHER TABLE
*/
}
?>
<form method="post" action="">
<input type="submit" value="Submit">
</form>