php - 从steamapi(json)抓取数据并将其导入到mysql

Question

我不太清楚如何将数据从远程 json (steamapi) 导入到 MySql。

<?php 
$con = mysql_connect("localhost","XXXXXXXXXXXXXXXXXXXX","XXXXXXXXXXXXXXXXXXXXXXXXX");
mysql_select_db('XXXXXXXXXXXXXXXXX',$con);

$url = "http://api.steampowered.com/ISteamUser/GetPlayerSummaries/v0002/?key=XXXXXXXXXXXXXXXXXXXXXXXX&steamids=76561198033811393";
$json = file_get_contents($url);
$result = json_decode($json);
foreach($result as $key => $value)
{
    if($value)
    {
        mysql_query("INSERT INTO steam (steamid, 
                                        communityvisibilitystate, 
                                        profilestate) 
                                VALUES ($value->steamid,
                                        $value->communityvisibilitystate,
                                        $value->profilestate)");
    }
    mysql_close($con);
}
?>

Steam JSON 图像(点击查看大图):

Answer 1

您可以使用$data->response->players读取每个玩家，它将被分配给$player，然后您可以直接访问他们的对象，例如$player->steamid。请参阅下面的示例:

$data = json_decode($json);
foreach($data->response->players as $player)
{
    echo $player->steamid, "\n\n";
}

我还建议您像这样更改查询:

$query = sprintf("INSERT INTO steam (steamid,
                                     communityvisibilitystate,
                                     profilestate) VALUES ('%s','%s','%s')",
                mysql_real_escape_string($player->steamid),
                mysql_real_escape_string($player->communityvisibilitystate),
                mysql_real_escape_string($player->profilestate));

防止任何进一步的问题，例如断弦或注入(inject)。

SQL 部分的结果代码为:

// Build the query
$query = sprintf("INSERT INTO steam (steamid,
                                     communityvisibilitystate,
                                     profilestate) VALUES ('%s','%s','%s')",
                mysql_real_escape_string($player->steamid),
                mysql_real_escape_string($player->communityvisibilitystate),
                mysql_real_escape_string($player->profilestate));

// Perform Query
$insert = mysql_query($query);

// Check result
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
if (!$insert)
{
    echo 'Invalid query: ' . mysql_error() . "\n";
    echo 'Whole query: ' . $query . "\n";
    echo "------------------\n";
}

鉴于您的实际代码，它看起来像这样:

<?php 
$con = mysql_connect("localhost","XXXXXXXXXXXXXXXXXXXX","XXXXXXXXXXXXXXXXXXXXXXXXX");
mysql_select_db('XXXXXXXXXXXXXXXXX',$con);

$url = "http://api.steampowered.com/ISteamUser/GetPlayerSummaries/v0002/?key=XXXXXXXXXXXXXXXXXXXXXXXX&steamids=76561198033811393";
$json = file_get_contents($url);
$data = json_decode($json);
foreach($data->response->players as $player)
{
    // Build the query
    $query = sprintf("INSERT INTO steam (steamid,
                                         communityvisibilitystate,
                                         profilestate) VALUES ('%s','%s','%s')",
                    mysql_real_escape_string($player->steamid),
                    mysql_real_escape_string($player->communityvisibilitystate),
                    mysql_real_escape_string($player->profilestate));

    // Perform Query
    $insert = mysql_query($query);

    // Check result
    // This shows the actual query sent to MySQL, and the error. Useful for debugging.
    if (!$insert)
    {
        echo 'Invalid query: ' . mysql_error() . "\n";
        echo 'Whole query: ' . $query . "\n";
        echo "------------------\n";
    }
}
mysql_close($con);