Update data in mysql column field without removing previous value

Question

I am trying to update "new" column value with new value but problem is my query remove previous data while inserting new value

What is want: here is example table structure,

Table name = agg_lvl primary key set = uid

uid     | new
--------|--------
1       | 100
2       | 300

You can see "new" has 100 points, for example I send 100 new points to user 1, so new column value should be 100 + 100 = 200, right now with this code

$query4 = mysql_query("INSERT INTO agg_lvl (uid, new) VALUES ('$uid','$new')
ON DUPLICATE KEY UPDATE uid='$uid',new='$new'");

Not sure what

new = '$new'

I have tried both ways but no success = >

new = 'new + $new' or new = new + '$new'

2nd: new=new+$new exectly = ON DUPLICATE KEY UPDATE new=new+$new — splash58
– splash58, Commented May 1, 2016 at 8:27
near miss :( so the problem was because of '' i remove it as per your instructions and now able to get my desire results — Goarge Go
– Goarge Go, Commented May 1, 2016 at 9:00
please post an answer, in comments i have no control to accept your answer, i am asking this because your answer is perfect and credit should gone to where it due, so it's due on you — Goarge Go
– Goarge Go, Commented May 1, 2016 at 9:12

splash58 · Accepted Answer · 2016-05-01 09:54:18Z

2

You should make changes in your query

Make num = nun+$num to add new value to old one
Remove quotes arount $new because it is a number but not a string
Remove uid from set list because insert already point to that record

And your query should look so:

$query4 = mysql_query("INSERT INTO agg_lvl (uid, new) VALUES ('$uid','$new')
ON DUPLICATE KEY UPDATE new=new+$new");

answered May 1, 2016 at 9:54

splash58

26.2k3 gold badges25 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

Goarge Go Over a year ago

one new and strange thing happened

splash58 Over a year ago

could you a little bit more?

Goarge Go Over a year ago

it's working Perfect, one new and strange thing happened, the problem is since "id" i set Auto Increment and primary key, so when this query fire, it inserting new row every-time, the reason is " ON DUPLICATE KEY UPDATE " not working, because of " KEY " case, do you have any idea about how to deal with should i need to assign unique name to column " date " so we able to update row if date is same, if yes than how ? so we have total 4 columns in db , ( id, uid, new, date )

splash58 Over a year ago

what is uid in this case?

Goarge Go Over a year ago

uid is coming from form posting

|

georoot · Accepted Answer · 2016-05-01 08:42:34Z

0

Okay first i will answer with the proper way to do the same, In this case i am assuming that UID is unique, so you make a new table scorecard with UID as foreign key. Now rather than update, you just insert stuff to table like if UID 1 gains 10 and 20 points, there are two entries. onw with 10 and one with 20. Now to get his current points, you add all points where UID=1 .

Now in your implementation the correct query would be

UPDATE userData SET points = points + x WHERE UID = $uid

where x is the new points gained and points is the name of column

edited May 1, 2016 at 8:42

answered May 1, 2016 at 8:27

georoot

3,6171 gold badge34 silver badges61 bronze badges

5 Comments

georoot Over a year ago

@Rahul thanks for the catch, happened because i added the where statement in an edit and forgot to check the syntax again :)

Goarge Go Over a year ago

2nd: new=new+$new exectly = ON DUPLICATE KEY UPDATE new=new+$new – splash58, this answer is working for me now

splash58 Over a year ago

@Goarge Go and why have you accepted not the answer that give you the result ? :) Accept your one

Goarge Go Over a year ago

because the guy who answer send comments he didn't posted answer, i guess he is new here

Goarge Go Over a year ago

when i try to accept my answer it says " accept your own answer in next 2 days ?

Goarge Go · Accepted Answer · 2016-05-01 09:09:46Z

0

$query4 = mysql_query("INSERT INTO agg_lvl (uid, new) VALUES ('$uid','$new') ON DUPLICATE KEY UPDATE uid='$uid',new=new+$new");

worked for me with help of @splash58

edited May 1, 2016 at 9:09

answered May 1, 2016 at 9:01

Goarge Go

571 silver badge8 bronze badges

Collectives™ on Stack Overflow

Update data in mysql column field without removing previous value

3 Answers 3

15 Comments

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

15 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related