how to insert multiple nested json object into mysql using PHP foreach loop?

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.

can you please share the php code by which you are trying to insert to database ? — Manish
– Manish, Commented Jun 25, 2016 at 11:43

Poiz · Accepted Answer · 2016-06-25 12:19:31Z

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

Test it out HERE.

Maybe I've misunderstood but you appear to be thinking of executing one query per row.

Collectives™ on Stack Overflow

how to insert multiple nested json object into mysql using PHP foreach loop?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related