I'm newbie with PHP and JSON. I have a script that collects data from an JSON API and stores it in to MySQL data-base.
Script is working fine but I have a problem collecting specific array data. This is the script :
<?php
$url="";
$db_name=" ";
$db_server=" ";
$db_user=" ";
$db_password="";
$newline=PHP_EOL;
$mysqli = new mysqli($db_server,$db_user, $db_password, $db_name);
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}else{
echo "You are connected to the database ($db_name)" . $newline;
}
echo "Initial character set: ". $mysqli->character_set_name(). $newline;
if (!$mysqli->set_charset("utf8")) {
echo "Error loading character set utf8: ", $mysqli->error. $newline;
exit();
} else {
echo "Current character set: ", $mysqli->character_set_name(). $newline;
}
$json = file_get_contents($url . '1}');
$obj = json_decode(utf8_encode($json));
$totalPages=$obj->totalPages;
//read page
for($i = 1; $i < $totalPages ; $i++) {
echo "Read URL " . $url . $i . '}'. $newline;
$json = file_get_contents($url . $i . '}');
$obj = json_decode($json);
foreach($obj->items as $item){
$id = $item->id;
$Name=$item->name;
$FirstName = $item->firstName;
$LastName = $item->lastName;
$attributes = $item->attributes;
foreach($attributes as $attribute){
if ($attribute->name=="fut.attribute.DIV"){
$DIV = $attribute->value;
}
if ($attribute->name=="fut.attribute.POS"){
$POS = $attribute->value;
}
}
$r = $mysqli->query("SELECT id FROM DATABAS WHERE id=". $id);
if ($r->num_rows <= 0){ //INSERT
$query = "INSERT INTO DATABASE ( ";
$query.= "`id` , ";
$query.= "`Name` , ";
$query.= "`FirstName` , ";
$query.= "`LastName` , ";
$query.= "`DIV` ,";
$query.= "`POS` ) VALUES (";
$query.= $id . " , ";
$query.= "'" . $Name . "' , ";
$query.= "'" . $FirstName . "' , ";
$query.= "'" . $LastName . "' , ";
$query.= "'" . $DIV. "' , ";
$query.= "'" . $POS. "' ";
$query.= ");";
$mysqli->query($query);
echo "Last Name inserted was: " . $Name . $newline;
}else{ //UPDATE
$query = "UPDATE database ";
$query.= "SET `Name` ='".$Name."' , ";
$query.= "`FirstName` ='".$FirstName."' , ";
$query.= "`LastName` ='".$LastName."' , ";
$query.= "`DIV`='".$DIV."' , ";
$query.= "`POS`='".$POS."' ";
$query.= "WHERE id=".$id;
$mysqli->query($query);
echo "Last ID update was: " . $_id . $newline;
}
}
}
$mysqli->close();
function replace_unicode_escape_sequence($match) {
return mb_convert_encoding(pack('H*', $match[1]), 'UTF-8', 'UCS-2BE');
}
function unicode_decode($str) {
return preg_replace_callback('/\\\\u([0-9af]{4})/i', 'replace_unicode_escape_sequence', $str);
}
The Json data which I like to add is Traits "traits" and Specialties specialities. Data is stored with [] and I simply have now idea how to read it.
Attributes are stored likewise but have fixed names to point to.
This is partial Json data:
,"position":"RW","playStyle":"Basic","playStyleId":null,"height":170,"weight":72,"birthdate":"1987-06-24","age":29,"acceleration":99,"aggression":58,"agility":95,"balance":98,"ballcontrol":99,"foot":"Left","skillMoves":4,"crossing":90,"curve":99,"dribbling":99,"finishing":99,"freekickaccuracy":99,"gkdiving":6,"gkhandling":11,"gkkicking":15,"gkpositioning":14,"gkreflexes":8,"headingaccuracy":99,"interceptions":36,"jumping":82,"longpassing":89,"longshots":97,"marking":21,"penalties":82,"positioning":99,"potential":95,"reactions":95,"shortpassing":99,"shotpower":89,"slidingtackle":35,"sprintspeed":96,"standingtackle":38,"stamina":91,"strength":71,"vision":99,"volleys":94,"weakFoot":4,"traits":["Shooting - Finesse Shot","Dribbler - Speed Dribbler","One Club Player","Ultimate Professional"],"specialities":["Speedster","Dribbler","Distance Shooter","Crosser","FK Specialist","Acrobat","Clinical Finisher","Complete Forward","Poacher"],"atkWorkRate":"Medium","defWorkRate":"Low","playerType":"TEAM OF THE YEAR","attributes":[{"name":"fut.attribute.PAC","value":99,"chemistryBonus":[0]},{"name":"fut.attribute.SHO","value":98,"chemistryBonus":[0]},{"name":"fut.attribute.PAS","value":97,"chemistryBonus":[0]},{"name":"fut.attribute.DRI","value":99,"chemistryBonus":[0]},{"name":"fut.attribute.DEF","value":40,"chemistryBonus":[0]},{"name":"fut.attribute.PHY","value":75,"chemistryBonus":[0]}],"name":"Messi","quality":"gold","color":"toty","isGK":false,"positionFull":"Right
I really hoop some can help me. I try to find the solution myself but the solutions I have seen don’t correspond with the used code and my lack of knowledge on the subject is limiting me to implement it myself.
