Auto Calculate MySQL data price based on 1 input field

Question

How can I change all prices 1 time instead of one by one? I want to just type a number in a field, and each price will be calculated based on my formula and updated.

So I have a database, in there, there are some prices. I already have a php page, where I can edit the prices one by one.

So the name and price values are loading 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 press edit, it will go to a another php page where I can change the price value and save it:

But I just want to type 1 price in an input field, and that value will be auto calculated by * or - and show that as results. So if I type a total price, let say: 2000 then I want each price automatically to change and then I can press Update.

So that I can give each name a formula like: 2000-500 = SHOW THIS VALUE And that I only put some numbers in the total field, all the prices will be calculated automatically and I don't have to change all prices one by one.

Like this: https://i.stack.imgur.com/yM40T.png enter image description here

The Edit 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);
?>

It have to be just Like this: http://i.stack.imgur.com/yM40T.png — d3xt3r, May 23 '15 at 23:38
@jewelhuq Thanks, i was already thinking that.. Where can i find a demo or example like how i want it? I searched 3 days but no results :( — d3xt3r, May 23 '15 at 23:42
Check out :http://stackoverflow.com/questions/1443292/how-to-implement-onchange-of-input-type-text-with-jquery or try onchane textbox value change in google — jewelhuq, May 23 '15 at 23:47
Thanks but still i have a another problem if i edit one price, i just see 1 name and price not everything! — d3xt3r, May 23 '15 at 23:49
Can you show the code you use to handle the single item edit? — Mark Leiber, May 23 '15 at 23:56
@MarkLeiber Yess offcourse, i just edited the post! Thank you very much! — d3xt3r, May 24 '15 at 00:11
@MarkLeiber I'm Sorry i just added it, i need something simple, i don't have to seee al the prices -> If i have just 1 input with a button where i can type EG: 3000 and press Update , that all the prices will changed in the database based on the math for each ID . — d3xt3r, May 24 '15 at 00:27
Where `2000/10 = 200, 2000*2=4000, 2000-500 = 1500` come from? — Hotdin Gurning, May 24 '15 at 01:51
@RubahMalam So if i type 2000 in this field: http://i.stack.imgur.com/yM40T.png Then i want that that value so: 2000 will be calculated for each Database ID.. So that i can set it like: For ID=2 math is: 2000*2 = 4000<-- And save the results in the Database — d3xt3r, May 24 '15 at 02:08
@d3xt3r, how complicated do the formulas need to be? Is it sufficient if they are just "enteredValue operator x" (examples: 2000 * 2, 2000 - 500, 2000 / 2) or will the math be more sophisticated than that? — Mark Leiber, May 26 '15 at 13:52

score 1 · Answer 1 · answered May 26 '15 at 12:22

This is the right answer

Create a new .php file, and put this in 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) { // Calculation 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').click(function(){ var value = $('#inputField').val();

        if (value === '') {
            alert("Please input a value");  
            return false;
        }
    })
})

***** Stackoverflow does a little weird if i paste the whole code in code tags**

score 0 · Answer 2 · answered May 26 '15 at 03:57

Okay, 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
?>

I hope this answers your question. Let me know, if you need a further explanation

score 0 · Answer 3 · edited May 23 '17 at 11:43

You can do this in a single UPDATE.

Here are the assumptions I'm making about the formulas:

they can be tokenized by spaces
they have three tokens, e.g. X(space)operator(space)Y
they always have the operator as the middle token

Here is the set up that I'm using to mimic 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 is a SELECT statement to show you what the UPDATE will be doing:

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

The columns part1, part2, and part3 illustrate how the formula is tokenized. 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 CASE statement, the second token (the operator) is evaluated, and based on that the math is executed as: part1 (operator) part3. You could add the modulo operator if you want as another case, but I figured it was unlikely to be used in your formulas.

So the UPDATE statement would 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 don't have a WHERE clause as I'm assuming that you are displaying and updating all records. If this isn't the case you'll need to provide the appropriate WHERE clause to limit the records that are updated.

One more important note. You should really be using prepared statements and parameterized queries to prevent SQL injection. In the above UPDATE statement, 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 it here: How can I prevent SQL injection in PHP?

Auto Calculate MySQL data price based on 1 input field

3 Answers3