我每天都在尝试将JSON数据从实时url（power API）传递到mysql数据库中。这是我的代码：
//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());
}
请指出我的代码中的错误或我如何使其工作。我不是PHP开发人员，我主要是自学成才的，对PHP和JQuery还相当陌生。任何帮助指出我的错误将不胜感激。
由于您已经获得了使用已弃用的 mysql_* 函数的或多或少完整的示例，因此下面是一个使用 PDO 和 prepare 语句 + 参数的示例。
<?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`)
)
');
}
将时间戳转换为数据库中的 DateTime 字段只是为了向您展示如果要执行此操作，则如何执行此操作。
json 结构为
{"chartData":
{"prev":"2016-03-03",
"next":null,
"date":"2016-03-04",
"type":"day",
"data":[
[1457135400000,null],[1457135700000,null]
....
]
在您的代码中
$data = $data['timestamp']['power'];
在 json 文件中没有
timestamp和power字段
我认为你需要这些数据"data":[[1457135400000,null],[1457135700000,null]]}}
当您需要执行循环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>';
?>