mysql update column then select updated value

I have a table like this

tbl_user

id
user_id
amount

first i want to update a row based on id

$amount = 123; // dyanamic value
$sql = "UPDATE tbl_user SET amount=amount-'$amount' WHERE id='$id' LIMIT 1 ";

now i want to get updated value of amount column i have applied this sql

$sql = "SELECT amount FROM tbl_user  WHERE id='$id'  LIMIT 1 ";

my question is can i combine both of above sql or any single query to achieve above task?

Answer

The best you could imitate is to use two lines of queries, probably using a variable like:

 UPDATE tbl_user SET
     amount = @amount := amount-'$amount'
 WHERE id='$id' LIMIT 1;

 SELECT @amount;

The best you could do then is to create a Stored Procedure like:

 DELIMITER //

 CREATE PROCEDURE `return_amount` ()
 BEGIN
    UPDATE tbl_user SET
     amount = @amount := amount-'$amount'
    WHERE id='$id' LIMIT 1;

    SELECT @amount;
 END //

And then call Stored Procedure in your PHP.

Note: PostgreSQL has this kind of option using RETURNING statement that would look like this:

 UPDATE tbl_user SET amount=amount-'$amount' 
 WHERE id='$id' LIMIT 1
 RETURNING amount

See here