Trying to read json and write it in sql with php

Question

Im trying to read json from a file or url,and then write it in sql using php

i have made this code:

<?php
    //connect to mysql db
$username = "test";
$password = "test";
$database = "wordpressdb";
$host = "localhost";

$conn = mysqli_connect($host, $username, $password, $database)
    if($conn === false){
        die("FAIL" . mysqli_connect_error());
    }

    //read the json file contents
    $jsondata = file_get_contents('test.json');


//{"cod":"200","calctime":0.3107,"cnt":15,"list":[{"id":2208791,"name":"Yafran","coord":{"lon":12.52859,"lat":32.06329},"main":{"temp":9.68,"temp_min":9.681,"temp_max":9.681,"pressure":961.02,"sea_level":1036.82,"grnd_level":961.02,"humidity":85},"dt":1485784982,"wind":{"speed":3.96,"deg":356.5},"rain":{"3h":0.255},"clouds":{"all":88},"weather":[{"id":500,"main":"Rain","description":"light rain","icon":"10d"}]}]}




    //convert json object to php associative array
    $data = json_decode($jsondata, true);

    //get the employee details




    $cod = $data['cod'];
    $calctime = $data['calctime'];
    $cnt = $data['cnt'];
    $id = $data['list']['id'];
    $name = $data['list']['name'];
    $lon = $data['list']['coord']['lon'];
    $lat = $data['list']['coord']['lat'];
    $temp = $data['list']['main']['temp'];
    $min = $data['list']['main']['temp_min'];
    $max = $data['list']['main']['temp_max'];
    $pressure = $data['list']['main']['pressure'];
    $level = $data['list']['main']['sea_level'];
    $level2= $data['list']['main']['grnd_level'];
    $humidity = $data['list']['main']['humidity'];
    $dt = $data['list']['dt'];
    $speed = $data['list']['wind']['speed'];
    $deg = $data['list']['wind']['deg'];
    $h = $data['list']['rain']['3h'];
    $all = $data['list']['clouds']['all'];
    $id2 = $data['list']['weather']['id'];
    $main = $data['list']['weather']['main'];
    $description = $data['list']['weather']['description'];
    $icon = $data['list']['weather']['icon'];

    //insert into mysql table
    $sql = "INSERT INTO test(cod, calctime, cnt, id, name, lon, lat, temp, temp_min, temp_max, pressure, sea_level, grnd_level, humidity, dt, speed, deg, 3h, all, id, main, descriptio, icon)
    VALUES('$cod', '$calctime', '$cnt', '$id', '$name', '$lon', '$lat', '$temp', '$min', '$max', '$pressure', '$level', '$level2', '$humidity', '$dt', '$speed', '$deg', '$h', '$deg', '$all', '$id2', '$main', '$description', '$icon')";
   if(mysqli_query($link, $sql)){

    echo "Records inserted successfully.";

} else{

    echo "ERROR: Could not able to execute $sql. " . mysqli_error($conn);

}
?>

But the code is not working :( Im new in programing,and i want to know how to make this(Json to php to mysql). can someone help me fix the code? I'm trying to fix it from 5 days.

What does "not working" mean? What error messages are you getting? See http://stackoverflow.com/help/how-to-ask — miken32, Mar 13 '17 at 22:20
Do try and avoid getting in the habit of doing things like `if ($x === false)` when `if (!$x)` would suffice. Extra verbosity leads to confusion because your comparison is needlessly specific. — tadman, Mar 13 '17 at 22:27
**WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST`, `$_GET` or **any** user data directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman, Mar 13 '17 at 22:27

score 0 · Answer 1 · answered Mar 13 '17 at 22:23

0

$link is not defined. It should be $conn in your case.

answered Mar 13 '17 at 22:23

CodeGodie

11,757
5
30
61

I fix this.But there is still a problem.And i dont see the errors ... when i run the script. http://i.imgur.com/39yzPmi.png – Georgi Bangeev Mar 13 '17 at 22:36
you probably have php's error reporting turned off. Turn it on: http://stackoverflow.com/questions/1053424/how-do-i-get-php-errors-to-display – CodeGodie Mar 13 '17 at 23:49

score 0 · Answer 2 · answered Mar 14 '17 at 03:38

  <?php
//connect to mysql db
 $username = "test";
 $password = "test";
 $database = "wordpressdb";
 $host = "localhost";

$conn = mysqli_connect($host, $username, $password, $database);
 if($conn === false){
    die("FAIL" . mysqli_connect_error());
}

//read the json file contents
$jsondata = file_get_contents('test.json');



//convert json object to php associative array
$data = json_decode($jsondata, true);

//get the employee details

 $cod = $data['cod'];
 $calctime = $data['calctime'];
 $cnt = $data['cnt'];
 $id = $data['list'][0]['id'];
 $name = $data['list'][0]['name'];
 $lon = $data['list'][0]['coord']['lon'];
 $lat = $data['list'][0]['coord']['lat'];
 $temp = $data['list'][0]['main']['temp'];
 $min = $data['list'][0]['main']['temp_min'];
 $max = $data['list'][0]['main']['temp_max'];
 $pressure = $data['list'][0]['main']['pressure'];
 $level = $data['list'][0]['main']['sea_level'];
 $level2= $data['list'][0]['main']['grnd_level'];
 $humidity = $data['list'][0]['main']['humidity'];
 $dt = $data['list'][0]['dt'];
 $speed = $data['list'][0]['wind']['speed'];
 $deg = $data['list'][0]['wind']['deg'];
 $h = $data['list'][0]['rain']['3h'];
 $all = $data['list'][0]['clouds']['all'];
 $id2 = $data['list'][0]['weather'][0]['id'];
 $main = $data['list'][0]['weather'][0]['main'];
 $description = $data['list'][0]['weather'][0]['description'];
 $icon = $data['list'][0]['weather'][0]['icon'];

  //insert into mysql table
  $sql = "INSERT INTO  `test`
          (
          `cod`,`calctime`,`cnt`,`master_id`,
          `name`,`lon`,`lat`,`temp`,`temp_min`,
          `temp_max`,`pressure`,`sea_level`,`grnd_level`,
          `humidity`,`dt`,`speed`,`deg`,`3h`,`all`,`id`,
          `main`,`descriptio`,`icon`
          )
        VALUES(
               '$cod', '$calctime', '$cnt', '$id', 
               '$name', '$lon', '$lat', '$temp', '$min',
               '$max', '$pressure', '$level', '$level2',
               '$humidity', '$dt', '$speed', '$deg', '$h',
               '$all', '$id2', '$main', '$description', '$icon'
              )";
   if(mysqli_query($conn, $sql)){
    echo "Records inserted successfully.";
   }else{
      echo "ERROR: Could not able to execute $sql. " . mysqli_error($conn);
     }
?>

you're using $link variable you must use $conn var you forget semicolon in the line of connection you're trying to insert 24 values and the table has only 23 cols you can't rename two cols with the same name,change the first id to master_id in the database table

Trying to read json and write it in sql with php

2 Answers2