Json nested Array into Mysql with PHP

Question

Hi All

The following code is working for the first part of the Json script, but when I try and parse the whole Json script I get the following error...

Notice: Undefined index: site_nice in C:\xampp\htdocs\working-scripts-jason\jsontest9.php on line 24

Notice: Undefined index: odds in C:\xampp\htdocs\working-scripts-jason\jsontest9.php on line 25

Working PHP Code following... parsed into the MySQL BD

<?php

    $host = "localhost";
    $username = "student";
    $password = "";
    $dbname = "football";
    $con = mysqli_connect($host, $username, $password, $dbname) or die('Error in Connecting: ' . mysqli_error($con));

    $st = mysqli_prepare($con, 'INSERT INTO epl_odds(sport_nice, team1, team2, commence_time, home_team) VALUES (?, ?, ?, ?, ?)');

    mysqli_stmt_bind_param($st, 'sssss', $sport_nice, $team1, $team2, $commence_time, $home_team);

    $filename = 'jsontest9.json';
    $json = file_get_contents($filename);

    $data = json_decode($json, true);

    foreach ($data as $row) {
      $sport_nice = $row['sport_nice'];
      $team1 = $row['teams']['0'];
      $team2 = $row['teams']['1'];
      $commence_time = $row['commence_time'];
      $home_team = $row['home_team'];

        mysqli_stmt_execute($st);
    }

    mysqli_close($con);
?>

Not Working PHP code following... I think its because of the nested array in the Json file and I can't get the data after "sites" into the database and after a few days searching still no joy, I have found similar issues which has helped me get to the stage but stuck at this point... any help appreciated... Thanks...

<?php

    $host = "localhost";
    $username = "student";
    $password = "";
    $dbname = "football";
    $con = mysqli_connect($host, $username, $password, $dbname) or die('Error in Connecting: ' . mysqli_error($con));

    $st = mysqli_prepare($con, 'INSERT INTO epl_odds(sport_nice, team1, team2, commence_time, home_team, site_nice, h2h) VALUES (?, ?, ?, ?, ?, ?, ?)');

    mysqli_stmt_bind_param($st, 'sssssss', $sport_nice, $team1, $team2, $commence_time, $home_team, $site_nice, $h2h);

    $filename = 'jsontest9.json';
    $json = file_get_contents($filename);

    $data = json_decode($json, true);

    foreach ($data as $row) {
      $sport_nice = $row['sport_nice'];
      $team1 = $row['teams']['0'];
      $team2 = $row['teams']['1'];
      $commence_time = $row['commence_time'];
      $home_team = $row['home_team'];
      $site_nice = $row['sites']['site_nice'];
      $h2h = $row['sites']['odds']['h2h']['0'];

        mysqli_stmt_execute($st);
    }

    mysqli_close($con);
?>

Json File...

[
  {
    "sport_key": "soccer_epl",
    "sport_nice": "EPL",
    "teams": [
      "Brighton and Hove Albion",
      "West Ham United"
    ],
    "commence_time": 1538766000,
    "home_team": "Brighton and Hove Albion",
    "sites": [
      {
        "site_key": "unibet",
        "site_nice": "Unibet",
        "last_update": 1538526493,
        "odds": {
          "h2h": [
            2.55,
            2.9,
            3.2
          ]
        }
      }
    ],
    "sites_count": 9
  }
]

rjdown · Accepted Answer · 2018-10-06 10:48:27Z

1

sites is an array, so you need to loop through it to get the data, or just use this if there will only ever be one:

$site_nice = $row['sites'][0]['site_nice'];

answered Oct 6, 2018 at 10:48

rjdown

9,2073 gold badges35 silver badges48 bronze badges

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

Comments

Gufran Hasan · Accepted Answer · 2018-10-06 10:57:02Z

0

sport_nice is an array of object sites. And commence_time, home_team are object. teams is an array. SO you should try to get data as below:

foreach ($data as $row) {
      $sites = $row->sites;
      $teams = $row->teams;
      $sport_nice = $sites->sport_nice;
      $team1 = $teams[0];
      $team2 = $teams[1];
      $commence_time = $row->commence_time;
      $home_team = $row->home_team;

        mysqli_stmt_execute($st);
    }

Note: First you print data $row data as in foreach loop and see data format.

echo '<pre>';
print_r($row);
echo '</pre>';

edited Oct 6, 2018 at 10:57

answered Oct 6, 2018 at 10:51

Gufran Hasan

9,4297 gold badges44 silver badges57 bronze badges

Collectives™ on Stack Overflow

Json nested Array into Mysql with PHP

Hi All

The following code is working for the first part of the Json script, but when I try and parse the whole Json script I get the following error...

Working PHP Code following... parsed into the MySQL BD

Json File...

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Hi All

The following code is working for the first part of the Json script, but when I try and parse the whole Json script I get the following error...

Working PHP Code following... parsed into the MySQL BD

Json File...

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related