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

what does var_dump($array); shows if you add it before your for? — Jorge Campos
– Jorge Campos, Commented Mar 7, 2018 at 21:54

Jorge Campos · Accepted Answer · 2018-03-08 01:11:29Z

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

edited Mar 8, 2018 at 1:11

answered Mar 7, 2018 at 21:59

Jorge Campos

23.8k7 gold badges65 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

auley_code Over a year ago

thanks @Jorge. i tried that previous but was getting an error: Error: INSERT INTO formguide (suspension, weekpoints, teamcode, value, points, playername, teamname, sixweekpoints, injury, playerid, pos) VALUES ('', '', '','','','','','','','','') Incorrect integer value: '' for column 'weekpoints' at row 1

Jorge Campos Over a year ago

Just to be clear, did you added your variables assignments inside the for statement?

auley_code Over a year ago

When doing that I get the same result i.e. the last object alone is added

auley_code Over a year ago

Ah, my if statement was outside the for and it i think I was missing }. All good now. Thanks for the multiple pointers!

Wesley Gonçalves · Accepted Answer · 2018-03-08 23:53:25Z

0

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.

edited Mar 8, 2018 at 23:53

answered Mar 7, 2018 at 22:07

Wesley Gonçalves

2,3753 gold badges22 silver badges27 bronze badges

2 Comments

Jorge Campos Over a year ago

If not handled properly this will break when you run the sql because you will have a spare comma at the end of the statement.

auley_code Over a year ago

Yes that caused an error. Removing it prints what i would expect i.e. all objects but get the error at the end that I can't seem to figure out: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('', '8', 'TOT','7.0','209','Kane, H','Tottenham Hotspur','49','','4002','STR')(' at line 1

Collectives™ on Stack Overflow

PHP insert nested json into mySQl

2 Answers 2

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related