当前位置: 动力学知识库 > 问答 > 编程问答 >

php - Insert json array data from url into mysql

问题描述:

I'm trying to pass JSON data from a live url (power API) into a mysql database on a daily basis. Here's my code:

//read the json file contents

$jsondata = file_get_contents('http://ws.meteocontrol.de/api/sites/P9JWT/data/energygeneration?apiKey=xVQfZ7HaA9');

//convert json object to php associative array

$data = json_decode($jsondata, true);

//get the solar power details

$date = $data['date'];

$data = $data['timestamp']['power'];

//insert into mysql table

$sql = "INSERT INTO tbl_power(date, data)

VALUES('$date', '$data')";

if(!mysql_query($sql,$con))

{

die('Error : ' . mysql_error());

}

Please indicate the error in my code or how I can get this to work. I'm not a PHP developer, I'm mostly self-taught and still fairly new to PHP and JQuery. Any assistance to point out my error would be greatly appreciated.

网友答案:

The json structure is

  {"chartData":

  {"prev":"2016-03-03",

  "next":null,

  "date":"2016-03-04",

  "type":"day",

  "data":[

  [1457135400000,null],[1457135700000,null]

  ....

  ]

In your code

  $data = $data['timestamp']['power'];

In json file no timestamp and power fields

I think you need that data "data":[[1457135400000,null],[1457135700000,null]]}}

When you need to do a loop foreach()

   <?

   /*connect to DB*/
   $con=mysql_connect($bd_host, $bd_user, $bd_password); 
   /*control connection*/
   if(!con) {
   echo 'Error.'; 
   exit; 
   }
   /*select databese name*/
   mysql_select_db($bd_base, $con); 
   /*set encode to utf-8*/
   mysql_query('SET NAMES utf8');
   /**/

    $jsondata = file_get_contents('http://ws.meteocontrol.de/api/sites/P9JWT/data/energygeneration?apiKey=xVQfZ7HaA9');

    //convert json object to php associative array
    $data = json_decode($jsondata, true);
    $date=$data['chartData']['date'];
    $data=$data['chartData']['data'];
    /*print_r($data);*/

    echo 'TIME DATE  '.$date.'<br>';

    echo '<table style="width:100%"><tr><td>time stamp</td><td>data</td></tr>';
    foreach($data as $array) {
    echo '<tr><td>';
    print_r($array[0]);
    echo '</td><td>';
    print_r($array[1]);
    echo '</td></tr>';


            /*insert in db but you will have big quantity of queryes*/

            $sql = "INSERT INTO tbl_power(date, data) VALUES('$array[0]',$array[1])";
            mysql_query($sql,$con);


    }
    echo '</table>';
    ?>
网友答案:

Since you already got a more or less complete example using the deprecated mysql_* functions, here is an example using PDO and a prepare statement + parameters.

<?php
$data = json_decode( file_get_contents('...url...'), true, 5, JSON_BIGINT_AS_STRING ) ;
if ( !$data ) {
    die('something went wrong with the JSON data');
}
else if ( !isset($data['chartData']) || !isset($data['chartData']['date'], $data['chartData']['data']) || !is_array($data['chartData']['data']) ) {
    die('unexpected JSON format');
}
else {
    // connect to the database
    // see http://docs.php.net/pdo.construct
    // and http://docs.php.net/ref.pdo-mysql.connection
    $pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'localonly', 'localonly', array(
        PDO::ATTR_EMULATE_PREPARES=>false,
        PDO::MYSQL_ATTR_DIRECT_QUERY=>false,
        PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION
    ));
    // this will create a (temporary) table, that fits the INSERT statement for this example
    setup($pdo);

    // prepare a statement with two parameters
    // see http://docs.php.net/pdo.prepared-statements
    $stmt = $pdo->prepare('INSERT INTO so_tbl_power (`gmtdate`, `data`) VALUES ( :pit, :measurement )');
    $stmt->bindParam(':pit', $pit);
    $stmt->bindParam(':measurement', $measurement);
    // when the statement is executed the values that are "in" $pit and $measurement at that moment will be used where the placeholders :pit and :measurement were placed in the statement. 

    $date = $data['chartData']['date']; // won't be using this ....
    foreach( $data['chartData']['data'] as $mp ) {
        // ? mp[1] <-> measurement reading failed?
        // skip those entries that do not have a value in $mp[1]
        // you might want to insert those anyway ....but I don't ;-)
        if ( !is_null($mp[1]) ) { 
            $mp[0] = gmdate('Y-m-d H:i:s', substr($mp[0], 0, -3));
            // assign the values to the parameters bound to the statement
            list($pit, $measurement) = $mp;
            // execute the statement (with those parameters)
            $stmt->execute();
        }
    }


    // now let's see what is in the table
    foreach( $pdo->query('SELECT id,`gmtdate`, `data` FROM so_tbl_power ORDER BY id', PDO::FETCH_ASSOC) as $row ) {
        echo join(', ', $row), "\r\n";
    }
}


function setup($pdo) {
    $pdo->exec('
        CREATE TEMPORARY TABLE so_tbl_power (
            id int auto_increment,
            `gmtdate` DateTime NOT NULL, 
            `data` Decimal(8,4),
            primary key(id),
            unique key(`gmtdate`)
        )
    ');
}

Converting the timestamp to a DateTime field in the database is only included to show you how to do it if you want to do it.

分享给朋友:
您可能感兴趣的文章:
随机阅读: