dsgtew3241
dsgtew3241
2018-03-07 21:29

PHP将嵌套的json插入mySQl

已采纳

I'm trying to insert nested json from a URL into mySQL.

I've tried a few foreach as per other questions/answers on here but can't seem to get it to work. I've given it a go with a for loop and it is inserting however it is only inserting 1 record, which is the final one.

There are 3 first-level array elements (formguide, teams, players) of which i'm wanting the values from formguide only.

Sample JSON that is returned from the URL:

{ 

"formguide": [
{
    "SUSPENSION": null,
    "WEEKPOINTS": "7",
    "TEAMCODE": "LIV",
    "VALUE": "4.5",
    "POINTS": "215",
    "PLAYERNAME": "Salah, M",
    "TEAMNAME": "Liverpool",
    "SIXWEEKPOINTS": "58",
    "INJURY": null,
    "PLAYERID": "3324",
    "POS": "MID"
    },
    {
    "SUSPENSION": null,
    "WEEKPOINTS": "8",
    "TEAMCODE": "TOT",
    "VALUE": "7.0",
    "POINTS": "209",
    "PLAYERNAME": "Kane, H",
    "TEAMNAME": "Tottenham Hotspur",
    "SIXWEEKPOINTS": "49",
    "INJURY": null,
    "PLAYERID": "4002",
    "POS": "STR"
    },

My code:

<?php
$servername = "localhost";
$username = "root";
$password = "pass";
$dbname = "my_db";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

include('simple_html_dom.php');

$url = file_get_contents('https://insertjsonURL.com');
$array = json_decode($url, true);
$length = count($array['formguide']);

//assign array keys to variables for sql insert query
for ($i = 0; $i < $length; $i++) {
$sus = $array['formguide'][$i]['SUSPENSION'];
$wpoints = $array['formguide'][$i]['WEEKPOINTS'];
$tcode = $array['formguide'][$i]['TEAMCODE'];
$val = $array['formguide'][$i]['VALUE'];
$points = $array['formguide'][$i]['POINTS'];
$pname = $array['formguide'][$i]['PLAYERNAME'];
$tname = $array['formguide'][$i]['TEAMNAME'];
$sixwpoints = $array['formguide'][$i]['SIXWEEKPOINTS'];
$injury = $array['formguide'][$i]['INJURY'];
$playerid = $array['formguide'][$i]['PLAYERID'];
$pos = $array['formguide'][$i]['POS'];
}

$sql = "INSERT INTO formguide (suspension, weekpoints, teamcode, value, points, playername, teamname, sixweekpoints, injury, playerid, pos)
VALUES ('$sus', '$wpoints', '$tcode','$val','$points','$pname','$tname','$sixwpoints','$injury','$playerid','$pos')";


//output message if successful or not
if (mysqli_query($conn, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

mysqli_close($conn);
?>

As mentioned I tried foreach from the below but neither seemed to work. Not sure if it's anything to do with me retrieving json via a URL? It's the main difference I can see. Poiz answer and AnkiiG answer

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • dqvj51875 dqvj51875 3年前

    The problem is rather simple. Your insert statement is outside your for statement which is resulting on it inserting just the last element of your array, so fix it as:

    for ($i = 0; $i < $length; $i++) { 
        $sus = $array['formguide'][$i]['SUSPENSION'];
        $wpoints = $array['formguide'][$i]['WEEKPOINTS'];
        $tcode = $array['formguide'][$i]['TEAMCODE'];
        $val = $array['formguide'][$i]['VALUE'];
        $points = $array['formguide'][$i]['POINTS'];
        $pname = $array['formguide'][$i]['PLAYERNAME'];
        $tname = $array['formguide'][$i]['TEAMNAME'];
        $sixwpoints = $array['formguide'][$i]['SIXWEEKPOINTS'];
        $injury = $array['formguide'][$i]['INJURY'];
        $playerid = $array['formguide'][$i]['PLAYERID'];
        $pos = $array['formguide'][$i]['POS'];
    
        $sql = "INSERT INTO formguide (suspension, weekpoints, teamcode, 
                              value, points, playername, teamname, 
                              sixweekpoints, injury, playerid, pos)
                VALUES ('$sus', '$wpoints', '$tcode','$val','$points','$pname','$tname',
             '$sixwpoints','$injury','$playerid','$pos')";
    
      //output message if successful or not
      if (mysqli_query($conn, $sql)) {
          echo "New record created successfully";
      } else {
          echo "Error: " . $sql . "<br>" . mysqli_error($conn);
      }
    }
    
    点赞 评论 复制链接分享
  • douyuan1752 douyuan1752 3年前

    If I'm understanding correctly, it's only inserting the last object in the array because there's only one sql sentence being executed and it is after the for statement. Thus, the variables $sus, $wpoints etc are being set with the values of the last object in the array - in your example it would add only the data of the "Kane, H" player.

    For each object you need to create a SQL statement so it can be added to the database. To do so, move you $sql variable into the for statement and use the concatenating assignment operator so you will have one SQL statement for each element set in the $sql variable.

    $sql = "INSERT INTO formguide (suspension, weekpoints /* ... the field names*/) VALUES"; 
    for ($i = 0; $i < $length; $i++) {
      $sus = $array['formguide'][$i]['SUSPENSION'];
      $wpoints = $array['formguide'][$i]['WEEKPOINTS'];
      // The other variables . . .
      $sql .= "('$sus', '$wpoints' /* The other variables*/),";
    }
    

    You can see about inserting multiple rows in MySQL in this link.


    EDIT

    As Jorge Campos mentioned in the comments, the $sql variable will have an spare comma that will cause an error. To correct this error, you can remove the spare comma as below.

    $sql = "INSERT INTO formguide (suspension, weekpoints /* ... the field names*/) VALUES"; 
    for ($i = 0; $i < $length; $i++) {
      $sus = $array['formguide'][$i]['SUSPENSION'];
      $wpoints = $array['formguide'][$i]['WEEKPOINTS'];
      // The other variables . . .
      $sql .= "('$sus', '$wpoints' /* The other variables*/),";
    }
    $pos = strrpos($sql, ","); // Last occurrence of ',' in $sql
    $sql = substr($sql, 0, $pos);// Remove the last ','
    

    However I would choose the Jorge Campos's answer because it requires less processing resources but there is another idea.

    点赞 评论 复制链接分享

相关推荐