how UPDATE mysql row plus one and get the value

Question

I updated with success

    $result = mysql_query("UPDATE $table SET `queue2` = `queue2` + 1 WHERE `id` = '$getid'");

but how can I get the "queue2" value without opening a new request to MySQL

I can simply get the new value with this command

$selresult = mysql_query("SELECT * FROM $table WHERE `id` = '$getid'") or die(mysql_error());

but I'm afraid that the database can get new update again and i will get higher number

Any idea how to do it ?

As stated in [the introduction](http://www.php.net/manual/en/intro.mysql.php) to the PHP manual chapter on the `mysql_*` functions: *This extension is not recommended for writing new code. Instead, either the [mysqli](http://www.php.net/manual/en/book.mysqli.php) or [PDO_MySQL](http://www.php.net/manual/en/ref.pdo-mysql.php) extension should be used. See also the [MySQL API Overview](http://www.php.net/manual/en/mysqlinfo.api.choosing.php) for further help while choosing a MySQL API.* — eggyal, Sep 06 '12 at 13:42

score 0 · Answer 1 · edited May 23 '17 at 12:20

0

See URL:-

PHP + MySQL transactions examples

Try this:-

printf ("Updated records: %d\n", mysql_affected_rows());
mysql_query("COMMIT");

edited May 23 '17 at 12:20

Community

1
1

answered Sep 06 '12 at 13:05

Abid Hussain

7,724
3
35
53

this part worked for me, like i said... i want to get the "queue2 +1" value – Kirma Sep 06 '12 at 13:06

score 0 · Answer 2 · edited May 23 '17 at 12:20

0

You will need to use a transaction between the queries to be certain.

The docs for transactions are here. A good SO question that covers it in detail: PHP + MySQL transactions examples

Edit:

Looking at it from a different angle, why don't you do it in reverse though? It might save the need for a transaction (thought it is possible that you get multiple reads before a write):

Get the value for your queue2 value to display in the page from this:

mysql_query("SELECT * FROM $table WHERE `id` = '$getid'");

You have the true value now, so you can run:

$result = mysql_query("UPDATE $table SET `queue2` = `queue2` + 1 WHERE `id` = '$getid'");

No transaction and you know the value of the data before the update.

edited May 23 '17 at 12:20

Community

1
1

answered Sep 06 '12 at 13:06

Fluffeh

33,228
16
67
80

@Kirma See edit, might solve all your problems without the need for a transaction. – Fluffeh Sep 06 '12 at 13:12
because i'm afraid that 2 people can get the same value.. and when you actually perform +1 and get the value there is no way that people will get the same value – Kirma Sep 06 '12 at 13:20
Then to resume it all: the only way to have a UNIQUE number for each connection/client is using LOCKING (or a MySQL primary key). – Konerak Sep 06 '12 at 13:49

score 0 · Answer 3 · edited Sep 06 '12 at 13:12

0

you can use query to update the value.

mysql_query("UPDATE user_profile SET userpoints = userpoints + 1 WHERE user_id = '".$user_id."'");

edited Sep 06 '12 at 13:12

Fluffeh

33,228
16
67
80

answered Sep 06 '12 at 13:07

jeeva

1,573
2
15
24

He has that bit down pat - he wants to know that no other query has updated the row in the meantime before his second query. – Fluffeh Sep 06 '12 at 13:08

how UPDATE mysql row plus one and get the value

3 Answers3