Insert data from json to mysql table from php

Question

im trying to insert data into a table from a json file but the rows gets 0. not the value from json

JSON code:

{
"posts": [{
    "dr_DeviceID": "323",
    "dr_UserLocalLat": "38.7482572",
    "dr_UserLocalLong": " -9.1847516"
}]
}

$connection = mysql_connect("localhost", "***", "!*****!");
if (!$connection)
{
    die('PHP Mysql database connection could not connect : ' . mysql_error());
} 
$db_selected = mysql_select_db("*****", $connection);


$result=mysql_query("SELECT * FROM $tbl_name wHERE ad_IMEI=ad_IMEI ");
$i=0;
while($row=mysql_fetch_array($result)) { 
$response[$i]['dr_DeviceID']  = $row['ad_IDDevice']; 
$response[$i]['dr_UserLocalLat']= $row['user_location_lat'];
$response[$i]['dr_UserLocalLong']= $row['user_location_long'];
$data['posts'][$i] = $response[$i];
$i=$i+2;}
$json_string = json_encode($data);
$file = 'select.json';
file_put_contents($file, $json_string);

$jsondata = file_get_contents('select.json');
$obj = json_decode($jsondata, true);
$id = $obj['posts']['dr_DeviceID'];
$dr_UserLocalLat = $obj['posts']['dr_UserLocalLat'];
$dr_UserLocalLong = $obj['posts']['dr_UserLocalLong'];
$sqlj = "INSERT INTO $tbl_name1 (dr_DeviceID, dr_UserLocalLat, dr_UserLocalLong) VALUES('$dr_DeviceID', '$dr_UserLocalLat', '$dr_UserLocalLong')";
$result=mysql_query($sqlj,$connection);

if i make this $id = $obj['posts'][0]['dr_DeviceID']; it works fine just for the 1 one , but i want to reach them all — Kevin Dias, Mar 21 '16 at 16:33
use `foreach( $obj['posts'] as $row ) { $row['dr_DeviceID'] ... }` — fusion3k, Mar 21 '16 at 16:34
Note - php's `mysql` library is deprecated; it is recommended to use `mysqli` instead (both libraries are available by default) - see [this question](http://stackoverflow.com/questions/8891443/when-should-i-use-mysqli-instead-of-mysql) for more info — nthall, Mar 21 '16 at 16:52

score 2 · Accepted Answer · answered Mar 21 '16 at 16:46

2

The problem is that you're trying to access an array of objects as if it was a single one. With this line here $data['posts'][$i] = $response[$i]; you add an item to the $data['posts'] array. If your result had more than one row, the json example you've left above would be

{
 "posts": [{
   "dr_DeviceID": "323",
   "dr_UserLocalLat": "38.7482572",
   "dr_UserLocalLong": " -9.1847516"
  },
  {
   "dr_DeviceID": "324",
   "dr_UserLocalLat": "39.7482572",
   "dr_UserLocalLong": " -19.1847516"
  }]
}

So, when you decode your json afterwards, you get an array of objects. To access every item in the array, you need some loop cycle. Otherwise, to get the first item from the json, you would need to do
$obj['posts'][0]['dr_UserLocalLat'], instead of $obj['posts']['dr_UserLocalLat'].

answered Mar 21 '16 at 16:46

trajchevska

942
7
13

i making a foreach but i have the same issue ` foreach ($obj['posts'] as $row ){ $query1 = "INSERT INTO number (dr_DeviceID,dr_UserLocalLat,dr_UserLocalLong) VALUES ('". $row['ad_IDDevice']."','". $row['user_location_lat']."','". $row['user_location_long']."');"; $result=mysql_query($query1,$connection); }` – Kevin Dias Mar 21 '16 at 16:59
I believe it should be $row['dr_DeviceID'] instead of $row['ad_IDDevice'], $row['dr_UserLocalLat'] instead of $row['user_location_lat'] and $row['dr_UserLocalLong'] instead of $row['user_location_long']. Try you var_dump-ing $obj['post'] before the foreach. What does it print? – trajchevska Mar 21 '16 at 18:59

Insert data from json to mysql table from php

1 Answers1