Home Forums Chart Support MySQL – (Displaying Date-Time values on a Chart Axis)

MySQL – (Displaying Date-Time values on a Chart Axis)

Viewing 9 posts - 1 through 9 (of 9 total)
  • #21954

    So I’m looking to do what’s achieved here but my data is from a MySQL Database. But I need to count the total times that date is listed automatically instead of manually.
    For example;
    In my database, I have the following dates,
    2016-08-30 ~ 2016-10-23 ~ 2016-10-23 ~ 2017-02-12 ~ 2018-08-30 ~ 2018-08-30 ~ 2017-02-12
    and in my graph, I want the,
    (axis)
    X = date
    Y = amount that date is listed in DataBase
    so the result should look like this,
    X = 2016-08-30 ~ 2016-10-23 ~ 2017-02-12 ~ 2018-08-30
    y = 1 ~ 2 ~ 2 ~ 2

    I hope this all made sense :)

    Thanks in advance.

    #21961

    JohnSnowGum,

    You can calculate the frequency the date-occurrences in the data & build a set of datapoints. Please refer the code-snippet below.

    function calculateFrequency(dateArray) {
      dateArray.sort();
      var currentXValue = null;
      var count = 0;
    
      for (var i = 0; i < dateArray.length; i++) {
        if (dateArray[i] != currentXValue) {
          if (count > 0)
            dps.push({x: new Date(currentXValue), y: count});
          currentXValue = dateArray[i];
          count = 1;
        } else {
          count++;
        }
      }
      if (count > 0)
        dps.push({x: new Date(currentXValue), y: count});
    }
    

    Please take a look at this JSFiddle for complete code. This example reads data from an array instead of database.
    column chart with data based on frequency of x occurrences

    __
    Priyanka M S
    Team CanvasJS

    #21964

    Yea that’s awesome, quick question? I’m using a JSON file to get the dates, how can I incorporate that into that example?

    #21982

    JohnSnowGum,

    Please refer this documentation page for step-to-step tutorial on rendering chart with data from JSON source.
    chart from external json
    Please take a look into this JSFiddle for an example on chart with date here the data is served from an external JSON source.

    __
    Priyanka M S
    Team CanvasJS

    #21998

    Thanks that’s perfect, quick of topic question how did you code your JSON? Your’s looks much better then mine :) http://banlister.com/graph-test/service.php

    #22000

    JohnSnowGum,

    The JSON data we have provided is an array of objects. In your case, it’s an array of arrays.

    __
    Priyanka M S
    Team CanvasJS

    #22015

    Ah ok. So my JSON should still work much like yours or do I need to change to be an array of objects (if so how would I do that? I’m not that familiar with JSON files in case you can’t tell) Thanks so much for Helping Priyanka :)

    #22016

    @JohnSnowGum,

    When you use JSON data as an array of arrays, you need to slightly modify the above fiddle. Below is the code snippet.

    for (var i = 0; i < dateArray.length; i++) {
      if (dateArray[i][0] != currentXValue) {
        if (count > 0)
          dps.push({x: new Date(currentXValue), y: count});
        currentXValue = dateArray[i];
        count = 1;
      } else {
        count++;
      }
    }

    Please take a look at this updated JSFiddle for complete code.

    __
    Priyanka M S
    Team CanvasJS

    #22017

    I’m not sure what I’m doing wrong, but I have a feeling it’s my JSON code… Here is a jsfiddle using my JSON and here is the code behind my JSON

    <?php
    
    $username = "";
    $password = "";
    $dbname = "";
    
    // Create connection
    $conn = new mysqli('localhost',$username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    
    $sql = "SELECT date FROM db";
    $result = $conn->query($sql);
    
    if ($result->num_rows > 0) {
        // output data of each row
    	$obj = array();
        while($row = $result->fetch_assoc()) {
    		$element = array($row["ban_date"]);
           	array_push($obj,$element);
    	}
    	echo json_encode($obj);
    } else {
        echo "0 results";
    }
    $conn->close();
    ?>
Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic.