Automatic calculation of MySQL data price based on 1 input field
How can I change all prices 1 time, and not one by one? I just want to enter a number in the box and each price will be calculated based on my formula and updated.
So I have a database, there are some prices there. I already have a php page where I can edit prices one by one.
So the name and price values are loaded from my database. This is the PHP file:
<?php
$objConnect = mysql_connect("localhost","***","***") or die("Error Connect to Database");
$objDB = mysql_select_db("***");
$strSQL = "SELECT * FROM orders";
$objQuery = mysql_query($strSQL) or die ("Error Query [".$strSQL."]");
?>
<table width="562" border="1">
<tr>
<th width="201"> <div align="center">Name</div></th>
<th width="213"> <div align="center">Price</div></th>
<th width="126"> <div align="center">Edit </div></th>
</tr>
<?php
while($objResult = mysql_fetch_array($objQuery))
{
?>
<tr>
<td><?php echo $objResult["gehalte"];?></td>
<td><?php echo $objResult["prijs"];?></td>
<td align="center"><a href="prijsedit.php?id=<?php echo $objResult["id"];?>">Edit</a></td>
</tr>
<?php
}
?>
</table>
<p>Total price:
<label for="textfield">:</label>
<input type="text" name="textfield" id="textfield">
<a href="#">Update</a>
</p>
<?php
mysql_close($objConnect);
?>
If I click on the edit button, it goes to another php page where I can change the price value and save it:
But I just want to print 1 price in the input field and that value will be automatically calculated with * or - and show that as results. So if I print the total price, say: 2000, then I want each price to automatically change, and then I can click Update.
So, I can give each name the formula: 2000-500 = SHOW THIS COST And that I just put some numbers in the full field, all prices will be calculated automatically and I don't have to change all prices one by one.
Like this: http://i.stack.imgur.com/yM40T.png
Change PHP page:
<html>
<head>
</head>
<body>
<form action="save.php?id=<?php echo $_GET["id"];?>" name="frmEdit" method="post">
<?php
$objConnect = mysql_connect("localhost","*","*") or die("Error Connect to Database");
$objDB = mysql_select_db("*");
$strSQL = "SELECT * FROM orders WHERE id = '".$_GET["id"]."' ";
$objQuery = mysql_query($strSQL);
$objResult = mysql_fetch_array($objQuery);
if(!$objResult)
{
echo "Not found CustomerID=".$_GET["id"];
}
else
{
?>
<table width="540" border="1">
<tr>
<th width="161"> <div align="center">CustomerID </div></th>
<th width="203"> <div align="center">Name</div></th>
<th width="154"> <div align="center">Price</div></th>
</tr>
<tr>
<td><div align="center"><input type="text" name="txtCustomerID" size="5" value="<?php echo $objResult["id"];?>"></div></td>
<td><input type="text" name="txtName" size="20" value="<?php echo $objResult["gehalte"];?>"></td>
<td><input type="text" name="txtEmail" size="20" value="<?php echo $objResult["prijs"];?>"></td>
</tr>
</table>
<input type="submit" name="submit" value="submit">
<?php
}
mysql_close($objConnect);
?>
</form>
</body>
</html>
And save PHP:
$strSQL = "UPDATE orders SET ";
$strSQL .="id = '".$_POST["txtCustomerID"]."' ";
$strSQL .=",gehalte = '".$_POST["txtName"]."' ";
$strSQL .=",prijs = '".$_POST["txtEmail"]."' ";
$strSQL .="WHERE id = '".$_GET["id"]."' ";
$objQuery = mysql_query($strSQL);
if($objQuery)
{
echo "Save Done.";
header('Location: edit.php');
}
else
{
echo "Error Save [".$strSQL."]";
}
mysql_close($objConnect);
?>
source to share
This is the correct answer
-
Create a new .php file and put it there:
$objConnect = mysql_connect("localhost","***","***") or die("Error Connect to Database"); $objDB = mysql_select_db("***"); $strSQL = "SELECT id FROM orders"; $objQuery = mysql_query($strSQL) or die ("Error Query [".$strSQL."]"); $value = isset ($_POST['inputField']) ? $_POST['inputField'] : ''; if (!empty($value)) { while ($row = mysql_fetch_array($objQuery)) { $price = calculate_price ($row['id'], $value); $strSQL = "UPDATE orders SET prijs =". $price. " WHERE id = ".$row['id']. ""; $result = mysql_query($strSQL) or die ("Error Query [".$strSQL."]"); } echo "<script>alert('Update Successful!');</script>"; }
function calculate_price ($ id, $ value) {$ price = 0; switch ($ id) {// Calculate here for id 0 case 0: $ price = $ value / 10; break; // Calculation here for id 1 Case 1: $ price = $ value / 10; break; // Calculation here for id 2
case 2: $ price = $ value * 2; break; // Calculation here for id 3 case 3: $ price = $ value + 4; break; default: break; } return $ price; }
$ (document) .ready (function () {$ ('# Button'). Press (function () {var value = $ ('# inputField'). val ();if (value === '') { alert("Please input a value"); return false; } }) })
***** Stackoverflow is a little weird if I paste all the code in code tags **
source to share
Ok, so I would recommend using multiple SQL statements.
<?php
//declare update value to variable x
//SQL Statement 1: select first value from database with ID2
//do math for first value using x and set it to new variable y
//SQL Statement 2: Update database with new value y for ID1
//Do the above for each value
?>
Hope this answers your question. Let me know if you need further explanation
source to share
You can do it in one UPDATE
.
Here are the assumptions I make about formulas:
- they can be indicated by spaces
- they have three tokens, for example. X (space) operator (space) Y
- they always have an operator as an average token
Here's the setup I'm using to simulate your data (obviously you don't need to run this part):
CREATE TABLE orders (
id numeric,
gehalte varchar(20),
formula varchar(20),
prijs numeric
);
INSERT INTO orders VALUES (1, '8karaat', 'n / 10', 80);
INSERT INTO orders VALUES (2, '14karaat', 'n - 500', 150);
INSERT INTO orders VALUES (3, '18 karaat', 'n * 2', 200);
Here's a guide SELECT
to show you what it will do UPDATE
:
SELECT id
, gehalte
, formula
, prijs AS prijs_original
, SUBSTRING_INDEX(formula, ' ', 1) AS part1
, SUBSTRING_INDEX(SUBSTRING_INDEX(formula, ' ', 2),' ',-1) AS part2
, SUBSTRING_INDEX(SUBSTRING_INDEX(formula,' ',-1),' ',1) AS part3
, CASE SUBSTRING_INDEX(SUBSTRING_INDEX(formula, ' ', 2),' ',-1)
WHEN '+' THEN
SUBSTRING_INDEX(REPLACE(formula,'n',2000), ' ', 1) + SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(formula,'n',2000),' ',-1),' ',1)
WHEN '-' THEN
SUBSTRING_INDEX(REPLACE(formula,'n',2000), ' ', 1) - SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(formula,'n',2000),' ',-1),' ',1)
WHEN '/' THEN
SUBSTRING_INDEX(REPLACE(formula,'n',2000), ' ', 1) / SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(formula,'n',2000),' ',-1),' ',1)
WHEN '*' THEN
SUBSTRING_INDEX(REPLACE(formula,'n',2000), ' ', 1) * SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(formula,'n',2000),' ',-1),' ',1)
END AS prijs_calculated
FROM orders;
Results:
id gehalte formula prijs part1 part2 part3 prijs_calculated
1 8karaat n / 10 80 n / 10 200
2 14karaat n - 500 150 n - 500 1500
3 18 karaat n * 2 200 n * 2 4000
SQL Fiddle: http://sqlfiddle.com/#!9/8816c/1
Columns part1, part2, and part3 illustrate how the formula is indicated. For example, if the formula is "n / 10", the first token is "n", the second token is "/", and the third token is "10". In the instruction CASE
, the second token (operator) is calculated and based on this the math is performed as: part1 (operator) part3. You can add the modulo operator if you like, like a different case, but I figured it is unlikely to be used in your formulas.
So, the operator UPDATE
will look like this:
UPDATE orders
SET prijs = CASE SUBSTRING_INDEX(SUBSTRING_INDEX(formula, ' ', 2),' ',-1)
WHEN '+' THEN
SUBSTRING_INDEX(REPLACE(formula,'n',2000), ' ', 1) + SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(formula,'n',2000),' ',-1),' ',1)
WHEN '-' THEN
SUBSTRING_INDEX(REPLACE(formula,'n',2000), ' ', 1) - SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(formula,'n',2000),' ',-1),' ',1)
WHEN '/' THEN
SUBSTRING_INDEX(REPLACE(formula,'n',2000), ' ', 1) / SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(formula,'n',2000),' ',-1),' ',1)
WHEN '*' THEN
SUBSTRING_INDEX(REPLACE(formula,'n',2000), ' ', 1) * SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(formula,'n',2000),' ',-1),' ',1)
END;
SQLFiddle: http://sqlfiddle.com/#!9/2a962/1
Note that I have no suggestion WHERE
as I am assuming you are showing and updating all entries. If this is not the case, you need to provide an appropriate proposal WHERE
to restrict updated entries.
One more important note. You should really use prepared statements and parameterized queries to prevent SQL injection. In the above expression, the UPDATE
value 2000 is the value from your form post. Instead of using, $_POST["theFormField"]
you should use a parameter. There is a great answer demonstrating how to do this here:
How to prevent SQL injection in PHP?
source to share