Home forums Using CanvasJS MySQL – (Displaying Date-Time values on a Chart Axis)

This topic contains 8 replies, has 2 voices, and was last updated by  JohnSnowGum 2 weeks, 5 days ago.

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

    JohnSnowGum
    Member

    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

    Priyanka M S
    Member

    JohnSnowGum,

    Here is an example with the similar scenario. This example reads data from an array instead of database.

    __
    Priyanka M S
    Team CanvasJS

    #21964

    JohnSnowGum
    Member

    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

    Priyanka M S
    Member

    JohnSnowGum,

    Please take a look into this jsfiddle, here the data is served from an external JSON source.

    __
    Priyanka M S
    Team CanvasJS

    #21998

    JohnSnowGum
    Member

    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

    Priyanka M S
    Member

    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

    JohnSnowGum
    Member

    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

    Priyanka M S
    Member

    @johnsnowgum,

    When you use JSON data as an array of arrays, you need to slightly modify the above fiddle.
    Please take a look at this updated jsfiddle.

    __
    Priyanka M S
    Team CanvasJS

    #22017

    JohnSnowGum
    Member

    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.