我是一个新手程序员,试图在世界上找到自己的路。我已经尝试将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自动增加的情况。
当然,您需要在插入等之前验证并转义数据。