我希望你能帮我一点。我有一个包含653 * 24对象的JSON文件，我想将它们添加到本地数据库（PHPMyAdmin，Mysql）中。我写了两个 for 循环（第一个 1 - 654，第二个 0 - 24），在这些循环中，我必须在 6 个不同的表中插入信息。我这样做了，但问题是循环不会插入所有 ~15k 行，只是 ~200 行，然后浏览器上的加载符号中断，插入停止而没有错误消息。
我想，插入对于系统来说太多了，但我不知道，所以希望你们有一个答案/解决方案让我解决问题。
for($i = 1; $i < 654; $i++) {
for($j = 0; $j < 24; $j++) {
*do 6 inserts here*
}
}
编辑：我再次查看了代码，加载653个json链接是否有问题？因此，它可能会导致脚本结束而不是超时？
杰伦
https://www.easports.com/fifa/ultimate-team/api/fut/item?jsonParamObject={%22page%22:1}
.PHP
$db = $this->getServiceLocator()->get('db');
$k = 1;
$dataArray = [];
for($i = 1; $i < 11; $i++) {
for($j = 0; $j < 24; $j++) {
$json = file_get_contents('https://www.easports.com/fifa/ultimate-team/api/fut/item?jsonParamObject={%22page%22:' . $i . '}');
$obj = json_decode($json);
$id = $k;
//dbArray
$playerArray = array("characteristics" => array(),
"club" => array(),
"nation" => array(),
"defending" => array(),
"dribbling" => array(),
"goalkeeper" => array(),
"league" => array(),
"pace" => array(),
"passing" => array(),
"physical" => array(),
"player" => array(),
"rating" => array(),
"shooting" => array());
$traitArray = array();
$specialitiesArray = array();
$specialImageArray = array();
$attributeArray = array();
foreach($obj->items[$j]->traits as $value) {
array_push($traitArray, $value);
}
$traitArray = implode(", ", $traitArray);
foreach($obj->items[$j]->specialities as $value) {
array_push($specialitiesArray, $value);
}
$specialitiesArray = implode(", ", $specialitiesArray);
foreach($obj->items[$j]->specialImages as $value) {
array_push($specialImageArray, $value);
}
if($specialImageArray[0] != NULL) $obj->items[$j]->headshotImgUrl = $specialImageArray[0];
foreach($obj->items[$j]->attributes as $key) {
array_push($attributeArray, $key->value);
}
array_push($playerArray['characteristics'], array(
"0" => $obj->items[$j]->firstName,
"1" => $obj->items[$j]->lastName,
"2" => $obj->items[$j]->commonName,
"3" => $obj->items[$j]->age,
"4" => $obj->items[$j]->height . " cm",
"5" => "",
"6" => "",
"7" => "",
"8" => $obj->items[$j]->headshotImgUrl,
"9" => $obj->items[$j]->position,
"10" => $obj->items[$j]->rating,
"11" => ucfirst($obj->items[$j]->quality),
"12" => ucfirst($obj->items[$j]->color),
"13" => $obj->items[$j]->skillMoves,
"14" => $obj->items[$j]->weakFoot,
"15" => $obj->items[$j]->foot,
"16" => $obj->items[$j]->atkWorkRate . " / " . $obj->items[$j]->defWorkRate,
"17" => $traitArray,
"18" => $specialitiesArray
));
array_push($playerArray['league'], array(
"0" => $obj->items[$j]->league->name,
"1" => $obj->items[$j]->league->abbrName,
"2" => ""
));
array_push($playerArray['nation'], array(
"0" => $obj->items[$j]->nation->name,
"1" => $obj->items[$j]->nation->imageUrls->large
));
array_push($playerArray['club'], array(
"0" => $obj->items[$j]->club->name,
"1" => $obj->items[$j]->club->imageUrls->normal->large
));
array_push($playerArray['defending'], array(
"0" => $attributeArray[4],
"1" => $obj->items[$j]->interceptions,
"2" => $obj->items[$j]->headingaccuracy,
"3" => $obj->items[$j]->marking,
"4" => $obj->items[$j]->standingtackle,
"5" => $obj->items[$j]->slidingtackle
));
array_push($playerArray['dribbling'], array(
"0" => $attributeArray[3],
"1" => $obj->items[$j]->agility,
"2" => $obj->items[$j]->balance,
"3" => $obj->items[$j]->reactions,
"4" => $obj->items[$j]->ballcontrol,
"5" => $obj->items[$j]->dribbling
));
array_push($playerArray['goalkeeper'], array(
"0" => $obj->items[$j]->gkdiving,
"1" => $obj->items[$j]->gkhandling,
"2" => $obj->items[$j]->gkkicking,
"3" => $obj->items[$j]->gkreflexes,
"4" => $obj->items[$j]->sprintspeed,
"5" => $obj->items[$j]->gkpositioning
));
array_push($playerArray['pace'], array(
"0" => $attributeArray[0],
"1" => $obj->items[$j]->acceleration,
"2" => $obj->items[$j]->sprintspeed
));
array_push($playerArray['passing'], array(
"0" => $attributeArray[3],
"1" => $obj->items[$j]->vision,
"2" => $obj->items[$j]->crossing,
"3" => $obj->items[$j]->freekickaccuracy,
"4" => $obj->items[$j]->shortpassing,
"5" => $obj->items[$j]->longpassing,
"6" => $obj->items[$j]->curve
));
array_push($playerArray['physical'], array(
"0" => $attributeArray[5],
"1" => $obj->items[$j]->jumping,
"2" => $obj->items[$j]->stamina,
"3" => $obj->items[$j]->strength,
"4" => $obj->items[$j]->aggression
));
array_push($playerArray['shooting'], array(
"0" => $attributeArray[1],
"1" => $obj->items[$j]->positioning,
"2" => $obj->items[$j]->finishing,
"3" => $obj->items[$j]->shotpower,
"4" => $obj->items[$j]->longshots,
"5" => $obj->items[$j]->volleys,
"6" => $obj->items[$j]->penalties
));
array_push($playerArray['rating'], array(
"0" => "",
"1" => "",
"2" => "",
"3" => "",
"4" => "",
"5" => "",
"6" => ""
));
array_push($playerArray['player'], array(
"0" => "",
"1" => "",
"2" => "",
"3" => "",
"4" => "",
"5" => "",
"6" => "",
"7" => $obj->items[$j]->isGK
));
$values = array(
':id' => $id,
':charFirstname' => $playerArray["characteristics"][0][0],
':charLastname' => $playerArray["characteristics"][0][1],
':charCommonname' => $playerArray["characteristics"][0][2],
':charAge' => $playerArray["characteristics"][0][3],
':charHeight' => $playerArray["characteristics"][0][4],
':charNation' => $playerArray["nation"][0][0],
':charClub' => $playerArray["club"][0][0],
':charLeague' => $playerArray["league"][0][0],
':charPicture' => $playerArray["characteristics"][0][8],
':charPosition' => $playerArray["characteristics"][0][9],
':charRating' => $playerArray["characteristics"][0][10],
':charQuality' => $playerArray["characteristics"][0][11],
':charColor' => $playerArray["characteristics"][0][12],
':charSkill' => $playerArray["characteristics"][0][13],
':charWeak_foot' => $playerArray["characteristics"][0][14],
':charStrong_foot' => $playerArray["characteristics"][0][15],
':charWorkrates' => $playerArray["characteristics"][0][16],
':charTraits' => $playerArray["characteristics"][0][17],
':charSpecialities' => $playerArray["characteristics"][0][18]);
array_push($dataArray, $values);
$k++;
}
}
foreach($dataArray as $values) {
$data[] = '("' . $values[':id'] . '", "' . $values[':charFirstname'] . '",
"' . $values[':charLastname'] . '", "' . $values[':charCommonname'] . '",
"' . $values[':charAge'] . '", "' . $values[':charHeight'] . '",
"' . $values[':charNation'] . '", "' . $values[':charClub'] . '",
"' . $values[':charLeague'] . '", "' . $values[':charPicture'] . '",
"' . $values[':charPosition'] . '", "' . $values[':charRating'] . '",
"' . $values[':charQuality'] . '", "' . $values[':charColor'] . '",
"' . $values[':charSkill'] . '", "' . $values[':charWeak_foot'] . '",
"' . $values[':charStrong_foot'] . '", "' . $values[':charWorkrates'] . '",
"' . $values[':charTraits'] . '", "' . $values[':charSpecialities'] . '")';
}
$query = 'INSERT INTO characteristics (id, firstname, lastname, commonname, age, height, nation, club, league, picture, position, rating, quality, color, skill, weak_foot, strong_foot, workrates, traits, specialities) VALUES' . implode(',', $data);
$stmt = $db->createStatement($query);
$stmt->prepare($query);
$results = $stmt->execute(array(
':id' => $id,
':charFirstname' => $playerArray["characteristics"][0][0],
':charLastname' => $playerArray["characteristics"][0][1],
':charCommonname' => $playerArray["characteristics"][0][2],
':charAge' => $playerArray["characteristics"][0][3],
':charHeight' => $playerArray["characteristics"][0][4],
':charNation' => $playerArray["nation"][0][0],
':charClub' => $playerArray["club"][0][0],
':charLeague' => $playerArray["league"][0][0],
':charPicture' => $playerArray["characteristics"][0][8],
':charPosition' => $playerArray["characteristics"][0][9],
':charRating' => $playerArray["characteristics"][0][10],
':charQuality' => $playerArray["characteristics"][0][11],
':charColor' => $playerArray["characteristics"][0][12],
':charSkill' => $playerArray["characteristics"][0][13],
':charWeak_foot' => $playerArray["characteristics"][0][14],
':charStrong_foot' => $playerArray["characteristics"][0][15],
':charWorkrates' => $playerArray["characteristics"][0][16],
':charTraits' => $playerArray["characteristics"][0][17],
':charSpecialities' => $playerArray["characteristics"][0][18]
));
这里有一些资源浪费者。
- 检索 JSON 文件的频率是所需频率的 24 倍。将呼叫移出内部循环
file_get_contents()。 - 准备好的陈述应该牢记这个口头禅：准备一次，执行多次。将语句准备移动到任何循环之外，然后在循环中执行它。
- 您没有构建适当的准备语句。该语句应包含占位符（
?或:label格式），但您将值插入到您的语句中。您传递给execute()的所有这些参数都将被忽略。编辑：实际上，您在页面上插入每个值24次，即您为11页中的每一页插入24 * 24行。
建议更改摘要：
$query = "INSERT INTO characteristics (
id, firstname, lastname, commonname, age, height, nation, club, league,
picture, position, rating, quality, color, skill, weak_foot, strong_foot,
workrates, traits, specialities
) VALUES (
:id, :charFirstname, :charLastname, :charCommonname, :charAge, :charHeight,
:charNation, :charClub, :charLeague, :charPicture, :charPosition, :charRating,
:charQuality, :charColor, :charSkill, :charWeak_foot, :charStrong_foot,
:charWorkrates, :charTraits, :charSpecialities
)";
$stmt = $db->prepare($query);
for ($i = 1; $i < 11; $i++) {
$json = file_get_contents('https://www.easports.com/fifa/ultimate-team/api/fut/item?jsonParamObject={%22page%22:' . $i . '}');
$obj = json_decode($json);
for ($j = 0; $j < 24; $j++) {
//set up your player array
$results = $stmt->execute(array(
':id' => $id,
':charFirstname' => $playerArray["characteristics"][0][0],
':charLastname' => $playerArray["characteristics"][0][1],
':charCommonname' => $playerArray["characteristics"][0][2],
':charAge' => $playerArray["characteristics"][0][3],
':charHeight' => $playerArray["characteristics"][0][4],
':charNation' => $playerArray["nation"][0][0],
':charClub' => $playerArray["club"][0][0],
':charLeague' => $playerArray["league"][0][0],
':charPicture' => $playerArray["characteristics"][0][8],
':charPosition' => $playerArray["characteristics"][0][9],
':charRating' => $playerArray["characteristics"][0][10],
':charQuality' => $playerArray["characteristics"][0][11],
':charColor' => $playerArray["characteristics"][0][12],
':charSkill' => $playerArray["characteristics"][0][13],
':charWeak_foot' => $playerArray["characteristics"][0][14],
':charStrong_foot' => $playerArray["characteristics"][0][15],
':charWorkrates' => $playerArray["characteristics"][0][16],
':charTraits' => $playerArray["characteristics"][0][17],
':charSpecialities' => $playerArray["characteristics"][0][18]
));
}
}
你需要重构你的代码。 因为这个
foreach($obj->items[$j]->traits as $value) {
array_push($traitArray, $value);
}
eqvivalent
$traitArray[] = $obj->items[$j]->traits;
json_decode以格式化数组的方式返回对象？我不明白。你可以简单地从这个对象中获取参数...例如，您解析了haracteristics，但您可以在查询中编写他
。':charFirstname' => $obj->items[$j]->firstName,
':charLastname' => $obj->items[$j]->lastName,
// and over points