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.foscode.com | because code should be free

5 thoughts on “JSON Array Single MySQL Insert and Update

  1. @phpci Thank you for stopping by, json is not a database, it is a data-interchange format. It is a collection of name/values and as explained on their site. Here is the gist of it, according to json.org:

    JSON is built on two structures:
    A collection of name/value pairs. In various languages, this is realized as an object, record, struct, dictionary, hash table, keyed list, or associative array.
    An ordered list of values. In most languages, this is realized as an array, vector, list, or sequence.

    Feel free to follow the link and get more detailed information. If you are asking how to obtain the data from mysql, then you can read one of my other posts with detailed instructions. This tutorial in particular shows you how to connect to mysql, gather the data and encode it into json for further use.

  2. Hiya great web page! Man. Outstanding. Superb. I’m going to search for your web blog plus take the feeds also? Now i’m pleased to search for a lot of practical information the following inside the set up, you want grow a lot more techniques with this regard, thanks for revealing.

Leave a Reply

Your email address will not be published. Required fields are marked *

*


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>