PHP将嵌套的json插入mySQl

Question

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

Answer 1

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);
  }
}