Parse JSON file in PHP and insert into SQL ----

Question

So I have been doing my research and no method seems to parse my file correctly. I have tried these two codes, my data is being inserted into sql however it is inserted 3 times with the first insertion being fine while the other 2 are blank. Here is my JSON file and code:

JSON:

   [
  {
    "Comments": {
      "Manufacturer": "--E", 
      "Model": "----- -----  ----", 
      "BIOSFamily": "---", 
      "BIOSDate": --/--/---8", 
      "SerialNumber": "---------"
    }
  }, 
   {
    "#ComputerSystem.v1-----------ystem": {
      "/redfish/v1/Systems/1/": {
        "AssetTag": "                                ", 
        "Bios": {
          "@odata.id": "/redfish/v1/systems/1/bios/"
            }, 
            "BiosVersion": "U----------------)", 
            "Boot": {
          "BootSourceOverrideMode": "-----y", 
          "BootSourceOverrideTarget": "None", 
          "BootSourceOverrideEnabled": "Disabled", 
          "[email protected]": [
            "None", 
            "Cd", 
            "Hdd", 
            "Usb", 
            "Utilities", 
           "Diags", 
           "BiosSetup", 
            "Pxe", 
           "UefiShell"
          ]

Code:

<?php

 $connect = mysqli_connect("reserve1", 
"root", "","server_31");

 $filename = "-----.json";

 $data = file_get_contents($filename);

 $array = json_decode($data, true);

 foreach($array as $row)
    {
  $sql = "INSERT INTO servers (Model, 
    Manufacturer, BIOSFamily, 
       BIOSDate, 
    SerialNumber)  VALUES 
    ('".$row["Comments"]["Model"]."' , 
    '".$row["Comments"] 
     ["Manufacturer"]."',
   '".$row["Comments"] 
  ["BIOSFamily"]."','".$row["Comments"] 
   ["BIOSDate"]."','".$row["Comments"] 
   ["SerialNumber"]."')";
        mysqli_query($connect, $sql);
       }




    echo "Data in";

   ?>

ERROR:" Notice: Undefined index: Comments "

other forloops i have tried are:

foreach($data as $Comments)
{
    $sql =" INSERT INTO 
'servers'('Manufacturer','Model', 
'BIOSFamily','BIOSDate', 
'SerialNumber'), VALUES('{$Comments- 
   >Manufacturer}', '{$Comments- 
   >Model}',  
    '{$Comments->BiosFamily}', 
    '{$Comments->BiosDate}',
                 '{$Comments- 
   >SerialNumber}')";

    }

ERROR:" Notice: Trying to get property of non-object in"

To reiterate: the first method does get my info onto sql but does so 3 times with the last 2 entries being blank. The second method does not insert anything into my table.

EDIT:

so i tried vardump, using the file itself all i got was NULL, copy and pasting the contents and labeling it $json= ' content ' in the script i get..

 C:\Users\Administrator\Desktop\Reserve1\newtry\NEWJSONP.php:16:
array (size=1)
  0 => 
    object(stdClass)[1]
      public 'Comments' => 
        object(stdClass)[2]
         public 'Manufacturer' => string 'HPE' (length=3)
          public 'Model' => string '-------------' (length=20)
          public 'BIOSFamily' => string '---' (length=3)
          public 'BIOSDate' => string '--/--/----' (length=10)
          public 'SerialNumber' => string '-------' (length=10)
C:\Users\Administrator\Desktop\Reserve1\newtry\NEWJSONP.php:17:
array (size=1)
  0 => 
    array (size=1)
      'Comments' => 
        array (size=5)
          'Manufacturer' => string '---' (length=3)
          'Model' => string '------ ----------' (length=20)
          'BIOSFamily' => string '---' (length=3)
          'BIOSDate' => string '--/--/----' (length=10)
          'SerialNumber' => string '-------' (length=10)

hello, have you try to var_dump($array)? EDIT: Maybe it's a mistake but in your json it misses a closing ] — Alexandre Painchaud
– Alexandre Painchaud, Commented Jul 23, 2018 at 15:45
That's not valid JSON, but if it were: foreach($array[0] as $row) — AbraCadaver
– AbraCadaver, Commented Jul 23, 2018 at 15:50
sorry i missed the " ] ". I will try that you said right now. — John Monte
– John Monte, Commented Jul 23, 2018 at 15:56
Three times with the last two rows being blank? You are going to have to show us the content of the json, because what you've shown, and what you describe is the error ... don't align. — IncredibleHat
– IncredibleHat, Commented Jul 23, 2018 at 15:57

Parfait · Accepted Answer · 2018-07-23 18:10:31Z

1

Simply index in your foreach loop the first item since $array object maintains Comments only in first position. See 0 index from var_dump output:

array (
  0 => 
  array (
    'Comments' => 
    array (
      'Manufacturer' => '--E',
      'Model' => '----- -----  ----',
      'BIOSFamily' => '---',
      'BIOSDate' => ' --/--/-- - 8 ',
      'SerialNumber' => '---------',
    ),
  ),
  1 => 
  array (
    '#ComputerSystem.v1-----------ystem' => 
  ...

Therefore, iterate through the Comments array and use parameterization for readability:

$connect = new mysqli($servername, $username, $password, $dbname);

// PREPARED STATEMENT
$sql = "INSERT INTO servers (Model, Manufacturer, BIOSFamily, BIOSDate, SerialNumber)
        VALUES(?, ?, ?, ?, ?)";

// INDEX FIRST ITEM AT 0
foreach($array[0] as $row) { 

    $stmt = $connect->prepare($sql);

    // BIND PARAMETERS (NO COMMENTS INDEX)
    $stmt->bind_param("sssss", $row["Model"], 
                               $row["Manufacturer"],
                               $row["BIOSFamily"],
                               $row["BIOSDate"],
                               $row["SerialNumber"]);

    // EXECUTE STATEMENT
    $result = $stmt->execute();    
}

answered Jul 23, 2018 at 18:10

Parfait

108k19 gold badges102 silver badges138 bronze badges

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

3 Comments

John Monte Over a year ago

THANK YOU SO MUCH! although I did not use your method, you helped me understand what i w as doing wrong. I took out the [comments] part on my script and it works perfect! The vardump and your explanation helped me understand it. Thank you again so much!

Parfait Over a year ago

Great! Glad to help. I do hope though you are using parameterization (industry best practice with SQL at application layer like PHP).

John Monte Over a year ago

i am a newbie but i will educate myself on that matter immediately

Collectives™ on Stack Overflow

Parse JSON file in PHP and insert into SQL ----

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related