Adjusting values in MySQL based on current value

My database is for keeping track of user’s balances, and there are certain actions that will add or remove balance from the users. Currently I am doing this to get the balance and update it

$conn->prepare("SELECT * FROM users WHERE userid=:uid")
$conn->bindValue(':uid', $data['id']
$conn-execute()
$currentBal = $conn->fetch()
$newBal = $currentBal['balance'] + 100
$conn->prepare("UPDATE users SET balance=:bal WHERE userid=:uid")
$conn->bindValue(':bal', $newBal)
$conn->bindValue(':uid', $data['id']

Is there a way to directly do math within the query to add or remove? It is an integer.

Answer

Just do arithmetic:

UPDATE users
   SET balance = balance + :inc
    WHERE userid = :uid;

You can hardcode the 100, but I think it is better to pass it in as a parameter.

Advertisement

Answer