Write a PHP script to convert JSON to PHP array to store in MySQL

Question

From a JSON array I want to create a PHP code and write it to MySQL. How can I do this? Below is my JSON array:

{  
   "school1":[  
      {  
         "name":"aaa Universe",
         "url":"http:\/\/aaa_Universe.com\/" 
      },
      {  
        "name":"bbb Universe",
         "url":"http:\/\/bbb_Universe.com\/" 
      }
   ],
   ....................................
   ....................................
   "school4":[  
      {  
          "name":"ggg Universe",
         "url":"http:\/\/ggg_Universe.com\/" 
      },

      {  
          "name":"hhh Universe",
         "url":"http:\/\/hhh_Universe.com\/" 
      }
   ]
}

I have written below PHP script to get expected result. Could you suggest other way:

$data = file_get_contents ("list.json");
$json = json_decode($data, true);
foreach ($json as $key => $value) {
    if (!is_array($value)) {
        echo $key . '=>' . $value . '<br/>';
    } else {
        foreach ($value as $key => $val) {
            echo $key . '=>' . $val . '<br/>';
        }
    }
}

Answer 1

When you are generating a dynamic query, PHP Data Objects (PDO) is the method you should use in nearly every case. PDO prevents escape characters from being a security threat or causing bugs. Here is a link.

That said, in your particular case, I take it that you only need a quick script to generate a query. I am assuming the "school1", "school2".... are the foreign keys of the table. I'll use the function addslashes to prevent errors on the escape characters instead of the PDO.

$data = file_get_contents ("list.json");
$json = json_decode($data, true);

$statement = "";
foreach ($json as $school => $schools) {        
    if (count($schools) > 0) {
        foreach($schools as $i => $schoolInfo){
            if($statement == ""){
                $statement .= "INSERT INTO dataBase.table (school,name,url) VALUES";
                $statement .= " ('".addslashes($school)."', '".addslashes($schoolInfo['name'])."','" . addslashes($schoolInfo['url']) ."' )";
            }else{
                $statement .= ",   ('".addslashes($school)."', '".addslashes($schoolInfo['name'])."','" . addslashes($schoolInfo['url']) ."' )";        
            }
        }
    }
} 

echo $statement;