我的行的堆栈值而不是多个

Question

So I've got a table that looks like this:

username - item - amount
user1 - sword - 1
user1 - sword - 1
user1 - sword - 1
user2 - sword - 1
user2 - sword - 1

How do I make it so that when I add an item to one of the users, they stack like this instead:

username - item - amount
user1 - sword - 3
user2 - sword - 2

the code for adding item:

$sql = "insert into items (username, item, amount) values ('".$_SESSION['user_name']."', 'sword', '1')";

Answer 1

you need to create unique index for username and item column and use on duplicate key update so if same pairs for username and item occurs sum existing amount value with new one

create unique index items_username_item on items(username, item);

insert into items (username, item, amount) 
values ('".$_SESSION['user_name']."', 'sword', '1')
on duplicate key update amount = amount + values(amount);