使用PHP使用外部JSON数据单独更新MySQL记录

Question

我正试图循环浏览一组包含用户IP的记录。我正在使用freegeoap.net API服务进行地理定位。在users表中，我存储了来自应用程序用户的IP，现在我有兴趣单独更新所有记录，方法是调用API，让它返回JSON内容，我用file_get_contents获取这些内容，然后根据键（即city、region_name AKA state）将其输入到2个字段中。

从API返回的示例数据：http://freegeoip.net/json/

我基本上有三个栏：[ip]，[城市]，[州]

我已经填充了IP数据。

下面是我的PHP代码的开头，它是对SELECT方法的修改。现在我需要单独执行UPDATE查询。file_get_contents的内部是freegeoap的API URL。

<?php
$con=mysqli_connect("localhost","user","password","database");
// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$result = mysqli_query($con,"SELECT * FROM user_log");
while($row = mysqli_fetch_array($result)) {
        $pageContent = file_get_contents('http://freegeoip.net/json/' . $row['ip']);
        $parsedJson  = json_decode($pageContent);
        echo "<br />The IP is: " . $row['ip'];
        echo "<br />The Location is: " . $parsedJson;
}
mysqli_close($con);
?>

问题：如何修改此代码以循环通过每个MySQL记录（基于IP列）并更新从API收到的相关字段数据（城市和州）？

Answer 1

• 循环浏览您的IP
• 获取地理位置
• 更新记录

$result = mysqli_query($con,"SELECT DISTINCT(`ip`) AS ip FROM user_log");
while($row = mysqli_fetch_array($result)) {
        $pageContent = file_get_contents('http://freegeoip.net/json/' . $row['ip']);
        $parsedJson  = json_decode($pageContent, true);
        echo "<br />The IP is: " . $row['ip'];
        echo "<br />The Location is: " . $parsedJson['city'];
        $strDbQuery = "UPDATE user_log SET city = ?, state = ? WHERE ip = ?";
        $objUpdate = mysqli_prepare($con, $strDbQuery);
        if($objUpdate) {
             mysqli_stmt_bind_param($objUpdate, 'sss', $parsedJson['city'], $parsedJson['region_name'], $row['ip']); 
             mysqli_stmt_execute($objUpdate);
        } else {
           echo "Cannot update for ip ". $row['ip'] . PHP_EOL;
        }
}
mysqli_close($con);

Answer 2

<?php
$con = mysqli_connect("localhost","user","password","database");
/* check connection */
if ($con->connect_errno) {
   die("Connect failed: " . $con->connect_error);
}
$result = $con->query("SELECT * FROM `user_log`");
while($row = $result->fetch_array()) {
    $pageContent = file_get_contents('http://freegeoip.net/json/' . $row['ip']);
    $parsedJson  = json_decode($pageContent);
    $stmt = $con->prepare("UPDATE `user_log` SET `city` = ?, `region_name` = ? WHERE `ip` = ?");
    $stmt->bind_param("sss", $parsedJson['city'], $parsedJson['region_name'], $row['ip']);
    $stmt->execute();
}
?>