duanjiu6697 2012-03-18 18:52
浏览 64
已采纳

解析JSON以在PHP中创建SQL插入语句

I'm a newbie programmer trying to find my way in the world. I've got my hands on JSON data that I'm trying to parse out into SQL statements to populate multiple database tables. I would like to loop through each dimension of the array and pull out specific parts of it to create an INSERT statement I can just pass to MySQL. I'm not sure if this is the best way to populate separate tables with data from one JSON file but it's the only thing I can think of. MySQL tables are separated so there is a table for a person, a table for address type, a table for address, a table for phone, a table for email etc. This is to account for a person record having numerous phone numbers, email addresses etc.

I have been able to decode the JSON from an external URL. Here is the code and a sample of the output using print_r.

$json_string = 'http://....';

$jsondata = file_get_contents($json_string);

$data = json_decode($jsondata, TRUE);

1 Record Sample:

Array ( [objects] => Array ( [0] => Array ( [first_name] => Anthony [last_name] => Perruzza [name] => Anthony Perruzza [elected_office] => City councillor [url] => http://www.toronto.ca/councillors/perruzza1.htm [gender] => [extra] => Array ( ) [related] => Array ( [boundary_url] => /boundaries/toronto-wards/york-west-8/ [representative_set_url] => /representative-sets/toronto-city-council/ ) [source_url] => http://www.toronto.ca/councillors/perruzza1.htm [offices] => Array ( [0] => Array ( [tel] => 416-338-5335 ) ) [representative_set_name] => Toronto City Council [party_name] => [district_name] => York West (8) [email] => councillor_perruzza@toronto.ca [personal_url] => [photo_url] => ) ) [meta] => Array ( [next] => /representatives/?limit=1&offset=1 [total_count] => 1059 [previous] => [limit] => 1 [offset] => 0 ) )

JSON Code Sample:

{"objects": [
    {"first_name": "Keith",
     "last_name": "Ashfield",
     "name": "Keith Ashfield",
     "elected_office": "MP",
     "url": "http://www.parl.gc.ca/MembersOfParliament/ProfileMP.aspx?Key=170143&Language=E",
     "gender": "",
     "extra": {},
     "related": {
          "boundary_url": "/boundaries/federal-electoral-districts/13003/", 
          "representative_set_url": "/representative-sets/house-of-commons/"
     },
     "source_url": "http://www.parl.gc.ca/MembersOfParliament/MainMPsCompleteList.aspx?TimePeriod=Current&Language=E",
     "offices": [
         { "type": "legislature",
           "fax": "613-996-9955",
           "postal": "House of Commons
Ottawa, Ontario
K1A 0A6",
           "tel": "613-992-1067"
         },
         { "type": "constituency",
           "fax": "506-452-4076",
           "postal": "23 Alison Blvd (Main Office)
Fredericton, New Brunswick
E3C 2N5",
           "tel": "506-452-4110"
         }
     ],
     "representative_set_name": "House of Commons",
     "party_name": "Conservative",
     "district_name": "Fredericton",
     "email": "keith.ashfield@parl.gc.ca",
     "personal_url": "",
     "photo_url": "http://www.parl.gc.ca/MembersOfParliament/Images/OfficialMPPhotos/41/AshfieldKeith_CPC.jpg"
    }
 ],
  "meta": {
       "next": "/representatives/house-of-commons/?limit=1&offset=1",
       "total_count": 307,
       "previous": null,
       "limit": 1,
       "offset": 0
   }
 }

Any help you can offer would be greatly appreciated. I've been pulling my hair out for the last few days trying to figure it out.

I've tried customizing code like the following to make it work but I haven't been able to hit the sweet spot. Please not, this code doesn't reference my data or variables. I deleted what didn't work for me. I'm just including it to give you an idea what I've tried.

foreach ($data as $item) {
    echo $item->{'first_name'} . "<br/>";
    echo $item->{'last_name'};
}

If you could point me in the direction of being able to parse out data from any level of the array it would be greatly appreciated.

Best,

S

  • 写回答

2条回答 默认 最新

  • dongyingdao8867 2012-03-18 21:21
    关注

    AFAIK, it is not possible to insert into several tables with one insert. Moreover, you need to preserve data integrity, so related tables would have right foreign keys.

    The general idea is to iterate through the data, insert records and remember inserted ids, then write them as corresponding foreign keys.

    You iterate thru your objects, insert all primitive properties as fields, then get an id using mysql_last_insert_id, then while saving offices (or their details) put that id as their related object id.

    E.g. we have the following JSON.

    {"authors": [
        {"first_name": "John",
         "last_name": "Doe",
         "books": [{
              "title": "Capture the flag",
              "ISBN": "123-456789-12345",
         },{
              "title": "Deathmatch",
              "ISBN": "123-456789-12346",
         }]
    ]}
    

    Then we insert that data with the following code:

    foreach ($data as $author) {
        mysql_query("INSERT INTO `authors` (`first_name`, `last_name`), VALUES('{$author->first_name}', '{$author->last_name}') ");
        $author_id = mysql_last_insert_id();
        foreach ($author->books as $book) {
            mysql_query("INSERT INTO `books` (`title`, `isbn`, `author_id`), VALUES('{$book->title}', '{$book->isbn}', '{$author_id}') ");
        }
    }
    

    This is for case you have auto-increment for id's in tables.

    Of course, you'll need to validate and escape data before insertion etc.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥100 关于使用MATLAB中copularnd函数的问题
  • ¥20 在虚拟机的pycharm上
  • ¥15 jupyterthemes 设置完毕后没有效果
  • ¥15 matlab图像高斯低通滤波
  • ¥15 针对曲面部件的制孔路径规划,大家有什么思路吗
  • ¥15 钢筋实图交点识别,机器视觉代码
  • ¥15 如何在Linux系统中,但是在window系统上idea里面可以正常运行?(相关搜索:jar包)
  • ¥50 400g qsfp 光模块iphy方案
  • ¥15 两块ADC0804用proteus仿真时,出现异常
  • ¥15 关于风控系统,如何去选择