drq22639
2011-12-23 12:43 浏览 229

我可以从JSON动态创建mySQL表吗?

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,
 "products":
  [],
 "relationships":
  [],
 "competitions":
  [],
 "providerships":
  [{"title": "Legal",
    "is_past": false,
    "provider":
     {"name": "TaylorWessing",
      "permalink": "taylorwessing"}}],
 "offices":
  [{"description": "European HQ",
    "address1": "",
    "address2": "",
    "zip_code": "",
    "city": "Brussels",
    "state_code": null,
    "country_code": "BEL",
    "latitude": null,
    "longitude": null}]}
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

3条回答 默认 最新

  • 已采纳
    dongluo6343 dongluo6343 2011-12-23 12:45

    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.

    点赞 评论 复制链接分享
  • dqotv26286 dqotv26286 2011-12-23 15:02

    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.

    点赞 评论 复制链接分享
  • douan8473 douan8473 2013-02-18 11: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.

    <?php
    
        JSON_to_table($place_your_JSON_var_here_please);
    
                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 ." (
                    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,";
                    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 (";
                reset($j_obj);
                    foreach($j_obj as $j_arr_key => $value){
                        $qi .= $j_arr_key . ",";
                    }
                    $qi = substr_replace($qi,"",-1);
                $qi .= ") VALUES (";
                reset($j_obj);
                    foreach($j_obj as $j_arr_key => $value){
                        $qi .= "'" . mysql_real_escape_string($value) . "',";
                    }
                $qi = substr_replace($qi,"",-1);
                $qi .= ")";
                $result = mysql_query($qi) or die(mysql_error());
    
            return true;
                }
    ?>
    
    点赞 评论 复制链接分享

相关推荐