如何使用PHP foreach循环将多个嵌套的json对象插入mysql中? [英] how to insert multiple nested json object into mysql using PHP foreach loop?
本文介绍了如何使用PHP foreach循环将多个嵌套的json对象插入mysql中?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
{
{
"empid":805475,
"personal":{
"name":"ABC",
"gender":"Male",
"age":28,
"address":{
"streetaddress":"Subhash Nagar",
"city":"Agra",
"state":"Uttarpradesh",
"postalcode":209111
}
},
"profile":{
"designation":"Coder",
"department":" Software dev."
}
},
{
"empid":814475,
"personal":{
"name":"PQR",
"gender":"Male",
"age":18,
"address":{
"streetaddress":"Subhash Nagar",
"city":"Delhi",
"state":"Delhi",
"postalcode":110022
}
},
"profile":{
"designation":"Coder",
"department":" Software dev."
}
},
{
"empid":805795,
"personal":{
"name":"MNP",
"gender":"Male",
"age":38,
"address":{
"streetaddress":"Subhash Nagar",
"city":"Meerut",
"state":"Uttarpradesh",
"postalcode":209111
}
},
"profile":{
"designation":"Coder",
"department":" Software dev."
}
},
{
"empid":805197,
"personal":{
"name":"AMN",
"gender":"Male",
"age":29,
"address":{
"streetaddress":"Subhash Nagar",
"city":"Mathura",
"state":"Uttarpradesh",
"postalcode":546125
}
},
"profile":{
"designation":"Coder",
"department":" Software dev."
}
}
}
这是我想使用PHP保存到Mysql中的json数据. 这是我的代码，仅适用于单个json对象(例如-仅适用于ABC)，但显示多个对象的错误. PHP代码:
This is the json data i wanted to save into Mysql using PHP. Here is my code that is working fine for only a single json object (for e.g.- ABC only) but shows error for more than one object. PHP Code:
//convert json object to php associative array
$data = json_decode($json, true);
//get the employee details
$id = $data['empid'];
$name = $data['personal']['name'];
$gender = $data['personal']['gender'];
$age = $data['personal']['age'];
$streetaddress = $data['personal']['address']['streetaddress'];
$city = $data['personal']['address']['city'];
$state = $data['personal']['address']['state'];
$postalcode = $data['personal']['address']['postalcode'];
$designation = $data['profile']['designation'];
$department = $data['profile']['department'];
//insert into mysql table
$sql = "INSERT INTO tbl_emp(empid, empname, gender, age, streetaddress, city, state, postalcode, designation, department)
VALUES('$id', '$name', '$gender', '$age', '$streetaddress', '$city', '$state', '$postalcode', '$designation', '$department')";
if(!mysql_query($sql,$con))
{
die('Error : ' . mysql_error());
}
?> 因此，这是适用于一个对象的php代码，但我不知道如何将整个json文件数据立即插入到mysql DB中.
?> so this is the php code that is working for one object but i donot know how to insert the whole json file data at once into mysql DB.
推荐答案
您的桌子怎么样?您是否要根据JSON数据构建SQL查询字符串?在下面的代码中，有许多假设可能符合或不符合您的意图，但仍然对初学者有效:
How is your table? Do you wish to build an SQL Query String from the JSON Data? In the Code below, there are a lot of assumptions which may or may not fit with your intentions but still are valid for starters:
<?php
$jsonData = '[
{
"empid" : 805475,
"personal" : {
"name" : "ABC",
"gender" : "Male",
"age" : 28,
"address" : {
"streetaddress" : "Subhash Nagar",
"city" : "Agra",
"state" : "Uttarpradesh",
"postalcode" : 209111
}
},
"profile": {
"designation" : "Coder",
"department" : " Software dev."
}
},
{
"empid" : 814475,
"personal" :{
"name" : "PQR",
"gender" : "Male",
"age" : 18,
"address" : {
"streetaddress" : "Subhash Nagar",
"city" : "Delhi",
"state" : "Delhi",
"postalcode" : 110022
}
},
"profile" :{
"designation" : "Coder",
"department" : " Software dev."
}
},
{
"empid" : 805795,
"personal" : {
"name" : "MNP",
"gender" : "Male",
"age" : 38,
"address" : {
"streetaddress":"Subhash Nagar",
"city" : "Meerut",
"state" : "Uttarpradesh",
"postalcode" : 209111
}
},
"profile" :{
"designation" : "Coder",
"department" : " Software dev."
}
},
{
"empid" :805197,
"personal" : {
"name" : "AMN",
"gender" : "Male",
"age" : 29,
"address" : {
"streetaddress" : "Subhash Nagar",
"city" : "Mathura",
"state" : "Uttarpradesh",
"postalcode" : 546125
}
},
"profile" : {
"designation" : "Coder",
"department" : " Software dev."
}
}
]';
// WE ASSUME YOUR TABLE-NAME IS my_table JUST FOR DEMONSTRATION:
$tblName = "`my_table`";
// CONVERT JSON DATA TO NATIVE PHP OBJECT
$objJson = json_decode($jsonData);
// CREATE A VARIABLE TO HOLD THE SQL (QUERY STRING)
$arrSQL = array();
foreach ($objJson as $key => $data) {
$tmpSQL = "INSERT INTO " . $tblName . "(empid, name, gender, age, streetaddress, city, state, postalcode, designation, department)";
$tmpSQL .= " VALUES('{$data->empid}', '{$data->personal->name}', '{$data->personal->gender}', '{$data->personal->age}', ";
$tmpSQL .= "'{$data->personal->address->streetaddress}', '{$data->personal->address->city}', '{$data->personal->address->state}', '{$data->personal->address->postalcode}', ";
$tmpSQL .= "'{$data->profile->designation}', '{$data->profile->department}' )";
$arrSQL[] = $tmpSQL;
}
// NOW YOU CAN JUST USE IMPLODE TO CREATE THE STRING EQUIVALENT OF THE SQL QUERY:
$strSQL = implode(";\n\n", $arrSQL);
var_dump($arrSQL);
var_dump($strSQL);
对其进行测试此处.
这篇关于如何使用PHP foreach循环将多个嵌套的json对象插入mysql中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
