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

Question

{
  {
    "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."
    }
  }    
}

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());
}

?> 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.

Answer 1

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(";

", $arrSQL);

        var_dump($arrSQL);
        var_dump($strSQL);

Test it out HERE.