JSON Array Single MySQL Insert and Update

This is another quick one. I wanted to insert a JSON array into MySQL with a single insert and, on duplicate key update. This is how you get it done:

Say you have a JSON array like this:

[{"num":"222","desc":"seconds","freq":11,"type":"hour","active":false},{"num":"1234","desc":"forths","freq":22,"type":"day","active":false},{"num":"3333","desc":"thirds","freq":33,"type":"week","active":true}]

And you want to insert all 3 records into MySQL with only 1 single insert, and at the
same time, you also want to do ON DUPLICATE KEY UPDATE:

//First decode the array 
$arr = $_POST['jsonarr'];
$decarr = json_decode($arr, true);
  
$count = count($decarr);

$values = array(); // This will hold our array values so we do one single insert 
$update_values = array(); // This will hold the values for the ON DUPLICATE KEY UPDATE section

for ($x=0; $x < $count; $x++) 
    {
	
    $newrec = $decarr[$x];  
    $num = $newrec['num']; $num = mysql_real_escape_string($num);
    $desc = $newrec['desc']; $desc = mysql_real_escape_string($desc);
    $freq = $newrec['freq']; $freq = mysql_real_escape_string($freq);
    $type = $newrec['type']; $type = mysql_real_escape_string($type);
    $active = $newrec['active']; $active = mysql_real_escape_string($active);
	
	
    // Create insert array 
    $values[] = "('".$num."', '".$desc."', '".$freq."', '".$type."', '".$active."')";	
	
    // This is used for the on duplicate update
    $update_values[] = "desc=VALUES(desc),freq=VALUES(freq),type=VALUES(type),active=VALUES(active)";     
		
    }
    
// Insert the records    
    
$sql = "INSERT INTO tbl_tablename (num, desc, freq, type, active)
 VALUES ".implode(',', $values)." ON DUPLICATE KEY UPDATE ".implode(', ', $update_values);

$result = mysql_query($sql, $conn);  

And that’s it, there you have it. Thanks for stopping by once again and remember, share with others, after all, code should be free.