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:


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

7 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. Hello there! This blog post could not be written much better!
    Looking at this post reminds me of my previous roommate! He continually kept preaching
    about this. I will forward this information to him.
    Pretty sure he will have a great read. Many thanks for sharing!

  3. Hi, Thanks for nice tutorial.

    I have a problem with the update bit whereas the code does not update the records on the database but instead it is creating new rows with the same content.

    NB: Used the same code as you outline above no changes at all.

    Any other way I could handle this?

  4. @Night Kenya Thanks for the comment. It is an insert on duplicate key update, so if the record doesn’t exist, or you haven’t created the table and set the field to be unique, then it’ll create a new record

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>