How to add multiple items in a MYSQL column

Question

I currently have a table with a person's username, password, email, and items (by items I mean like items that you can collect in a game also called an "inventory"). However, how can I add multiple items in the items column? For example if I want to add item A, B, and C in here:

Answer 1

You should use 2 tables to store this data.

users: username | password | email
-------------------------
items: username | item

You can then insert an unlimited number of items for a given username in the second table (each item is a new record).

You can then select all the items for one user like this:

SELECT item FROM items WHERE username = ?

You can also combine the 2 tables in a number of ways.

/* this one selects all users who have a specific item */
SELECT u.* FROM users u JOIN items i ON u.username = i.username WHERE i.item = ?

How do you wish to use the data?

You should also start to use ID values (numbers) so that the you don't have to waste space repeating text based content such as the username.

---

I think this might be a really good way, however can you show me what you mean by each item is a new record? I'm still a bit confused.

You could insert the values like this:

insert into items values ('jonathan', 'hat');
insert into items values ('jonathan', 'drink');
insert into items values ('jonathan', 'mouse');
insert into items values ('user1981730', 'hat');
insert into items values ('user1981730', 'mouse');

Which would result in this:

Then you could query for all users who have drink as an item:

SELECT u.* FROM users u JOIN items i ON u.username = i.username WHERE i.item = 'drink'

Then to fetch a list of all items obtained by user1981730:

SELECT item FROM items WHERE username = 'user1981730'

Once you are happy with this we can talk about adding ID columns.

---

what do you mean by ID columns?

Notice that in the above example the items table has a lot of duplicate information. The username jonathan is repeated 3 times, the item hat is repeated twice. This would be much worse with a real database.

We can save space and generally increase performance by using an integer ID to represent each unique value. Each user would have an ID and so would each unique item.

Let's start with just the users. We must add the new column to the users table.

We make it a PRIMARY KEY which will ensure that all values are unique so that no 2 users can have the same ID. We can also use the AUTO_INCREMENT to have the value automatically increase with each new record.

We would then change our items table so that it made use of the new column instead of username.

Notice how we still have the same information that we did before. User with ID number 1 has a drink, hat and a mouse and the user with ID number 2 has the hat and mouse.

We can still do the same queries as before (with some small changes).

/* this selects all the items for a known user_id */
SELECT item FROM items WHERE user_id = ?

/* this selects all the items for a user when we only know the username */
SELECT i.item FROM items i JOIN users u ON u.user_id = i.item_id WHERE u.username = ?

/* this one selects all users who have a specific item */
SELECT u.* FROM users u JOIN items i ON u.user_id = i.user_id WHERE i.item = ?

So far we have only modified the tables to avoid duplicating the username. If we wanted to also avoid duplicating item names we can add an additional table. This starts to get tricky so I won't go into too much detail (for now) but the table structure would be as follows:

users: username | password | email
-------------------------------
items: item_id | name
-------------------------------
users_items: user_id | item_id

The users table is as before. The items table has a new role and it stores all the unique items. The users_items table now links the other 2 tables.

---

I will end the SO answer here. If you have any further question you can email me with the address listed above (the one used in the example table).

Answer 2

You have several options. Either (if the use case is simple enough), just encode multiple values, e.g., by separating with commas.

However, the clean approach is to use another table, where you create one entry per item and have a foreign key which points to your first table.

Answer 3

Make a row for each item in a separate table.

INSERT INTO items (userid, item) VALUES
(1, 'item number 1'),
(1, 'item number 2'),
(1, 'item number 3');

Don't put all items in one row in your user table. JSON, comma separated values, and other techniques may sound tempting but they aren't real solutions. Your SQL and logic will be much simpler and cleaner if you have one row per item in a table dedicated to keeping track of them.