Execute OPERATION (plus, minus, multiply, divide) in Update SQL

How to execute plus, minus, multiply or divide operation in SQL to the selected table and row?

Below is example code where I manually minus original quantity to create a new quantity and update into the selected row:

$idArr = $_POST['checkboxId'];

foreach($idArr as $index=>$value)
{开发者_运维百科
    $id = mysql_real_escape_string($value);

    // Get Quantity from this item id
    $sql = "SELECT quantity FROM items WHERE item_id = '$id'";
    $result = mysql_query($sql);
    $quantity = $row['quantity'];

    // New quantity after minus by 1
    $new_quantity = $row['quantity'] - 1;

    // Update new quantity to this item
    $sql = "UPDATE items SET quantity = '$new_quantity' WHERE item_id = '$id'";
    $result = mysql_query($sql);
}

Is it a practical way to update changes of quantity(integer) in preferred rows? Can I do that with a single update query?

Why not just put the operation in the SQL update query ?

For example, you could have a query such as this one :

UPDATE items SET quantity = quantity - 1 WHERE item_id = '$id'

Or :

UPDATE items SET quantity = quantity + 1 WHERE item_id = '$id'

Great advantage : this is done in a single SQL query (no select, and, then, update) ; which means there will be no problem if two users try to do this at the exact same time : SQL will deal with concurrency, and do one query after the other.

With your initial solution, you could have (if you're pretty unlucky -- but this happens) :

• first user does a select, gets 5 as quantity
• second user does a select, gets 5 as quantity
• first user updates to 4
• second user updates to 4 too... but, there, it should have been updated to 3 !

$sql = "UPDATE items SET quantity = quantity - 1 WHERE item_id = '$id'";

You can also use +, * and /

http://dev.mysql.com/doc/refman/5.0/en/arithmetic-functions.html

You can use:

$sql = "UPDATE items SET quantity = quantity - 1 WHERE item_id = '$id'";

This has an additional advantage over your code. When using select/update there is a chance that something else modifies quantity between the select and the update. With your code, this modification could be lost since it will be overwritten by the update query. Using a single query guarantees that this cannot happen.

$sql = "UPDATE items SET quantity = quantity - 1 WHERE item_id = '$id'";

UPDATE items SET Quantity = Quantity - 1 WHERE item_id = ...