mySQL column to hold array

Question

I'm a beginner concerning coding and especially SQL and PHP. I deal with app. 120 users. The users can acquire app. 300 different collectible items. When a user acquires a specific item, I would like the ID number of that particular item to be stored in the row of the user who acquired it, so that there is some information about what items the user already has (and to avoid duplicate items in his possession). Is there a good way to store such information? Is it even possible to set a column type to array and store it there?

Please note: I'm not lazy and I've been digging around and searching for the answer for 2 hours. I couldn't find a solution. I know of the rule that one should insert only one piece of information into one cell.

ohmu · Accepted Answer · 2014-11-20 18:15:51Z

MySQL does not support storing arrays. However, you can use a second table to emulate an array by storing the relation between the users and items. Say you have the table users:

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    ...
);

And you have a table defining items:

CREATE TABLE items (
    item_id SERIAL PRIMARY KEY,
    ...
);

You can relate what items a user has using a table similar to user_items:

CREATE TABLE user_items (
    id SERIAL PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    item_id BIGINT UNSIGNED NOT NULL,
    ...,
    FOREIGN KEY (user_id)
        REFERENCES users (user_id),
    FOREIGN KEY (item_id)
        REFERENCES items (item_id)
);

Then, to determine what items user 123 has acquired, you could use JOINs similar to:

SELECT items.*
FROM users
INNER JOIN user_items
    ON user_items.user_id = users.user_id
INNER JOIN items
    ON items.item_id = user_items.item_id
WHERE users.user_id = 123; -- Or some other condition.

Michael · Accepted Answer · 2014-11-11 19:19:38Z

I assume you have 2 tables for example, users and items. To control which user already has a specific item, i would create an associative table, including the UserID from users and ItemID from items. This way you can now check in your user_items table if the user already has this item.

Here is a small example:
users (UserID is PK):
+--------+----------+ | UserID | UserName | +--------+----------+ | 1 | Fred | | 2 | Joe | +--------+----------+
items (ItemID is PK):
+---------+----------+ | ItemID | ItemName | +---------+----------+ | 5 | Book | | 6 | Computer | +---------+----------+
user_items (ItemID referencing items.ItemID, UserID referencing users.UserID):
+---------+--------+ | ItemID | UserID | +---------+--------+ | 5 | 1 | | 6 | 2 | +---------+--------+

Collectives™ on Stack Overflow

mySQL column to hold array

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related