CODEWITHSUNDEEP
phpmysqlint
lukeshek
lukeshek

Reputation: 978

Can I update an INT value + 1 and return the new value?

Is it possible in MySQL+PHP to increase an INT value and return the new value within one query?

$sql = mysql_query("UPDATE table SET number=number+1 WHERE id='uniqid'");

$updated_number = ???

Or I need to post another query?

SELECT number FROM table WHERE id='uniqid'

Upvotes: 6

Views: 3094

Answers (5)

Ramunas Dronga
Ramunas Dronga

Reputation: 128

There is way without procedure. You can store new value in session variable and get it from next statement.

UPDATE table SET number = (@next_value := number + 1) WHERE id = 'uniqid';

ant then:

SELECT @next_value;

Your session variable is not affected by other increments.

Upvotes: 0

Alnitak
Alnitak

Reputation: 339896

Simple answer - no, it's not possible.

Longer answer, yes, if you use a stored procedure that increments the value for the specified ID, retrieves the new value and returns it.

I've just tested this under MySQL 5.1.59:

CREATE PROCEDURE increment (IN uniqid VARCHAR(255))
BEGIN
   UPDATE `table` SET number = number + 1 WHERE id = uniqid;
   SELECT number FROM `table` WHERE id = uniqid;
END

Usage:

CALL increment(uniqid)

If multiple simultaneous accesses are possible you may wish to LOCK the table first to ensure the operation's atomicity - MySQL apparently doesn't allow stored procedures to lock tables themselves.

Upvotes: 7

MahanGM
MahanGM

Reputation: 2382

@luvPlsQL is right. But basically you need to get the data first, next increase it and then update. At last you got the increased variable before update so you can use it.

Upvotes: -2

jDeveloper
jDeveloper

Reputation: 2126

you can do 

status = mysql_query(mysql,
                     "UPDATE table SET number=number+1 WHERE id='uniqid';\
                      SELECT FROM table WHERE id='uniqid';\
                     ");

and use mysql_next_result() to get the results.

Upvotes: -1

Martin.
Martin.

Reputation: 10539

No. You will need to select that line afterwards

Upvotes: 7

Related Questions