Adding lists of data to MySQL

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 game categories and the possible number of items (0+) that each user has for each game. I want to set a table for each game with categories -> id and -> items, so I can keep a list of user ids in a table with the items they have for this game.

I can't get it to work, I guess because of the dynamic number of items per user. Is it possible for me to follow my above table schema? Why not / how?

I have tried:

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();
    }

      

+3


source to share


2 answers


There are many ways to do this, but it is very flexible and seems to answer your questions.

-- 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've never had to ask which users have a given item, you can skip the player_item table and put the data (for example, JSON) of your items into a player

blob column of the table .

+1


source


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



0


source







All Articles