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();
}
source to share
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 .
source to share