php json mysql嵌套_使用php將嵌套的JSON導入MySQL

1

Merry Christmas, mkrl. We aren't really supposed to write new code for anyone, we are only meant to fix existing coding attempts. However, today I wanted to learn more about pdo by doing and your question just happen to be the beneficiary. Below is a fully functional transactional pdo process for your exact case. I've tested on my server with 3 MyISAM tables that match your structure. Now, it is possible that you are not into pdo at the moment; if not, I hope my offering will compel you to try something new and modernize the way you query your database.

聖誕快樂,mkrl。我們並不是真的應該為任何人編寫新的代碼，我們只是要修復現有的代碼嘗試。然而，今天我想通過做來了解更多關於pdo的知識，你的問題正好是受益者。下面是一個完整的事務處理pdo過程。我在服務器上測試了與您的結構匹配的my3 isam表。現在，有可能你現在還沒有進入pdo;如果沒有，我希望我的產品將迫使您嘗試一些新的東西，並使您查詢數據庫的方式現代化。

$json='[

{

"targetgroup": "Staff",

"plan": "this field just exists and should be ignored in database",

"budgetlevel": "Government",

"spots": 5,

"edutype": "Bachelor",

"qualilevel": "Specialist",

"speciality": "Mathematician",

"qualification": "Finished",

"faculty": "Applied mathematics",

"institute": "this field is sometimes empty in input data",

"eduform": "Full-time",

"profiles": [

"Jr. Arithmetic manager"

],

"entrancetests": [

{

"subject": "math",

"typeoftest": "GOV",

"minscore": "37",

"ratingtype": "out of 100"

},

{

"subject": "language",

"typeoftest": "GOV",

"minscore": "27",

"ratingtype": "out of 100"

},

{

"subject": "physics",

"typeoftest": "GOV",

"minscore": "40",

"ratingtype": "out of 100"

}

]

},

{

"targetgroup": "Educational workers",

"plan": "fridge",

"budgetlevel": "Legacy",

"spots": 26,

"edutype": "Bachelor",

"qualilevel": "Master",

"speciality": "Data analysis",

"qualification": "Finished",

"faculty": "Machine learning mathematics",

"institute": "",

"eduform": "Full-time",

"profiles": [

"Head counting manager"

],

"entrancetests": [

{

"subject": "Discrete mathematics",

"typeoftest": "GOV",

"minscore": "32",

"ratingtype": "Out of 100"

},

{

"subject": "Algorythm theory",

"typeoftest": "GOV",

"minscore": "51",

"ratingtype": "Out of 100"

},

{

"subject": "Advanced exception catching",

"typeoftest": "GOV",

"minscore": "56",

"ratingtype": "Out of 100"

}

]

}

]';

$db=new PDO("mysql:host=yourhost;dbname=yourdbname;charset=utf8","username","password");

try{

$db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION); // this will deny subsequent queries from being executed if there is an error and permit exception handle at the bottom

$db->beginTransaction();

// dep

$dep_cols=array("targetgroup","budgetlevel","spots",

"edutype","qualilevel","speciality","qualification",

"faculty","institute","eduform"); // declare columns

$dep_keys=array_map(function($v){return ":$v";},$dep_cols); // build :keys

$dep_cols=array_combine($dep_keys,$dep_cols); // assign :keys

var_export($dep_cols);

$dep_query="INSERT INTO `dep` (`".implode('`,`',$dep_cols)."`)"; // list columns as csv

$dep_query.=" VALUES (".implode(',',array_keys($dep_cols)).");";

echo "

$dep_query

";

$stmt_add_dep=$db->prepare($dep_query);

// profile

$profile_cols=array('name');

$profile_query="INSERT INTO `profile` (`id`,`".implode('`,`',$profile_cols)."`)"; // list columns as csv

$profile_query.=" VALUES (LAST_INSERT_ID(),".implode(',',array_fill(0,sizeof($profile_cols),"?")).");";

echo "

$profile_query

";

// entrancetests

$entrance_cols=array('subject','typeoftest','minscore','ratingtype'); // declare columns

$entrance_keys=array_map(function($v){return ":$v";},$entrance_cols); // build :keys

$entrance_cols=array_combine($entrance_keys,$entrance_cols); // assign :keys

var_export($entrance_cols);

$entrance_query="INSERT INTO `entrancetests` (`id`,`".implode('`,`',$entrance_cols)."`)"; // list columns as csv

$entrance_query.=" VALUES (LAST_INSERT_ID(),".implode(',',array_keys($entrance_cols)).");";

echo "

$entrance_query

";

$stmt_add_entrance=$db->prepare($entrance_query);

foreach(json_decode($json) as $d){

foreach($dep_cols as $k=>$v){

$stmt_add_dep->bindValue($k,(property_exists($d,$v)?$d->$v:""));

echo "

$k => {$d->$v}

";

}

$stmt_add_dep->execute();

echo "

Dep Affected Rows: ",$stmt_add_dep->rowCount(),"

";

$stmt_add_profile=$db->prepare($profile_query);

foreach($d->profiles as $k=>$v){

$stmt_add_profile->bindValue($k+1,$v);

echo "

",$k+1," => $v

";

}

$stmt_add_profile->execute();

echo "

Profile Affected Rows: ",$stmt_add_profile->rowCount(),"

";

foreach($d->entrancetests as $o){

foreach($entrance_cols as $k=>$v){

$stmt_add_entrance->bindValue($k,(property_exists($o,$v)?$o->$v:""));

echo "

$k => {$o->$v}

";

}

}

$stmt_add_entrance->execute();

echo "

Entrance Affected Rows: ",$stmt_add_entrance->rowCount(),"

";

}

// $db->commit(); // Only use with InnoDB tables. MyISAM is auto-commit

}

catch(PDOException $e){

// $db->rollBack(); // Only works if InnoDB table. If MyISAM table, it doesn't rollback.

echo "Error message: {$e->getMessage()}. File: {$e->getFile()}. Line: {$e->getLine()}";

// do not show these error messages to users when you go live

}

As is, this script will stop executing subsequent queries if a query has an error.

同樣，如果查詢出現錯誤，此腳本將停止執行后續查詢。

I know that there are many SO users with expertise in pdo processes. If anyone finds any issues or has recommendations that can refine my code, please curb the urge to downvote me; rather just leave me a constructive comment that explains specifically what isn't right/good and possibly how to fix it. Remember the purpose of SO is to help people, not elevate ourselves by putting others down. I am always willing to edit my answer so that future SO readers have access to the best code.

我知道有很多在pdo過程中具有專業知識的用戶。如果有人發現任何問題或建議可以改進我的代碼，請抑制對我投反對票的沖動;而是給我留下一個建設性的評論，具體地解釋什么是對的/不好的，以及如何修復它。記住這樣做的目的是幫助別人，而不是通過貶低別人來提升自己。我總是願意編輯我的答案，以便將來讀者能夠訪問到最好的代碼。