Adding lists of data to MySQL

Question

I have some data to add to my database, I'm not sure what my table schema should be. I have an id number for each specific user, 4 categories of games, and a possible number of items (0+) each user has for each game. I want to set a table for each game with the categories ->id and ->items, so I can save the list of user id's in the table, with the items they have for that game.

I can't seem to get it to work, I think because of the dynamic number of items for each user. Is it possible for me to achieve my above mentioned table schema? Why not/how?

I have been trying:

foreach ($json->rgDescriptions as $mydata)
        {    
         $sql = $dbh->prepare('INSERT INTO user_items_tf2 (items) VALUES (:item) WHERE steam_id = :steamid');
         $sql->bindParam(':item', $mydata->name);
         $sql->bindParam(':steamid', $steamprofile['steamid']);
         $sql->execute();
    }

Why not have one table for users, one for games, one for items, a table that links users to games and a table that links userGames to items. That way it's all one to one relationships and is nice and simple — scrowler
– scrowler, Commented May 4, 2015 at 5:59
@scrowler I currently have a table for users, and 4 tables for the 4 games which hold all the possible items for each game. I don't fully understand what you mean. I'm not sure how to make each user have a dynamic list of items. — Mitch8910
– Mitch8910, Commented May 4, 2015 at 6:41
@Uchiha Because of the WHERE part? That still doesn't fix my actual problem though. — Mitch8910
– Mitch8910, Commented May 4, 2015 at 6:42
Please have a look over these links insert and update. Try to learn the differences between them — Narendrasingh Sisodia
– Narendrasingh Sisodia, Commented May 4, 2015 at 6:46

buckaroo1177125 · Accepted Answer · 2015-05-05 03:30:19Z

There are numbers of ways to do this but one which is very flexible and seems to answer your questions would be this.

-- Players
CREATE TABLE player
    (`id` int primary key auto_increment, `name` varchar(255))
;

-- Games
CREATE TABLE game
    (`id` int primary key auto_increment, `name` varchar(255))
;

-- Items and what game they belong to
CREATE TABLE item
    (`id` int primary key auto_increment, `game_id` int, `name` varchar(255))
;

-- What games players are playing
CREATE TABLE player_game
    (`player_id` int, `game_id` int)
;

-- What items players have
CREATE TABLE player_item
    (`player_id` int, `item_id` int, index(`player_id`))
;

If you never needed to ask the question which users had a given item you could skip the player_item table and stuff the data (as JSON for instance) of their items into a column of the player table with a blob type.

al3x2508 · Accepted Answer · 2015-05-04 13:23:23Z

0

$sql = $dbh->prepare('INSERT INTO user_items_tf2 (items, steam_id) VALUES (:item, :steamid)');

answered May 4, 2015 at 13:23

al3x2508

234 bronze badges

Collectives™ on Stack Overflow

Adding lists of data to MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related