Search code examples
javascriptphpmysqlamchartsamstock

How to insert JSON format into dataProvider using amcharts library with mySql

I want to do the same thing as here but with the data extracted from mySQL database and using amCharts library, so here is a snap from the code used to extract data.

The file 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 );
}
?>

So I have extracted all data in $rows.

In my database, I have 3 columns:

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

I was able to print data into a chart by following this amcharts tutorial : using-data-loader-to-connect-charts-to-mysql-data-base

To display the data we need something like that: 

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

The file : json_encode.php contains:

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

The data extracted is in JSON format.

The chart was simple so I want something more advanced. I found this amcharts demo : multiple-data-sets

Here is a snap of the code:

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"
    }]
});

So to conclude, I want to insert "json_encode.php" in dataProvider but I haven't found a way to do it.

Something like:

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

Any help will be appreciated :)

update :

FIRST

file json_encode.php when loaded shows 

[{"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"}]

with using dataLoader like @martynasma suggests i get the chart but without any data : it's seems that the date was well extracted but i don't get any datat (NB: i used data.php that i have put above)

charts loaded successfully but not the DATA

SECOND

, when changing data.php to what @martynasma have suggested the charts still loaded but without any data or date ..

the file json_encode.php shows 

[{"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"}]

it seems that there is something wrong with the date

charts loaded but with no data and date showing 1970 !!!

now i have added some data into mySql This is the data DATA captured from mySql database

this is what i get , i'm happy that some data gets out but it not what i want

only some data that i have added recently appears

@martynasma ok i have updated minPeriod and timestamps but i still don't get the right date , note that json_encode.php contains something like {"date":1439769600,"value":"5"} the date field it's NOT CORRECT and that's why in the page that loaded i get 1970-01-17 00:00 ... i think that mySql set the date like this if it does not found the right date No i have searched to resolve the date format and i was able to get the right value by a little modification of your code

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

after that the page loaded with the correct date but still don't get the data and the charts is looking weird now

chart with exact date but the panel disapeard

you can see that in the zoom panel i only get 1 hour and Max and the others disapered and i don't know why

here what it should look like , this capture if from the second data like i have found from the tutorial the original charts from amcharts

Solution

  • If you want to use Data Loader in Stock Chart, you need to add dataLoader block into your data set definition. I.e.:

    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
  // ...
} );

    There's a section about it in Data Loader doc.

    Having said that, your PHP script that produces JSON data, does not seem to be setup for the fields that are defined in your data set's fieldMappings.

    Furthermore, it separates date and time into separate fields. The Stock Chart can't read dates and time from separate fields, so you need to have one.

    I suppose your PHP script can be remade like this:

    <?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 );
?>

    The following should produce JSON, that should work with the chart config you have.

    Note that I multiply date by 1000, because PHP timestamp is in seconds, whilst JavaScript timestamps are in milliseconds.

    Also, it seems you have a hourly data. You will need to set minPeriod in categoryAxesSettings to "hh" so the chart knows it's not daily data. I.e.:

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

    As a final note, PHP MySQL extension is deprecated. Consider switching to MySQLi, which is correct way to access MySQL data base in modern PHP environments.