Take the 2-minute tour ×

Let's say I have a server side script which generates JSON from a simple select on a table. The JSON is encoded in the 1st script.

I have no control over this 1st script, but am aware when the underlying database structure changes and when the JSON structure changes.

Script 2 uses CURL to get the .js file (contents) which contains the JSON, I can then decode into an array.

What I need to do then is store the data in another database.

My question is basically about automating this process and being able to create a table from an array when you don't know what the structure of the array is until it arrives.

Can it be done?

EDIT Added the JSON as it currently stands, but the point is it might change.

{"name": "Google",
 "homepage_url": "http://www.google.com",
 "blog_url": "",
 "blog_feed_url": "",
 "twitter_username": "",
 "category_code": "ecommerce",
 "tag_list": "retail-portal-online-shopping-markets",
 "alias_list": null,
 "image": null,
  [{"title": "Legal",
    "is_past": false,
     {"name": "TaylorWessing",
      "permalink": "taylorwessing"}}],
  [{"description": "European HQ",
    "address1": "",
    "address2": "",
    "zip_code": "",
    "city": "Brussels",
    "state_code": null,
    "country_code": "BEL",
    "latitude": null,
    "longitude": null}]}
share|improve this question
Er, sure. What are you stuck on in particular? Do you have any examples of the json arrays? –  Grim... Dec 23 '11 at 12:47

3 Answers 3

up vote 0 down vote accepted

I will answer next to the question but if you want to store JSON why not use a document database? CouchDB couch store your JSON "as is" without you having to do any processing.

share|improve this answer
Actually I'm working server-side. I won't be going to the client-side to manipulate the data. –  T9b Dec 23 '11 at 13:43
and? you can use CouchDB on server side. –  RageZ Dec 23 '11 at 13:44
sorry front page of Couch TL;DR ;) I will look into it, as it seems like it could be interesting. –  T9b Dec 23 '11 at 13:56
I didn't use CouchDB, because I was on mySQL. However the principle of just storing the whole row data in a single field was the answer, mainly because I then wanted to do a string search anywhere in the data. It would have been much more difficult to do across multiple fields. –  T9b Dec 29 '11 at 8:25

Simple code piece to create a mysql table and insert values from any JSON variable. This is a fast hack.. check field types etc :) There's probably better ways, but this one work and has already saved me hours in manual field-naming

This can be modified to create a relation table handling objects aswell. Now, it's made for array formed JSON stuff and not objects ie arrays in arrays.



            function JSON_to_table($j_obj, $tblName = "New_JSON_table_" . time()){
            $j_obj = json_decode($your_JSON_variable, true);
            if(!mysql_num_rows( mysql_query("SHOW TABLES LIKE '" . $tblName . "'"))){ 
                $cq = "CREATE TABLE ". $tblName ." (
                foreach($j_obj as $j_arr_key => $value){
                    $cq .= $j_arr_key . " VARCHAR(256),";
                $cq = substr_replace($cq,"",-1);
                $cq .= ")";
                mysql_query($cq) or die(mysql_error());

            $qi = "INSERT INTO $tblName (";
                    $qi .= $j_arr_key . ",";
                $qi = substr_replace($qi,"",-1);
            $qi .= ") VALUES (";
                    $qi .= "'" . mysql_real_escape_string($value) . "',";
            $qi .= ")";
            $result = mysql_query($qi) or die(mysql_error());

        return true;
share|improve this answer

Once you get the JSON you could use PHP's json_decode function to convert the JSON to a standard PHP object. http://php.net/manual/en/function.json-decode.php

You could then build your SQL query string by looping over the freshly created object using foreach. http://php.net/manual/en/control-structures.foreach.php

When you iterate through your object you'll probably have to do type checking to ensure that you are creating the correct kind of field for the database. For example use PHP's is_numeric function to make sure that you set that field as numeric instead of varchar. But that's up to you what to do there. You could just let it rip and make all the fields varchar.

You could also just store the JSON object as is, but I don't know if you just need to archive the data or if you need it for searching and reporting. If you do need to search/report on it then you'll have to do some kind of conversion. Or do what RageZ said and put in a Couch database. There's a few other choices out there too, like MongoDB. http://www.mongodb.org/ But if you don't have time to learn and install a No-SQL database then I'd stick with conversion.

share|improve this answer

Your Answer


