Alex Alex - 4 months ago 12
JSON Question

PHP Save nested json to mysql database

I have some json data that i am retrieving from an external url. It is not importing correctly unless i take out some of the brackets. Anyone know how to properly import this json data? I don't really need the "success", "num_items", "build time" and "updated at". Im a noobie. Thanks!

Here is the php

$filename = "http://www.someurl.com/data.json";
$data = file_get_contents($filename);
$array = json_decode($data, true);

foreach($array as $row)
{
$sql = "INSERT INTO table_all_items(name, quality) VALUES (
'".$row["name"]."',
'".$row["quality"]."'
)";
mysqli_query($connect, $sql);
}


Here is data.json

{
"success": true,
"num_items": 7312,
"items": [
{
"name": "Net",
"quality": "New"
},
{
"name": "Ball",
"quality": "New"
},
{
"name": "Hoop",
"quality": "Used"
}
],
"build_time": 320,
"updated_at": 15680
}

Answer

You were looping through the wrong element of your array. As you want to list the items, your loop must look like :

 foreach($array["items"] as $row)  


//$filename = "http://www.someurl.com/data.json";
//$data = file_get_contents($filename);  
$data='{
    "success": true,
    "num_items": 7312,
    "items": [
        {
            "name": "Net",
            "quality": "New"
        },
        {
            "name": "Ball",
            "quality": "New"
        },
        {
            "name": "Hoop",
            "quality": "Used"
        }
    ],
    "build_time": 320,
    "updated_at": 15680
}';
$array = json_decode($data, true);
$sql = "INSERT INTO table_all_items(name, quality) VALUES ";
 foreach($array["items"] as $row)  
 {  
      $sql = $sql." (
      '".$row["name"]."', 
      '".$row["quality"]."'
      ),";       
 }  
  mysqli_query($connect, substr($sql,0,-1));

I also updated the sql query, for it sends only one request with many values, instead on many requests with one value.