CODEWITHSUNDEEP
phpmysql
Goarge Go
Goarge Go

Reputation: 57

Update data in mysql column field without removing previous value

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'

Upvotes: 0

Views: 3193

Answers (3)

splash58
splash58

Reputation: 26153

You should make changes in your query

  1. Make num = nun+$num to add new value to old one
  2. Remove quotes arount $new because it is a number but not a string
  3. 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");

Upvotes: 2

Goarge Go
Goarge Go

Reputation: 57

$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

Upvotes: 0

georoot
georoot

Reputation: 3617

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

Upvotes: 0

Related Questions