Multidimensional Key/Value Array into MySQL Table

Question

Alright, so I've never done this before. I am not a professional, but I know roughly how MySQL works. I know my way around PHP slightly better but still beginner stages. I also currently have a method in mind to go about it.

However, before I waste 2+ hours learning what I did wrong, I thought I'd ask here first how you guys would do it. Im starting with a (relatively) small array and working to big stuff when I get this understood well.

Array: http://pastebin.com/g8RyxtP5

So.. what would you do to get this into MySQL? Right now my initial thought is to make the table and its looking like this right now:

<?php
include "login.php";
include "createdb.php";

$con = mysqli_connect($mysql_host, $mysql_user, $mysql_pass, $mysql_table)
or die("Some error occurred during connection " . mysqli_error($con));

/*$sql = "CREATE TABLE Career (
ID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
Username varchar(32),
Password varchar(32),
Battletag varchar(32),
Region varchar(2)
)
";*/

$sql = "CREATE TABLE career (
ID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
battletag varchar(32),
lastHeroPlayed varchar(32),
lastUpdated varchar(32),
monsters varchar(32),
elites varchar(32),
hardcoreMonsters varchar(32),
barbarian varchar(32),
crusader varchar(32),
demon-hunter varchar(32),
monk varchar(32),
witch-doctor varchar(32),
wizard varchar(32),
paragonLevel varchar(32),
paragonLevelHardcore varchar(32),

h1name varchar(32),
h1id varchar(32),
h1level varchar(32),
h1hardcore varchar(32),
h1gender varchar(32),
h1dead varchar(32),
h1class varchar(32),
h1last-updated varchar(32),

h2name varchar(32),
h2id varchar(32),
h2level varchar(32),
h2hardcore varchar(32),
h2gender varchar(32),
h2dead varchar(32),
h2class varchar(32),
h2last-updated varchar(32),

h3name varchar(32),
h3id varchar(32),
h3level varchar(32),
h3hardcore varchar(32),
h3gender varchar(32),
h3dead varchar(32),
h3class varchar(32),
h3last-updated varchar(32),

h4name varchar(32),
h4id varchar(32),
h4level varchar(32),
h4hardcore varchar(32),
h4gender varchar(32),
h4dead varchar(32),
h4class varchar(32),
h4last-updated varchar(32),

h5name varchar(32),
h5id varchar(32),
h5level varchar(32),
h5hardcore varchar(32),
h5gender varchar(32),
h5dead varchar(32),
h5class varchar(32),
h5last-updated varchar(32),

h6name varchar(32),
h6id varchar(32),
h6level varchar(32),
h6hardcore varchar(32),
h6gender varchar(32),
h6dead varchar(32),
h6class varchar(32),
h6last-updated varchar(32),

h7name varchar(32),
h7id varchar(32),
h7level varchar(32),
h7hardcore varchar(32),
h7gender varchar(32),
h7dead varchar(32),
h7class varchar(32),
h7last-updated varchar(32),

h8name varchar(32),
h8id varchar(32),
h8level varchar(32),
h8hardcore varchar(32),
h8gender varchar(32),
h8dead varchar(32),
h8class varchar(32),
h8last-updated varchar(32),

h9name varchar(32),
h9id varchar(32),
h9level varchar(32),
h9hardcore varchar(32),
h9gender varchar(32),
h9dead varchar(32),
h9class varchar(32),
h9last-updated varchar(32),

h10name varchar(32),
h10id varchar(32),
h10level varchar(32),
h10hardcore varchar(32),
h10gender varchar(32),
h10dead varchar(32),
h10class varchar(32),
h10last-updated varchar(32),

h11name varchar(32),
h11id varchar(32),
h11level varchar(32),
h11hardcore varchar(32),
h11gender varchar(32),
h11dead varchar(32),
h11class varchar(32),
h11last-updated varchar(32),

h12name varchar(32),
h12id varchar(32),
h12level varchar(32),
h12hardcore varchar(32),
h12gender varchar(32),
h12dead varchar(32),
h12class varchar(32),
h12last-updated varchar(32),
";

if (mysqli_query($con,$sql)) {
   echo "TABLE 'Career' created successfully!<br>";
} else {
   echo "Error creating table: " . mysqli_error($con)."<br>";
}


mysqli_query($con, $sql);

After the table is created I was going to do some PHP/MySQL magic (basically learning as I go) to get the array->parse->push to mysql ?

Thanks for any insight. What should I not be doing/how should I be going about this?

Edit: Okay I think maybe i can make one table still, and include the defining career var "battletag" into each hero separately (along with any vital info i need for each hero). I think that would work better than what I had.

$sql = "CREATE TABLE heros (
ID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
battletag varchar(64) ,
name varchar(32),
id int,
level int,
hardcore bool,
gender bool,
dead bool,
class varchar(32),
last-updated TIMESTAMP
";

$sql2 = "CREATE TABLE career (
ID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
battletag varchar(64),
lastHeroPlayed varchar(32),
lastUpdated TIMESTAMP,
monsters int,
elites int,
hardcoreMonsters int,
barbarian DOUBLE(4,3),
crusader DOUBLE(4,3),
demon-hunter DOUBLE(4,3),
monk DOUBLE(4,3),
witch-doctor DOUBLE(4,3),
wizard DOUBLE(4,3),
paragonLevel int,
paragonLevelHardcore int
";

I see ID (number), level (number), gender (presumably only 2 options, why 32?), dead (BOOL/TINYINT(1)?), and last-updated (TIMESTAMP or BIGINT, depending on how you want to implement?) that are all VARCHAR(32) that should not be. — Noah
– Noah, Commented Jun 18, 2014 at 3:58
Yeah Ill edit those before I actually make the table this was mainly an example of the general structure I had in mind, thanks though. They def wont all be varchar32 when I make the final table I use on the live site. — Paul Duncan
– Paul Duncan, Commented Jun 18, 2014 at 5:01
your hero table should have these variables to meet your array values. (id, name, level, hardcore, gender, dead, class, last-updated, paragonLevel, paragonLevelHardcore, battleTag) and its up to you how you records these succeedings array of values in your database are you going to track who kills those heroes?, how many time played every heroes?, table of fallen heroes? and progression table? who kills = array( ) timeplayed = array() fallenheroes = array() progression = array() — rm_beginners
– rm_beginners, Commented Jun 19, 2014 at 2:02

Black Sheep · Accepted Answer · 2014-06-18 04:48:41Z

1

From 1 to 12...

h12name varchar(32),
h12id varchar(32),
h12level varchar(32),
h12hardcore varchar(32),
h12gender varchar(32),
h12dead varchar(32),
h12class varchar(32),
h12last-updated varchar(32),

Below can be 1 column "GAME_NAME" so still you can add....

$sql = "CREATE TABLE career (
ID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
battletag varchar(32),
lastHeroPlayed varchar(32),
lastUpdated varchar(32),
monsters varchar(32),
elites varchar(32),
hardcoreMonsters varchar(32),
barbarian varchar(32),
crusader varchar(32),
demon-hunter varchar(32),
monk varchar(32),
witch-doctor varchar(32),
wizard varchar(32),
paragonLevel varchar(32),
paragonLevelHardcore varchar(32),

I suggest so just create table like this...or you can create 2 join table 1 for game and one for hnn...

name_game varchar(150)
hname varchar(32),
hid varchar(32),
hlevel varchar(32),
hhardcore varchar(32),
hgender varchar(32),
hdead varchar(32),
hclass varchar(32),
hlast-updated varchar(32),

Redundant just put in 1 table..............

edited Jun 18, 2014 at 4:48

Black Sheep

6,7047 gold badges32 silver badges51 bronze badges

answered Jun 18, 2014 at 3:57

rm_beginners

1595 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Paul Duncan Over a year ago

Not 100% following. What do you mean 'create 2 join table 1 for game and one for hnn'?

rm_beginners Over a year ago

if you are grouping your games you need to separate the games so in the future you can still add more games in your games table. the h11...hnn is separate table so that you still can add...just join 2 table together and you can store more rows of data. gamesID in games table and gamesID in HNN table join that 2 tables together and get the id of every hnn for every games.

Paul Duncan Over a year ago

The hnn are heros and there is a max of 12 a career can have at one time, so it can never go more than that

rm_beginners Over a year ago

each hero playing all games?

Paul Duncan Over a year ago

Each hero is part of the career. Not sure what you mean by games. THis is just the career data of one player and his list of characters he has. If I were to separate them into different tables, one for career and one for each the heros, Im not sure how Id get hero data when looking up the career unless I first went out and got the array again and that would defeat the purpose of having it in the db. Im just not understanding.

|

Collectives™ on Stack Overflow

Multidimensional Key/Value Array into MySQL Table

1 Answer 1

7 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Related