How do I extract data from JSON and insert it in MySQL with PHP?

Question

I need to extract data from a JSON fila and insert it in MySQL with PHP. This is the code,

<?php
  $connect = mysqli_connect("localhost", "root", "pass", "dbname"); //Connect PHP to MySQL Database
  $query = '';
  $table_data = '';
  $filename = "organizations.json";
  $data = file_get_contents($filename); //Read the JSON file in PHP
  $array = json_decode($data, true); //Convert JSON String into PHP Array
  foreach($array as $row) //Extract the Array Values by using Foreach Loop - "Inserto todos los campos en la tabla pero no muestro el 'id' en la web"
  {
   $query .= "INSERT INTO organizations(id, displayName, created) VALUES ('".$row["id"]."', '".$row["displayName"]."', '".$row["created"]."'); ";  // Make Multiple Insert Query 
   $table_data .= '
        <tr>
    <td>'.$row["displayName"].'</td>
    <td>'.$row["created"].'</td>
</tr>
   '; //Data for display on Web page
  }

The code works fine with the following json file:

[
    {
      "id": "Y2lzY29z",
      "name": "Enterprise Edition",
      "totalUnits": 1000,
      "consumedUnits": 1
    },
    {
      "id": "MGUzZjBj",
      "name": "Messaging",
      "totalUnits": 1000,
      "consumedUnits": 0
    }
]

But it doesn't work with the following json file:

{
  "items": [
    {
      "id": "Y2lzY29z",
      "name": "Enterprise Edition",
      "totalUnits": 1000,
      "consumedUnits": 1
    },
    {
      "id": "MGUzZjBj",
      "name": "Messaging",
      "totalUnits": 1000,
      "consumedUnits": 0
    }
  ]
}

I don't know how to write the query correctly

$query .= "INSERT INTO organizations
                    (id, displayName, created) 
            VALUES ('".$row["id"]."', '".$row["displayName"]."', 
                    '".$row["created"]."');

Thanks!

Have you tried altering your foreach - `foreach($array["items"] as $row)` — Nigel Ren, Apr 03 '20 at 14:14
sorry, I´ve pasted the wrong variables on Json files. The correct variables are id, displayName, created.. But this isn´t the problem — Mariano Munarriz, Apr 03 '20 at 14:17
@MarianoMunarriz if you pasted something wrong, you can change your question easily. Find the "edit" link just below the blue "php" and "mysql" tags. But you're right, that isn't the main problem. See the various answers below. — ADyson, Apr 03 '20 at 14:18
[How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) — Dharman, Apr 03 '20 at 18:23

RiggsFolly · Answer 1 · 2020-04-03T14:31:26.607

First you should parameterise the query and then bind the values, this protects against SQL Injection Attack.

This also allows you to prepare the query once but execute it, with different parameter many times, thus saving round trips to the database and multiple compilations of the query.

And of course the array starts from $array['items']

<?php
//Connect PHP to MySQL Database
$connect = mysqli_connect("localhost", "root", "pass", "dbname"); 

$filename = "organizations.json";
$data = file_get_contents($filename); //Read the JSON file in PHP
$array = json_decode($data, true); //Convert JSON String into PHP Array

//prepare the query once
$query = "INSERT INTO organizations
                    (id, displayName, created) 
            VALUES (?,?,?)"; 
$stmt = $connect->prepare($query);

foreach($array['items'] as $row) {
    $stmt->bind_param('iss',  $row['id'], $row['displayName'], $row['created']);
    $stmt->execute();

    $table_data .= "<tr>
            <td>$row[displayName]</td>
            <td>$row[created]</td>
        </tr>";
}

@yourCommonSense Does this better meet with your approval – RiggsFolly Apr 03 '20 at 14:27 — RiggsFolly, Apr 03 '20 at 14:27

score 1 · Accepted Answer · answered Apr 03 '20 at 14:17

"it doesn't work with the following json file"

...that's because the structure is different in the JSON. This should be fairly obvious - you can't expect it to just automatically work with any arbitrary data structure you give it.

The code needs modifying to read the new structure. In this specific case it's quite trivial - you have an inner object holding the array, so you have to loop through that object instead of the top-level:

Change

foreach($array as $row)

to

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

I was able to solve it, thank you very much for your help – Mariano Munarriz Apr 03 '20 at 14:25 — Mariano Munarriz, Apr 03 '20 at 14:25

score 0 · Answer 3 · answered Apr 03 '20 at 14:17

Your second Json object has a field, "items". So you you just need to make a small adjustment to your code:

    <?php
         ...
          $array = json_decode($data, true); 
          $items = $array["items"];
          foreach($items as $row){
          ...
          }

score -1 · Answer 4 · answered Apr 03 '20 at 14:16

With the same code, You just need to adjust in foreach loop. Use $array['items'] instand of just $array

  <?php
      $connect = mysqli_connect("localhost", "root", "pass", "dbname"); //Connect PHP to MySQL Database
      $query = '';
      $table_data = '';
      $filename = "organizations.json";
      $data = file_get_contents($filename); //Read the JSON file in PHP
      $array = json_decode($data, true); //Convert JSON String into PHP Array
      foreach($array['items'] as $row) //Extract the Array Values by using Foreach Loop - "Inserto todos los campos en la tabla pero no muestro el 'id' en la web"
      {
       $query .= "INSERT INTO organizations(id, displayName, created) VALUES ('".$row["id"]."', '".$row["displayName"]."', '".$row["created"]."'); ";  // Make Multiple Insert Query 
       $table_data .= '
            <tr>
        <td>'.$row["displayName"].'</td>
        <td>'.$row["created"].'</td>
    </tr>
       '; //Data for display on Web page
      }

How do I extract data from JSON and insert it in MySQL with PHP?

4 Answers4