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

DOB field can accept null, check this stackoverflow.com/questions/1691117/… — Bilal Ahmad
– Bilal Ahmad, Commented Aug 6, 2021 at 21:51
Why dont you use a table like this: ID | DATA: ID is the index (that doesn't matter here) | DATA: JSON encoded field, where you put everything you want, and when you read this field you just change the [KEY] as table Header | [VALUE] as it's value — Alexandro Giles
– Alexandro Giles, Commented Aug 6, 2021 at 21:51
"fields like DOB do not accept NULL" Well that's not really a problem with your code or your data, but with your database schema. You'll have to either make the column nullable or throw away rows that are missing that field. — Alex Howansky
– Alex Howansky, Commented Aug 6, 2021 at 22:05
@BilalAhmad the problem with that is how do I set a variable to null as shown, if I said $dob = null the insert looks like Values ('Billy Jackson', 172, '') which does throw an error — Marc-9
– Marc-9, Commented Aug 6, 2021 at 22:08
@AlexandroGiles That was how I did it originally but its nor about just storing the data but making it as easy as possible to do queries on it, I want to easily find the tallest students with one query, and other examples that make storing all the json in one column not very likely — Marc-9
– Marc-9, Commented Aug 6, 2021 at 22:09

Alex Howansky · Accepted Answer · 2021-08-06 22:34:21Z

2

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
);

answered Aug 6, 2021 at 22:34

Alex Howansky

54k8 gold badges85 silver badges102 bronze badges

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

1 Comment

Marc-9 Over a year ago

Thanks so much again, also that tip to shorten my code will be a life saver!

Tangentially Perpendicular · Accepted Answer · 2021-08-06 22:34:07Z

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.

Collectives™ on Stack Overflow

Dynamic SQL Inserts for JSON data [PHP]

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related