CODEWITHSUNDEEP
phpmysql
d3xt3r
d3xt3r

Reputation: 130

Auto Calculate MySQL data price based on 1 input field

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.

https://i.sstatic.net/q0fWl.png

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:

https://i.sstatic.net/YMgUh.png

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.sstatic.net/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);
?>

Upvotes: 3

Views: 3824

Answers (3)

Mark Leiber
Mark Leiber

Reputation: 3138

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?

Upvotes: 0

MissesSalima
MissesSalima

Reputation: 120

This is the right answer

  1. 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**

Upvotes: 1

Horatio
Horatio

Reputation: 1861

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

Upvotes: 0

Related Questions