PHP insert nested json into 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);
  }
}

Answer 2

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.