mysql update column then select updated value

It is not possible with a single query, but you can combine multiple commands into a script and execute them with a single request to the database server.

Run this script:

"UPDATE tbl_user SET amount=amount-'$amount' WHERE id='".$id."';SELECT amount FROM tbl_user  WHERE id='".$id."'; "

Also, you might want to check whether $id is a number, as I do not see a protection against SQL injection inside your code. SQL injection is a serious threat, you would do better to prepare and protect yourself against it.

We can also use:

UPDATE  tbl_user  SET id = LAST_INSERT_ID(id),  amount = 2.4,user_id=4 WHERE id = 123;

// SELECT
$id =SELECT LAST_INSERT_ID();
SELECT amount,user_id FROM tbl_user  WHERE id = $id   LIMIT 1

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

A function can do this easily. It sounds like you want to limit how many times your code connects to the database. With a stored function or procedure, you are only making one connection. Yes, the stored function has two queries inside it (update then select), but these are executed on the server side without stopping to do round trips to the client.

http://sqlfiddle.com/#!2/0e6a09/1/0

Here's my skeleton of your table:

CREATE TABLE tbl_user (
  id       VARCHAR(100) PRIMARY KEY,
  user_id  VARCHAR(100),
  amount   DECIMAL(17,4) );

INSERT INTO tbl_user VALUES ('1', 'John', '100.00');

And the proposed function:

CREATE FUNCTION incrementAmount
  (p_id VARCHAR(100), p_amount DECIMAL(17,4))
RETURNS DECIMAL(17,4)
BEGIN
  UPDATE tbl_user
SET amount = amount + p_amount
WHERE id = p_id;
  RETURN (SELECT amount FROM tbl_user WHERE id = p_id);
END
//

Then you just run one query, a SELECT on the function you just created:

SELECT incrementAmount('1', 5.00)

The query result is:

105