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?
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 
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 Here would be the procedure
CREATE PROCEDURE UpdateAndSelect ( @amount MONEY, @id INT ) AS BEGIN UPDATE tbl_user SET amount = @amount WHERE id = @id LIMIT 1 SELECT amount FROM tbl_user WHERE id = @id LIMIT 1 END GO You would call this stored procedure by setting your variables (@amoutn and @id) and then calling:
exec UpdateAndSelect Hope this helps solve your problem
