I am pulling data from an api that outputs json data that contains the players stats. I have a separate table for each skill in a game I play. There are 27 total skills so that would be 28 tables total including the users table. There are two issues first is that one of the insert statements is trying to put the same record twice and creating a duplicate entry error and the second issue is some of the insert statements are not putting anything into their respective tables. The is another issue that happening as well which is that its only putting one users stat information into the database. Example: I have 3 users in my users table: Bob, Jane, Paul its only putting Bobs skill data in. So essentially only inserting one record when there are roughly 150 names in the users table at the moment. Ohh and I forgot to mention that I am inserting the users id from users table into the skills table. I included the $highscores array just in case anybody needed that
At first I thought it was the fact that I used the $query variable more than once so I made them unique, ex: $query1, $query2, $query3 etc, the problems persist. I checked all the spelling where the if is checking if the variable is equal to a skill and all the spelling matches. All the code is identical.
Array
(
[skillvalues] => Array
(
[0] => Array
(
[level] => 99
[xp] => 648475179
[rank] => 41501
[id] => 3
)
.
.
.
repeat for the other 26 skills
)
)
//grab users and the users id out of the users table
$query = $conn->query("SELECT user_id, name FROM users ORDER BY user_id
ASC");
ini_set('max_execution_time', 1600);
While($row = $query->fetch(PDO::FETCH_ASSOC)){
$name = $row["name"];
$user_id = $row["user_id"];
//to compensate for spaces because otherwise file_get_contents will fail
$name = str_replace(' ', '%20', $name);
//api where the data is coming from which outputs as json
$highscores = file_get_contents("https://apps.runescape.com/runemetrics/profile/profile?user=" .$name);
$highscores = json_decode($highscores, true);
$offset = 8;
//only grab the skills data
$highscores = array_slice($highscores, $offset);
//put the space back
$name = str_replace('%20', ' ', $name);
//assigns skill name to skill id
$skills_arr = array('0' => "attack",'1' => "defence",'2' => "strength",'3' => "hitpoints",'4' => "ranged",'5' => "prayer",'6' => "magic",'7' => "cooking",'8' => "woodcutting",'9' => "fletching",'10' => "fishing",'11' => "firemaking",'12' => "crafting",'13' => "smithing",'14' => "mining",'15' => "herblore",'16' => "agility",'17' => "thieving",'18' => "slayer",'19' => "farming",'20' => "runecrafting",'21' => "hunter",'22' => "construction",'23' => "summoning",'24' => "dungeoneering",'25' => "divination",'26' => "invention");
//to catch if file_get_contents ends up grabing a profile the player has thier data set to private
if(!empty($highscores)){
foreach($highscores['skillvalues'] as $skills){
$skill = $skills_arr[$skills['id']];
$xp = $skills['xp'];
$rank = $skills['rank'];
$level = $skills['level'];
$profile = "1";
//to catch if file_get_contents ends up grabbing a profile where the player has no rank in any skill
if(!empty($rank)){
//assign the values to the correct skill
if($skill == "attack"){
$skill = $skill;
$rank = $rank;
$level = $level;
$xp = $xp;
$query = $conn->prepare("INSERT INTO $skill VALUES(?, ?, ?, ?, ?)");
$query->execute(array($user_id, $rank, $level, $xp, $profile));
}
.
.
.
repeat the exact same code for the other 26 skills
}
}//END WHILE LOOP
