我是一个新手程序员,试图在世界上找到自己的路。我已经尝试将JSON数据解析为SQL语句以填充多个数据库表。我想遍历数组的每个维度,并拉出数组的特定部分以创建INSERT语句,我可以将其传递给MySQL。我不确定这是否是用一个JSON文件中的数据填充单独的表的最佳方法,但这是我唯一能想到的。 MySQL表是分开的,因此存在一个人的表,一个地址类型的表,一个地址的表,一个电话的表,一个电子邮件的表等。这是为了解释一个包含许多电话号码,电子邮件地址的人记录等等

我已经能够从外部URL解码JSON。这是使用print_r的代码和输出示例。

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

$jsondata = file_get_contents($json_string);

$data = json_decode($jsondata, TRUE);


1个记录样本:

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代码示例:

{"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\nOttawa, Ontario\nK1A 0A6",
           "tel": "613-992-1067"
         },
         { "type": "constituency",
           "fax": "506-452-4076",
           "postal": "23 Alison Blvd (Main Office)\nFredericton, New Brunswick\nE3C 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
   }
 }


您能提供的任何帮助将不胜感激。最近几天,我一直在梳理头发,以求弄清楚。

我曾尝试自定义以下代码以使其正常工作,但我一直无法达到最佳效果。请不要,此代码不引用我的数据或变量。我删除了对我无效的内容。我只是将其包括在内,以使您了解我尝试过的方法。

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


如果您能指出我能够从数组的任何级别解析出数据的方向,将不胜感激。

最好,

小号

最佳答案

AFAIK,不能一次插入到多个表中。此外,您需要保留数据完整性,因此相关表将具有正确的外键。

通常的想法是遍历数据,插入记录并记住插入的id,然后将它们写为相应的外键。

您遍历对象,将所有原始属性插入为字段,然后使用id获取mysql_last_insert_id,然后在保存办公室(或其详细信息)时将id作为其相关对象ID。

例如。我们有以下JSON。

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


然后,使用以下代码插入该数据:

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}') ");
    }
}


这是为了在表中为ID自动增加的情况。

当然,您需要在插入等之前验证并转义数据。

10-07 19:53
查看更多