user3511007 user3511007 - 2 months ago 14
MySQL Question

import json to mysql

I have this json file

{
"5927803e892d4c459f2992a3a1ac2ead.16": {
"lat": 57.532696,
"lng": 9.949331
},
"e25fe65e4fe4468db1361213ba1a376b.16": {
"lat": 57.501783,
"lng": 9.947415,
"name": "Vidstrup Train Station",
"disabled": false
},
"dcd8b44bd0f3478cb2d99c8d5115ba50.16": {
"lat": 57.480323,
"lng": 9.968769,
"name": "Vellingshøj Station",
"disabled": false
}
}


I have a working php file to import it
but I really would like the ID also
my problem is that in the json file
there are no name for it like "id":""

<?php
//
$connect = mysqli_connect("localhost", "xxxxx", "xxxxxx", "Rails");
$filename = "location-export.json";
$data = file_get_contents($filename);
$array = json_decode($data, true);
foreach($array as $row)
{
$sql = "INSERT INTO location(location_lat, location_lng, location_name, location_mission) VALUES ('".$row["lat"]."', '".$row["lng"]."','".$row["name"]."', '".$row["mission"]."')";
mysqli_query($connect, $sql);
}
echo "Data Inserted";
?>

Answer

Change your code as follows

 foreach($array as $id => $row)  
 {  
      $sql = "INSERT INTO location(id, location_lat, location_lng, location_name, location_mission) VALUES ('{$id}','".$row["lat"]."', '".$row["lng"]."','".$row["name"]."', '".$row["mission"]."')";       
      mysqli_query($connect, $sql);       
 }  
 echo "Data Inserted";  
 ?> 

but of course this is totally insecure (and possibly slower) than using prepared statements.

You should try

$stmt = mysql_prepare($link, "INSERT INTO location(id, location_lat, location_lng, location_name, location_mission) VALUES(?,?,?,?)");

 foreach($array as $id => $row)  
 {  
        mysqli_stmt_bind_param($stmt,"ssss", $id,  $row["lat"], ....);
        mysqli_stmt_execute($stmt);


 }