php - 如何抓取json数据并存入数据库

Question

我正在尝试从 json 中获取数据，我想将其保存在数据库中。我有错误，请帮我接通。下面是我的代码。

<?php

//My connection was ok

//read the json file contents
$url = ('http://api.football-data.org/v1/teams/61/players/');
$jsondata = file_get_contents($url);

//convert json object to php associative array
$data = json_decode($jsondata, true);
//print_r($data);

//get the employee details
foreach ($data['_links']['players'] as $myp) {
    $name = $myp['name'];
    $posi = $myp['position'];
    $nation = $myp['nationality'];
    $market = $myp['marketValue'];
}

//    //insert into mysql table
mysql_select_db($db, $conn); //database and connection 

$date = date('Y-m-d H:m:s'); //date stamp formatting
//sql query that insert the user info into the database
$sql = "INSERT INTO player ( Name, Position, Nationality, Market, Created)       
        VALUES( ?,?,?,?,?)";
// bind variables to insert query params
    mysqli_stmt_bind_param($sql, 'sss', $name, $posi, $nation, $market, $date);
//if the connection is sucessful, display regards message
if (mysql_query($sql, $conn)) {
    echo "Thank you <br/>";
} else {   //if the connection is not established
    die('Error: ' . mysql_error());
}

mysql_close($conn); //close of process or connection
?>

我尝试先打印我想要的值/数据，以确保 foreach 循环正常工作，但我也遇到了错误:

注意:Undefined index: players in C:\xampp\htdocs\lab4\apitest.php on line 22

警告:第 22 行为 C:\xampp\htdocs\lab4\apitest.php 中的 foreach() 提供的参数无效

警告:mysql_select_db() 期望参数 2 是第 31 行 C:\xampp\htdocs\lab4\apitest.php 中给出的资源、对象

警告:mysqli_stmt_bind_param() 期望参数 1 为 mysqli_stmt，第 38 行 C:\xampp\htdocs\lab4\apitest.php 中给出的字符串

警告:mysql_query() 期望参数 2 是第 40 行 C:\xampp\htdocs\lab4\apitest.php 中给出的资源、对象错误:

这是我的 json:

{

    "_links": {
        "self": {
            "href": "http://api.football-data.org/v1/teams/61/players"
        },
        "team": {
            "href": "http://api.football-data.org/v1/teams/61"
        }
    },
    "count": 25,
    "players": [
        {
            "name": "Marcos Alonso",
            "position": "Left-Back",
            "jerseyNumber": 3,
            "dateOfBirth": "1990-12-28",
            "nationality": "Spain",
            "contractUntil": "2021-06-30",
            "marketValue": "9,000,000 €"
        },
        {
            "name": "Marco van Ginkel",
            "position": "Central Midfield",
            "jerseyNumber": null,
            "dateOfBirth": "1992-12-01",
            "nationality": "Netherlands",
            "contractUntil": "2018-06-30",
            "marketValue": "7,000,000 €"
        },

请帮助我了解如何成功读取所需数据并将其存储在数据库中。

非常感谢!

Answer 1

您的数据访问方法有问题。你写:

foreach ($data['_links']['players'] as $myp) {

你必须改成这样才能得到正确的结构:

foreach ($data['players'] as $myp) {

因为 players 键不是 _links 的子键。

第二个问题是如果你想将所有玩家插入数据库。您必须将语句移动并插入并执行到 foreach 循环中。

希望对你有帮助!