title: Dynamic SQL Inserts for JSON data [PHP]
body: <p>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.</p>
  <p>If I am inserting several thousands students a record might look like</p>
  <pre><code>{&quot;name&quot;: &quot;Billy Jackson&quot;, &quot;Height&quot;: 172, &quot;DOB&quot; : &quot;2002-08-21&quot;}
  </code></pre>
  <p>However its not certain that height and or DOB is set in any record, what I currently do is something like</p>
  <pre><code>&lt;?php
    foreach($records as $json){
        $name = addslashes($json['name']);
        if(isset($json['Height']){
            $height = $json['Height'];
        }
        else{
            $height = &quot;NULL&quot;
        }
        if(isset($json['DOB']){
            $dob = $json['DOB'];
        }
        else{
            $dob = &quot;NULL&quot;
        }
   }
   $db-&gt;query(&quot;INSERT INTO table (name, height, dob) VALUES ('$name', $height, '$dob')&quot;);
  </code></pre>
  <p>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.</p>
  <p>Is there a more elegant built in solution, to only try and insert into columns where the value exists in the JSON.</p>
  <p>Is this something prepared statements handle?</p>
  <p>EDIT</p>
  <p>lets say the example record above did not have DOB setthe insert statement would look like</p>
  <p>&quot;INSERT INTO table (name, height, dob) VALUES ('Billy Jackson', 172, 'NULL')&quot;</p>
  <p>Which fails, if have $dob be set to null ($dob = null) if it is not set then the insert statement looks like</p>
  <p>&quot;INSERT INTO table (name, height, dob) VALUES ('Billy Jackson', 172, '')&quot;</p>
  <p>Which fails</p>
  <p>Why even include the dob column? because some records do have a dob and I want them included in the insert</p>