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: http://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);
?>

@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
Check out :stackoverflow.com/questions/1443292/… or try onchane textbox value change in google — jewelhuq

MissesSalima MissesSalima · Accepted Answer · 2015-05-26T12:22:03

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

Auto Calculate MySQL data price based on 1 input field

3 Answers