Dynamic SQL Inserts for JSON data [PHP]

Question

Near daily I am tasked with inserting JSON data into a relational database via PHP, as is with JSON data some records with have certain columns while others do not, and this tends to be a problem when inserting into a table.

If I am inserting several thousands students a record might look like

{"name": "Billy Jackson", "Height": 172, "DOB" : "2002-08-21"}

However its not certain that height and or DOB is set in any record, what I currently do is something like

<?php
  foreach($records as $json){
      $name = addslashes($json['name']);
      if(isset($json['Height']){
          $height = $json['Height'];
      }
      else{
          $height = "NULL"
      }
      if(isset($json['DOB']){
          $dob = $json['DOB'];
      }
      else{
          $dob = "NULL"
      }
 }
 $db->query("INSERT INTO table (name, height, dob) VALUES ('$name', $height, '$dob')");

As you may see this is not elegant nor does it work for several types, fields like DOB do not accept NULL, nor do enums.

Is there a more elegant built in solution, to only try and insert into columns where the value exists in the JSON.

Is this something prepared statements handle?

EDIT

lets say the example record above did not have DOB setthe insert statement would look like

"INSERT INTO table (name, height, dob) VALUES ('Billy Jackson', 172, 'NULL')"

Which fails, if have $dob be set to null ($dob = null) if it is not set then the insert statement looks like

"INSERT INTO table (name, height, dob) VALUES ('Billy Jackson', 172, '')"

Which fails

Why even include the dob column? because some records do have a dob and I want them included in the insert

Answer 1

Empty string '' is not the same as null. Nor is the string "null". Since your query explicitly quotes the contents of the $dob variable, you're quoting the string null such that it becomes "null" which is definitely not null. :)

To avoid the need to mess with quotes (and SQL injection), you'll want to use a prepared statement, something like this:

$db->prepare('INSERT INTO table (name, height, dob) VALUES (?, ?, ?)');

Then when you bind the values, PHP will automatically take care of what fields need quotes and which don't.

Also note, you can shortcut this:

if (isset($json['Height']){
    $height = $json['Height'];
} else {
    $height = "NULL"
}

Into just this:

$height = $json['Height'] ?? null;

Which would eliminate a bunch of your code and make your bind something like this:

$stmt->bind_param(
    'sis',
    $json['name'],
    $json['Height'] ?? null,
    $json['dob'] ?? null
);

Answer 2

You should start with addressing the problems in your table design.

All columns that MUST have data should be set to NOT NULL, and a default value set, if appropriate. It may not be appropriate to have a default value for User Name, for example, so don't set one.

All columns that MIGHT have data should be set to accept NULL, with a default value set as appropriate. If there's no data then the correct value should generally be NULL and that should be set as a default.

Note that both DATE and ENUM columns can accept NULL if properly configured.

Once you have your column definitions correct you can generate an INSERT query based on the actual values you find in your JSON file. The data integrity rules you set in your table definition will ensure that appropriate values are entered for any row that is created with values missing, or that the row is not created if 'must have' data is missing.

This leads to some code like this, based on PDO prepared statements:

$json = '{"name": "Billy Jackson", "Height": 172, "DOB" : "2002-08-21"}';

$columnList = [];
$valueList = [];

$j = json_decode($json);
foreach($j as $key=>$value) {
    $columnList[] = $key;
    // interim processing, like date conversion here:
    // e.g if $key == 'DOB' then $value = reformatDate($value);
    $valueList[] = $value;

}
// Now create the INSERT statement
// The column list is created from the keys in the JSON record
// An array of values is assembled from the values in the JSON record
// This is used to create an INSERT query that matches the data you actually have

$query = "INSERT someTable (".join(',',$columnList).") values (".trim(str_repeat('?,',count($valueList)),',').")";
// echo for demo purposes
echo $query; // INSERT someTable (name,Height,DOB) values (?,?,?)

// Now prepare the query
$stmt = $db->prepare($query);

// Execute the query using the array of values assembled above.
$stmt->execute($valueList);

Note: You many need to extend this to handle mapping from JSON keys to column names, format changes in date fields, etc.