gpt4 book ai didi

php - 如何使用 PHP foreach 循环将多个嵌套的 json 对象插入到 mysql 中?

转载 作者:行者123 更新时间:2023-11-29 20:51:16 24 4
gpt4 key购买 nike

{
{
"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 代码:

//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。

最佳答案

你的 table 怎么样?您希望从 JSON 数据构建 SQL 查询字符串吗?在下面的代码中,有很多假设可能符合也可能不符合您的意图,但对于初学者来说仍然有效:

    <?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);

测试一下 HERE .

关于php - 如何使用 PHP foreach 循环将多个嵌套的 json 对象插入到 mysql 中?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/38028092/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com