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
– Kevin Dias, Commented Mar 21, 2016 at 16:33
use foreach( $obj['posts'] as $row ) { $row['dr_DeviceID'] ... } — fusion3k
– fusion3k, Commented Mar 21, 2016 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 for more info — nthall
– nthall, Commented Mar 21, 2016 at 16:52

trajchevska · Accepted Answer · 2016-03-21 16:46:13Z

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, 2016 at 16:46

trajchevska

9527 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kevin Dias Over a year ago

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

trajchevska Over a year ago

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?

Collectives™ on Stack Overflow

Insert data from json to mysql table from php

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related