javascript - 如何使用带有 mySql 的 amcharts 库将 JSON 格式插入 dataProvider

Question

我想做与这里相同的事情，但使用从 mySQL 数据库中提取的数据并使用 amCharts 库，所以这里是用于提取数据的代码的快照。

文件data.php

<?php
include("include/connexion.php");
$requete = "SELECT * from table";
$resultat = mysql_query($requete) or die(mysql_error());
$total_vue = 0 ;
$rows = array(); 
while( $data = mysql_fetch_assoc( $resultat ) ) {
  $rows[] = $data;
  $varr = $data[ 'date' ];
  $total_vue += $data[ 'temps' ];
  $timestamp = strtotime( $varr );
  $date = date( "m-d-Y", $timestamp );  
  $time = date( "G-i-s", $timestamp );
}
?>

所以我已经提取了 $rows 中的所有数据。

在我的数据库中，我有 3 列:

id    : int(11) with AUTO_INCREMENT,
temps : int(11) ,
date  : timestamp

我能够按照此 amcharts tutorial : using-data-loader-to-connect-charts-to-mysql-data-base 将数据打印到图表中

要显示数据，我们需要这样的东西:

"dataLoader": {
  "url": "json_encode.php"
},

文件:json_encode.php 包含:

<?php
include( "data.php" );
echo  json_encode( $rows );
?>

提取的数据为JSON格式。

图表很简单，所以我想要更高级的东西。我找到了这个 amcharts demo : multiple-data-sets

这是代码的快照:

var chartData1 = [
  { country: "Czech Republic", litres: 156.90},
  { country: "Ireland", litres: 131.10},
  { country: "Germany", litres: 115.80},
  { country: "Australia", litres: 109.90},
  { country: "Austria", litres: 108.30},
  { country: "UK", litres: 99.00}
];
var chart = AmCharts.makeChart( "chartdiv", {
  type: "stock",
  "theme": "light",
 dataSets: [ {
      title: "first data set",
      fieldMappings: [ {
        fromField: "value",
        toField: "value"
      }, {
        fromField: "volume",
        toField: "volume"
      } ],
      dataProvider: chartData1,
      categoryField: "date"
    }]
});

总而言之，我想在dataProvider 中插入“json_encode.php”，但我还没有找到实现它的方法。

类似于:

"dataProvider": {
    "url": "json_encode.php"
},

任何帮助将不胜感激:)

更新:

FIRST

加载时显示文件 json_encode.php

[{"id":"1","temps":"5","date":"2015-08-17 02:00:00"},{"id":"2","temps":"12","date":"2015-08-17 07:00:00"},{"id":"3","temps":"8","date":"2015-08-17 12:17:11"},{"id":"4","temps":"18","date":"2015-08-17 16:22:00"},{"id":"5","temps":"6","date":"2015-08-18 19:40:42"},{"id":"20","temps":"2","date":"2015-08-18 11:22:32"},{"id":"25","temps":"20","date":"2015-08-18 12:21:00"},{"id":"26","temps":"9","date":"2015-08-18 16:00:00"},{"id":"30","temps":"15","date":"2015-04-18 00:00:00"},{"id":"31","temps":"12","date":"2015-01-18 00:00:00"},{"id":"33","temps":"18","date":"2014-10-18 00:00:00"},{"id":"34","temps":"6","date":"2014-07-18 00:00:00"},{"id":"44","temps":"14","date":"2015-08-17 18:24:00"},{"id":"88","temps":"19","date":"2015-08-19 00:00:00"},{"id":"100","temps":"12","date":"2015-08-18 04:00:00"},{"id":"102","temps":"18","date":"2015-08-18 10:00:00"},{"id":"103","temps":"26","date":"2015-08-18 19:00:00"},{"id":"104","temps":"4","date":"2015-08-18 20:00:00"},{"id":"105","temps":"0","date":"2015-08-18 15:25:20"},{"id":"106","temps":"1","date":"2015-08-18 16:17:00"},{"id":"107","temps":"1","date":"2015-08-18 16:17:08"},{"id":"108","temps":"3","date":"2015-08-18 16:17:14"},{"id":"109","temps":"1","date":"2015-08-18 16:17:17"},{"id":"110","temps":"8","date":"2015-08-18 16:17:27"},{"id":"111","temps":"3","date":"2015-08-18 16:17:32"},{"id":"112","temps":"5","date":"2015-08-18 16:17:39"},{"id":"113","temps":"8","date":"2015-08-18 16:17:49"},{"id":"114","temps":"10","date":"2015-08-18 16:18:02"},{"id":"115","temps":"21","date":"2015-08-18 16:18:27"},{"id":"116","temps":"1","date":"2015-08-18 16:18:30"},{"id":"117","temps":"7","date":"2015-08-18 16:18:39"},{"id":"118","temps":"5","date":"2015-08-18 16:18:46"},{"id":"119","temps":"18","date":"2015-08-18 16:19:07"},{"id":"120","temps":"8","date":"2015-08-18 16:19:17"},{"id":"121","temps":"1","date":"2015-08-18 16:19:20"},{"id":"122","temps":"3","date":"2015-08-18 16:19:36"},{"id":"123","temps":"3","date":"2015-08-18 16:19:41"}]

使用@martynasma 之类的 dataLoader 建议我得到图表但没有任何数据:日期似乎提取得很好但我没有得到任何数据(注意:我使用了我上面放的 data.php)

SECOND

，当将 data.php 更改为 @martynasma 所建议的内容时，图表仍在加载但没有任何数据或日期..

文件json_encode.php显示

[{"date":1439769600,"value":"5"},{"date":1439787600,"value":"12"},{"date":1439806631,"value":"8"},{"date":1439821320,"value":"18"},{"date":1439919642,"value":"6"},{"date":1439889752,"value":"2"},{"date":1439893260,"value":"20"},{"date":1439906400,"value":"9"},{"date":1429308000,"value":"15"},{"date":1421535600,"value":"12"},{"date":1413583200,"value":"18"},{"date":1405634400,"value":"6"},{"date":1439828640,"value":"14"},{"date":1439935200,"value":"19"},{"date":1439863200,"value":"12"},{"date":1439884800,"value":"18"},{"date":1439917200,"value":"26"},{"date":1439920800,"value":"4"},{"date":1439904320,"value":"0"},{"date":1439907420,"value":"1"},{"date":1439907428,"value":"1"},{"date":1439907434,"value":"3"},{"date":1439907437,"value":"1"},{"date":1439907447,"value":"8"},{"date":1439907452,"value":"3"},{"date":1439907459,"value":"5"},{"date":1439907469,"value":"8"},{"date":1439907482,"value":"10"},{"date":1439907507,"value":"21"},{"date":1439907510,"value":"1"},{"date":1439907519,"value":"7"},{"date":1439907526,"value":"5"},{"date":1439907547,"value":"18"},{"date":1439907557,"value":"8"},{"date":1439907560,"value":"1"},{"date":1439907576,"value":"3"},{"date":1439907581,"value":"3"}]

日期好像有问题

现在我已经在 mySql 中添加了一些数据这是数据

这就是我得到的，我很高兴一些数据泄露了，但这不是我想要的

@martynasma 好的，我已经更新了 minPeriod 和时间戳，但我仍然没有得到正确的日期，请注意 json_encode.php 包含类似 {"date":1439769600,"value":"5"} 日期字段不正确，这就是为什么在加载的页面中我得到 1970-01-17 00:00 ...我认为 mySql 设置的日期是这样的，如果它不正确的话找到合适的日期不，我已经搜索以解决日期格式问题，并且我能够通过对您的代码进行一些修改来获得正确的值

while( $data = mysql_fetch_assoc( $resultat ) ) {
   $rows[] = array(
    "date" =>  date( 'Y-m-d H:i:s', strtotime( $data['date'])  ),
    "value" => $data[ 'temps' ]
  );
}

之后页面加载了正确的日期，但仍然没有获取数据，图表现在看起来很奇怪

你可以看到在缩放面板中我只有 1 小时和 Max 其他的都消失了，我不知道为什么

这里应该是什么样子，如果是从第二个数据中捕获的，就像我从教程中找到的那样

Answer 1

如果您想在 Stock Chart 中使用 Data Loader，您需要将 dataLoader block 添加到您的数据集定义中。即:

var chart = AmCharts.makeChart( "chartdiv", {
  "type": "stock",
  "dataSets": [ {
    "title": "first data set",
    "fieldMappings": [ {
      "fromField": "value",
      "toField": "value"
    }, {
      "fromField": "volume",
      "toField": "volume"
    } ],
    "categoryField": "date",
    "dataLoader": {
      "url": "json_encode.php"
    }
  } ],
  // the rest of your Stock Chart config
  // ...
} );

Data Loader doc 中有一节是关于它的.

话虽如此，您生成 JSON 数据的 PHP 脚本似乎并未针对数据集的 fieldMappings 中定义的字段进行设置。

此外，它将日期和时间分隔到单独的字段中。股票图表无法从单独的字段中读取日期和时间，因此您需要一个。

我想您的 PHP 脚本可以像这样重新制作:

<?php
$requete = "SELECT * from table";
$resultat = mysql_query( $requete ) or die( mysql_error() );
$rows = array(); 
while( $data = mysql_fetch_assoc( $resultat ) ) {
  $rows[] = array(
    "date" => strtotime( $data[ 'date' ] ) * 1000,
    "value" => $data[ 'temps' ]
  );
}
echo json_encode( $rows );
?>

以下内容应生成 JSON，它应与您拥有的图表配置一起使用。

请注意，我将日期乘以 1000，因为 PHP 时间戳以秒为单位，而 JavaScript 时间戳以毫秒为单位。

此外，您似乎有每小时的数据。您需要设置 minPeriod在 categoryAxesSettings 中设置为“hh”，以便图表知道它不是每日数据。即:

"categoryAxesSettings": {
  "minPeriod": "hh",
  // the rest of your category axes settings
  // ...
}

最后一点，PHP MySQL 扩展已弃用。考虑切换到 MySQLi ，这是在现代 PHP 环境中访问 MySQL 数据库的正确方法。