CODEWITHSUNDEEP
phpmysqldatabasesql-updateinsert-update
user1722138
user1722138

Reputation:

How to add to an existing value in the database

I'm back with another absolute beginner question, I have values stored in a database, my goal is to allow users to either subtract or add to a specific database value (quantity) using a drop down menu with values from ranging from 1 - 10. I'm keeping it simple for now.

mysql_select_db("toner", $con);
$sql=INSERT INTO inventory (partnumber, quantity)
VALUES
('$_POST[partnumber]','$_POST[quantity]');
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
mysql_close($con);

I can find plenty of examples just adding or deleting a static value however that's not what I need. I've searched around and few examples resembling what I'm looking for 1 & 2 It seems as though UPDATE would be the best solution however I'm not sure exactly how it should be implemented, I've also looking into concatenation but I'm not sure it can accomplish what I need, any assistance you can offer this novice is appreciated. Thanks

Upvotes: 0

Views: 2500

Answers (2)

Chris
Chris

Reputation: 5605

To get the following solution to work you will need to insert a value into the database for instance partnumber = 1 and qantity = 0. You will definatly need to modify this to work with your mysql connection string and will need to look in the database after to check that it has worked or add a select query. However this will update the inventory item 1's quantity with that selected in the dropdown...

file.php

<?php
    include("db_connection_file.php"); // in here are the username and password etc for your mysql connection
    if($_POST){ // the code in this block will ONLY fire if the form has been posted
        $dropdown_contents = $_POST['drop']; // this is the value of the form item 'drop' that has been posted...
        $part_no_from_form = $_POST['part_no'];
        $query ="UPDATE inventory SET quantity = '".$dropdown_contents."' WHERE partnumber = '".$part_no_from_form ."'"; // this updates the database and sets the quantity to that posted where the part number is that in the form
        $testResult = mysql_query($query) or die('Error, query failed'); 
        echo "This worked";
    }
?>
<form acion="file.php" method="post">
    Quantity <select name="drop" id="drop">
        <option>1</option>
        <option>2</option>
        <option>3</option>
        <option>4</option>
        <option>5</option>
        <option>6</option>
        <option>7</option>
        <option>8</option>
        <option>9</option>
        <option>10</option>
    </select><br />
    Part No. <select name="part_no" id="part_no">
        <option>1</option>
        <option>2</option>
        <option>3</option>
        <option>4</option>
        <option>5</option>
        <option>6</option>
        <option>7</option>
        <option>8</option>
        <option>9</option>
        <option>10</option>
    </select><br />
    <input type="submit" value="go" />
</form>

Note: This can easily be extended to specify which partnumber you want to UPDATE in the database by adding say another dropdown or inputbox, catching the value posted and passing it through the where part of the Update query.

THIS HAS NO VALIDATION WHATSO EVER! It is also using depreciated MySQL connection functions, please look up MySQLi and PDO

Upvotes: 2

Matt Clark
Matt Clark

Reputation: 28589

To update a row to a new KNOWN value:

UPDATE inventory SET quantity = $KnownValue WHERE partnumber = $PartNumber;

To update a row adding to an existing value, i.e. an additional 5 units were added to inventory:

UPDATE inventory SET quantity = quantity + $IncramentalValue WHERE partnumber = $PartNumber;

Upvotes: 0

Related Questions